memdb: snapshot getter and iter returns latest data instead

[you06]

Bug Report

Please answer these questions before submitting your issue. Thanks!

See also tikv/client-go#1394

The snapshot getter and iter of memdb should read bypass the staging data(like the written data from current statement, it's not released yet). But the current snapshot read the latest data instead, see the unit test in client-go's PR.

The affected code:

• tidb/pkg/executor/union_scan.go

  Line 113 in cdd7c9e

  us.memBufSnap = mb.SnapshotGetter()

• tidb/pkg/executor/mem_reader.go

  Lines 578 to 582 in 7e73ddc

  	if !reverse {
  	iter = txn.GetMemBuffer().SnapshotIter(rg.StartKey, rg.EndKey)
  	} else {
  	iter = txn.GetMemBuffer().SnapshotIterReverse(rg.EndKey, rg.StartKey)
  	}

The actual impact on TiDB will be attached once there is a reproduce.

1. Minimal reproduce step (Required)

set session tidb_distsql_scan_concurrency = 1;
set session tidb_index_lookup_join_concurrency = 1;
set session tidb_projection_concurrency=1;
set session tidb_init_chunk_size=1;
set session tidb_max_chunk_size=40;
set session tidb_index_join_batch_size = 10;

create table t(a int primary key, b int, index i(b));

begin;
insert into t values (0, 0), (1, 1), (2, 2), (3, 3), (4, 4), (5, 5), (6, 6), (7, 7), (8, 8), (9, 9), (10, 10), (11, 11), (12, 12), (13, 13), (14, 14), (15, 15), (16, 16), (17, 17), (18, 18), (19, 19), (20, 20), (21, 21), (22, 22), (23, 23), (24, 24), (25, 25), (26, 26), (27, 27), (28, 28), (29, 29), (30, 30), (31, 31), (32, 32), (33, 33), (34, 34), (35, 35), (36, 36), (37, 37), (38, 38), (39, 39), (40, 40), (41, 41), (42, 42), (43, 43), (44, 44), (45, 45), (46, 46), (47, 47), (48, 48), (49, 49), (50, 50), (51, 51), (52, 52), (53, 53), (54, 54), (55, 55), (56, 56), (57, 57), (58, 58), (59, 59), (60, 60), (61, 61), (62, 62), (63, 63), (64, 64), (65, 65), (66, 66), (67, 67), (68, 68), (69, 69), (70, 70), (71, 71), (72, 72), (73, 73), (74, 74), (75, 75), (76, 76), (77, 77), (78, 78), (79, 79), (80, 80), (81, 81), (82, 82), (83, 83), (84, 84), (85, 85), (86, 86), (87, 87), (88, 88), (89, 89), (90, 90), (91, 91), (92, 92), (93, 93), (94, 94), (95, 95), (96, 96), (97, 97), (98, 98), (99, 99), (100, 100);
insert into t (select /*+ INL_JOIN(t1) */ 100 - t1.a as a, t1.b from t t1, (select a, b from t) t2 where t1.b = t2.b) on duplicate key update b = values(b);
select a, b from t where a + b != 100;
commit;

The insert on duplicate key statement will update it to (0, 100), (1, 99), ... (100, 0)

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

Without begin and commit, select a, b from t where a + b != 100 returns empty result set.

The above in-txn case should keep the same behavior.

3. What did you see instead (Required)

mysql> insert into t (select /*+ INL_JOIN(t1) */ 100 - t1.a as a, t1.b from t t1, (select a, b from t) t2 where t1.b = t2.b) on duplicate key update b = values(b);
Query OK, 162 rows affected (0.01 sec)
Records: 82  Duplicates: 81  Warnings: 0

mysql> select a, b from t where a + b != 100;
+----+------+
| a  | b    |
+----+------+
|  0 |    0 |
|  1 |    1 |
|  2 |    2 |
|  3 |    3 |
|  4 |    4 |
|  5 |    5 |
|  6 |    6 |
|  7 |    7 |
|  8 |    8 |
|  9 |    9 |
| 10 |   10 |
| 11 |   11 |
| 12 |   12 |
| 13 |   13 |
| 14 |   14 |
| 15 |   15 |
| 16 |   16 |
| 17 |   17 |
| 18 |   18 |
+----+------+
19 rows in set (0.00 sec)s

4. What is your TiDB version? (Required)

Release Version: v8.3.0-alpha-206-ge30408e869
Edition: Community
Git Commit Hash: e30408e869e40cc738c9bb085ccc2f078fbee2c5
Git Branch: HEAD
UTC Build Time: 2024-08-20 02:28:46
GoVersion: go1.21.10
Race Enabled: false
Check Table Before Drop: false
Store: tikv

[you06]

This bug needs dynamic built unionscan to trigger, the reproduce is very targeted design.