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 &
mysql -h "127.0.0.1" -u root -P 4000
MySQL> create database testdb;
MySQL> \q
mysql -h "127.0.0.1" -u root -P 4000 -D testdb < mysql_bk.sql

mysql_bk.sql: mysql_bk.txt

Testcase 1

mysql -h "127.0.0.1" -u root -P 4000 -D testdb

mysql> insert into t_vwvgdc (wkey, pkey, c_rdsfbc) values (155, 228000, 99.50);
mysql> select
                  ref_4.pkey as c0
                from
                  t_vwvgdc as ref_4
                where 0 <> 0
              union
              select
                  ref_5.pkey as c0
                from
                  t_vwvgdc as ref_5;

Testcase 2

mysql -h "127.0.0.1" -u root -P 4000 -D testdb

mysql> BEGIN OPTIMISTIC;
mysql> insert into t_vwvgdc (wkey, pkey, c_rdsfbc) values (155, 228000, 99.50);
mysql> select
                  ref_4.pkey as c0
                from
                  t_vwvgdc as ref_4
                where 0 <> 0
              union
              select
                  ref_5.pkey as c0
                from
                  t_vwvgdc as ref_5;
mysql> commit;

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

+--------+
| c0     |
+--------+
|  17000 |
|  15000 |
|  66000 |
|    ... |
| 228000 |
+--------+
30 rows in set (0.00 sec)

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.1.0
Edition: Community
Git Commit Hash: 1a89decdb192cbdce6a7b0020d71128bc964d30f
Git Branch: HEAD
UTC Build Time: 2022-08-01 09:18:07
GoVersion: go1.18
Race Enabled: false
TiKV Min Version: v3.0.0-60965b006877ca7234adaced7890d7b029ed1306
Check Table Before Drop: false

[tiancaiamao]

Do you mean there is a UnionScan executor? if so, that's expected.
Inside transaction, we use a UnionScan to combine the data from TiKV snapshot and transaction's in-memory buffer.

All the changes are buffered in the transaction before it commit. Without UnionScan, you can't read what you write, so the plan is different. @JZuming

[JZuming]

Quite strange. Here is another test case

BEGIN OPTIMISTIC;

insert into t_vwvgdc (wkey, pkey, c_rdsfbc) values (155, 228000, 99.50);

select pkey from t_vwvgdc where 0 <> 0;   --- output empty set

select pkey from t_vwvgdc;   --- output 30 rows

select pkey from t_vwvgdc where 0 <> 0
union
select pkey from t_vwvgdc;   --- output empty set

commit;

The first SELECT outputs an empty set, and the second SELECT outputs a set with 30 rows. It is quite strange that the third SELECT (first SELECT union second SELECT) outputs an empty set.