TiDB (with tikv) produces different results for SELECT statements when using transaction

[JZuming]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

Setup the environment:

tiup playground --db.binpath /path/to/latest/tidb-server &

Testcase 1

mysql -h "127.0.0.1" -u root -P 4000 -D testdb < mysql_bk.sql
mysql -h "127.0.0.1" -u root -P 4000 -D testdb

mysql> update t_8uiwcc set wkey = 264;
mysql> select * from
  t_rxo2lc
where (case when 55
           > case when t_rxo2lc.c_4j_5y not in (
            select
                  ref_0.c_do8jqc as c0
                from
                  t_8uiwcc as ref_0
                where 24 >= 100
              union
              select
                  ref_1.pkey as c0
                from
                  t_8uiwcc as ref_1
                where 49 <= 75) then 14 else 63 end
           then 1 else 78 end
       between 6 and 100);

Testcase 2

mysql -h "127.0.0.1" -u root -P 4000 -D testdb < mysql_bk.sql
mysql -h "127.0.0.1" -u root -P 4000 -D testdb

mysql> start transaction;
mysql> update t_8uiwcc set wkey = 264;
mysql> select * from
  t_rxo2lc
where (case when 55
           > case when t_rxo2lc.c_4j_5y not in (
            select
                  ref_0.c_do8jqc as c0
                from
                  t_8uiwcc as ref_0
                where 24 >= 100
              union
              select
                  ref_1.pkey as c0
                from
                  t_8uiwcc as ref_1
                where 49 <= 75) then 14 else 63 end
           then 1 else 78 end
       between 6 and 100);
mysql> commit;

mysql_bk.sql: mysql_bk.txt

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

The execution result of SELECT statements in Testcase 1 and Testcase 2 are same.

3. What did you see instead (Required)

Output of SELECT statement in Testcase 1

+------+---------+----------+----------+----------+----------+---------+--------------------+----------+----------+----------+----------+
| wkey | pkey    | c_tqng_c | c_0x5nid | c_dwgced | c_wngutc | c_4j_5y | c_rh_b1d           | c_2kjcpc | c_5nxshb | c_nntyvd | c__v1pmd |
+------+---------+----------+----------+----------+----------+---------+--------------------+----------+----------+----------+----------+
|  450 | 1057000 | NULL     |  1057000 | o83s     | o0jzo    |    NULL | 1703.8446113454934 |    60.85 | o83s     | o83s     | NULL     |
|  450 | 1058000 | v5gptc   |  1058000 | NULL     | qvpggb   |    NULL | 1703.8446113454934 |    71.91 | NULL     | NULL     | v5gptc   |
|  450 | 1059000 | mtjffd   |  1059000 | ojmxkc   | NULL     |    NULL | 1703.8446113454934 |    100.5 | ojmxkc   | ojmxkc   | mtjffd   |
+------+---------+----------+----------+----------+----------+---------+--------------------+----------+----------+----------+----------+

Output of SELECT statement in Testcase 2

Empty set (0.00 sec)

Their results are different.

4. What is your TiDB version? (Required)

Release Version: v6.2.0
Edition: Community
Git Commit Hash: daf2b17cdfe30c02ce282361009fb5bdb05f2b0e
Git Branch: HEAD
UTC Build Time: 2022-09-22 19:59:46
GoVersion: go1.18
Race Enabled: false
TiKV Min Version: 6.2.0-alpha
Check Table Before Drop: false
Store: tikv

[you06]

Hi, thanks for the issue. I made a bisect and this issue is fixed by #36961, the root cause is same with #36903.