Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

prepare stmt with stale read get wrong result #43044

Closed
you06 opened this issue Apr 13, 2023 · 2 comments · Fixed by #43204
Closed

prepare stmt with stale read get wrong result #43044

you06 opened this issue Apr 13, 2023 · 2 comments · Fixed by #43204
Labels

Comments

@you06
Copy link
Contributor

you06 commented Apr 13, 2023

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

Run playground with the following TiKV config:

[resolved-ts]
advance-ts-interval = "100ms"

Run this case.

/* init */ drop table if exists t;
/* init */ create table t(id int primary key, v int);

/* init */ insert into t values(1, 1), (2, 2);

/* t1 */ select sleep(2);
/* t1 */ select * from t as of timestamp now() - interval 1 second;
/* t1 */ prepare stmt from 'select * from t as of timestamp now() - interval 1 second';
/* t1 */ execute stmt;

/* t2 */ insert into t values(3, 3);

/* t1 */ select sleep(1);
/* t1 */ select * from t as of timestamp now() - interval 1 second;
/* t1 */ execute stmt;

/* t1 */ select sleep(1);
/* t1 */ select * from t as of timestamp now() - interval 1 second;
/* t1 */ execute stmt;

2. What did you expect to see? (Required)

select and execute always returns the same result.

3. What did you see instead (Required)

The previous result, there is a issue my test tool, which does not read data from a execute statement
/* init */ drop table if exists t;
-- init >> 0 rows affected
/* init */ create table t(id int primary key, v int);
-- init >> 0 rows affected
/* init */ insert into t values(1, 1), (2, 2);
-- init >> 2 rows affected
/* t1 */ select sleep(2);
-- t1 >> +----------+
-- t1    | SLEEP(2) |
-- t1    +----------+
-- t1    |        0 |
-- t1    +----------+
/* t1 */ select * from t as of timestamp now() - interval 1 second;
-- t1 >> +----+---+
-- t1    | ID | V |
-- t1    +----+---+
-- t1    |  1 | 1 |
-- t1    |  2 | 2 |
-- t1    +----+---+
/* t1 */ prepare stmt from 'select * from t as of timestamp now() - interval 1 second';
-- t1 >> 0 rows affected
/* t1 */ execute stmt;
-- t1 >> 0 rows affected
/* t2 */ insert into t values(3, 3);
-- t2 >> 1 rows affected
/* t1 */ select sleep(1);
-- t1 >> +----------+
-- t1    | SLEEP(1) |
-- t1    +----------+
-- t1    |        0 |
-- t1    +----------+
/* t1 */ select * from t as of timestamp now() - interval 1 second;
-- t1 >> +----+---+
-- t1    | ID | V |
-- t1    +----+---+
-- t1    |  1 | 1 |
-- t1    |  2 | 2 |
-- t1    +----+---+
/* t1 */ execute stmt;
-- t1 >> 0 rows affected
/* t1 */ select sleep(1);
-- t1 >> +----------+
-- t1    | SLEEP(1) |
-- t1    +----------+
-- t1    |        0 |
-- t1    +----------+
/* t1 */ select * from t as of timestamp now() - interval 1 second;
-- t1 >> +----+---+
-- t1    | ID | V |
-- t1    +----+---+
-- t1    |  1 | 1 |
-- t1    |  2 | 2 |
-- t1    |  3 | 3 |
-- t1    +----+---+
/* t1 */ execute stmt;
-- t1 >> 0 rows affected

Note this is also a anomaly. After sleep 1 second, as of timestamp now() - interval 1 second should see the previous insert (3, 3).

Case details
/* t2 */ insert into t values(3, 3);
-- t2 >> 1 rows affected
/* t1 */ select sleep(1);
-- t1 >> +----------+
-- t1    | SLEEP(1) |
-- t1    +----------+
-- t1    |        0 |
-- t1    +----------+
/* t1 */ select * from t as of timestamp now() - interval 1 second;
-- t1 >> +----+---+
-- t1    | ID | V |
-- t1    +----+---+
-- t1    |  1 | 1 |
-- t1    |  2 | 2 |
-- t1    +----+---+

It's caused by now() accurate issue.


Run this statement with mysql client, got the following output, the resultset of execute stmt is not updated after (3, 3) is inserted.

MySQL [test]> /* init */ create table t(id int primary key, v int);
Query OK, 0 rows affected (0.134 sec)

MySQL [test]> 
MySQL [test]> /* init */ insert into t values(1, 1), (2, 2);
Query OK, 2 rows affected (0.006 sec)
Records: 2  Duplicates: 0  Warnings: 0

MySQL [test]> 
MySQL [test]> /* t1 */ select sleep(2);
+----------+
| sleep(2) |
+----------+
|        0 |
+----------+
1 row in set (2.001 sec)

MySQL [test]> /* t1 */ select * from t as of timestamp now() - interval 1 second;
+----+------+
| id | v    |
+----+------+
|  1 |    1 |
|  2 |    2 |
+----+------+
2 rows in set (0.001 sec)

MySQL [test]> /* t1 */ prepare stmt from 'select * from t as of timestamp now() - interval 1 second';
Query OK, 0 rows affected (0.001 sec)

MySQL [test]> /* t1 */ execute stmt;
+----+------+
| id | v    |
+----+------+
|  1 |    1 |
|  2 |    2 |
+----+------+
2 rows in set (0.001 sec)

MySQL [test]> 
MySQL [test]> /* t2 */ insert into t values(3, 3);
Query OK, 1 row affected (0.004 sec)

MySQL [test]> 
MySQL [test]> /* t1 */ select sleep(1);
+----------+
| sleep(1) |
+----------+
|        0 |
+----------+
1 row in set (1.002 sec)

MySQL [test]> /* t1 */ select * from t as of timestamp now() - interval 1 second;
+----+------+
| id | v    |
+----+------+
|  1 |    1 |
|  2 |    2 |
+----+------+
2 rows in set (0.002 sec)

MySQL [test]> /* t1 */ execute stmt;
+----+------+
| id | v    |
+----+------+
|  1 |    1 |
|  2 |    2 |
+----+------+
2 rows in set (0.001 sec)

MySQL [test]> 
MySQL [test]> /* t1 */ select sleep(1);
+----------+
| sleep(1) |
+----------+
|        0 |
+----------+
1 row in set (1.001 sec)

MySQL [test]> /* t1 */ select * from t as of timestamp now() - interval 1 second;
+----+------+
| id | v    |
+----+------+
|  1 |    1 |
|  2 |    2 |
|  3 |    3 |
+----+------+
3 rows in set (0.002 sec)

MySQL [test]> /* t1 */ execute stmt;
+----+------+
| id | v    |
+----+------+
|  1 |    1 |
|  2 |    2 |
+----+------+
2 rows in set (0.002 sec)

4. What is your TiDB version? (Required)

Release Version: v7.1.0-alpha
Edition: Community
Git Commit Hash: 8c9663941fc633bc57edfcfb3edc5da8533ace57
Git Branch: heads/refs/tags/v7.1.0-alpha
UTC Build Time: 2023-04-12 14:36:43
GoVersion: go1.20.3
Race Enabled: false
TiKV Min Version: 6.2.0-alpha
Check Table Before Drop: false
Store: tikv
@you06 you06 added the type/bug The issue is confirmed as a bug. label Apr 13, 2023
@cfzjywxk
Copy link
Contributor

em...
Is it because the stale_read_ts is not set properly?

@ti-chi-bot ti-chi-bot added may-affects-5.1 This bug maybe affects 5.1.x versions. may-affects-5.2 This bug maybe affects 5.2.x versions. may-affects-5.3 This bug maybe affects 5.3.x versions. may-affects-5.4 This bug maybe affects 5.4.x versions. may-affects-6.1 may-affects-6.5 labels Apr 17, 2023
@seiya-annie seiya-annie removed may-affects-5.1 This bug maybe affects 5.1.x versions. may-affects-5.2 This bug maybe affects 5.2.x versions. may-affects-5.3 This bug maybe affects 5.3.x versions. may-affects-5.4 This bug maybe affects 5.4.x versions. may-affects-6.1 may-affects-6.5 labels Apr 17, 2023
@you06
Copy link
Contributor Author

you06 commented Apr 19, 2023

Is it because the stale_read_ts is not set properly?

Yes, the prepare/execute stmt only set stale_read_ts when preparing.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
Projects
None yet
Development

Successfully merging a pull request may close this issue.

5 participants