Update with sub query uses incorrect snapshot in RR isolation level

[wengsy150943]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

init database with 1 row as below.

create table t(a int, b int);
insert into t values(1,1);

Then start two session and update that row in session2 after session1 starts a transaction.
Then execute a update with sub query in session1.

session1 > begin;
session2 > update t set b=2 where a=1; -- now the data is (1,2)
session1 > update t set b=3 where b=(select b from t where b=2); -- if update reads the latest committed data, it will update the database to (1,3)
session1 > commit;
session1 > select * from t; -- check the final database statement

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

The last select returns:

MySQL [test]> select * from t;
+------+------+
| a    | b    |
+------+------+
|    1 |    3 |
+------+------+
1 row in set (0.00 sec)

3. What did you see instead (Required)

The last select returns:

MySQL [test]> select * from t;
+------+------+
| a    | b    |
+------+------+
|    1 |    2 |
+------+------+
1 row in set (0.00 sec)

We also check the behaviour of normal update in the case below.

-- start init
create table t(a int, b int);
insert into t values(1,1);
-- end init, and the database statement is the same as the case above.
session1 > begin;
session2 > update t set b=2 where a=1; -- now the data is (1,2)
session1 > update t set b=3 where b=2; -- if update reads the latest committed data, it will update the database to (1,3)
session1 > commit;
session1 > select * from t; -- check the final database statement

Then it returns:

MySQL [test]> select * from t;
+------+------+
| a    | b    |
+------+------+
|    1 |    3 |
+------+------+
1 row in set (0.00 sec)

as expected.

Note that update t set b=3 where b=2; and update t set b=3 where b=(select b from t where b=2); have the same meaning in these two cases. It;s confusing to have different results.

4. What is your TiDB version? (Required)

+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tidb_version()                                                                                                                                                                                                                                                                               |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Release Version: v7.1.0
Edition: Community
Git Commit Hash: 635a4362235e8a3c0043542e629532e3c7bb2756
Git Branch: heads/refs/tags/v7.1.0
UTC Build Time: 2023-05-30 10:58:57
GoVersion: go1.20.3
Race Enabled: false
TiKV Min Version: 6.2.0-alpha
Check Table Before Drop: false
Store: tikv |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

[wengsy150943]

We analyze the cause of this bug by explaining the execution plan:

MySQL [test]> explain update t set b=3 where b = (select b from t as t2 where b=2);
+--------------------+---------+------+---------------+---------------+
| id                 | estRows | task | access object | operator info |
+--------------------+---------+------+---------------+---------------+
| Update_15          | N/A     | root |               | N/A           |
| └─TableDual_17     | 0.00    | root |               | rows:0        |
+--------------------+---------+------+---------------+---------------+
2 rows in set (0.01 sec)

The sub query in update is executed as a single query and uses snapshot read.
The snapshot read by sub query is different from that read by update and breaks the consistency in this single update SQL(with sub query), because a single SQL reads two snapshots.
We test the same case in MySQL(v8.0.33), whose sub query read consistent snapshot as update.
Thus this execution is incompatible with MySQL.

Since the execution of update t set b=3 where b = (select b from t as t2 where b=2 for update);(add for update in sub query) returns the expected result.
Maybe it can be fixed by marking sub query in write operation as a for update read.