clustered index + unionscan，query using index return the same 2 records

[vivid392845427]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

set new_collations_enabled_on_first_bootstrap: true

CREATE TABLE `t` (
  `c_int` int(11) NOT NULL,
  `c_str` varchar(40) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
  `v_str` varchar(40) GENERATED ALWAYS AS (substr(`c_str`, 1, 34)) VIRTUAL,
  `s_str` varchar(40) GENERATED ALWAYS AS (substr(`c_str`, 1, 34)) STORED,
  `c_set` set('blue','green','red','yellow','white','orange','purple') NOT NULL,
  PRIMARY KEY (`c_int`,`c_set`) CLUSTERED ,
  UNIQUE KEY `c_int_2` (`c_int`),
  UNIQUE KEY `c_str` (`c_str`(24))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
insert into t(c_int, c_str, c_set) values( 11, 'eager mcnulty','green' );
begin;
update t set c_int = c_int + 5, c_str = "priceless satoshi" where (c_int, c_str) in ((11, "inspiring thompson"));
select * from t;
select c_int,c_set from t;

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

queries return the same result：

mysql> select * from t;
+-------+---------------+---------------+---------------+-------+
| c_int | c_str         | v_str         | s_str         | c_set |
+-------+---------------+---------------+---------------+-------+
|    11 | eager mcnulty | eager mcnulty | eager mcnulty | green |
+-------+---------------+---------------+---------------+-------+
1 row in set (0.01 sec)

mysql> select c_int,c_set from t;
+-------+-------+
| c_int | c_set |
+-------+-------+
|    11 | green |
+-------+-------+
2 rows in set (0.00 sec)

3. What did you see instead (Required)

mysql> select * from t;
+-------+---------------+---------------+---------------+-------+
| c_int | c_str         | v_str         | s_str         | c_set |
+-------+---------------+---------------+---------------+-------+
|    11 | eager mcnulty | eager mcnulty | eager mcnulty | green |
+-------+---------------+---------------+---------------+-------+
1 row in set (0.01 sec)

mysql> select c_int,c_set from t;
+-------+-------+
| c_int | c_set |
+-------+-------+
|    11 | green |
|    11 | green |
+-------+-------+
2 rows in set (0.00 sec)

mysql> explain select * from t;             
+-------------------------+---------+-----------+---------------+--------------------------------+
| id                      | estRows | task      | access object | operator info                  |
+-------------------------+---------+-----------+---------------+--------------------------------+
| UnionScan_5             | 1.00    | root      |               |                                |
| └─TableReader_7         | 1.00    | root      |               | data:TableFullScan_6           |
|   └─TableFullScan_6     | 1.00    | cop[tikv] | table:t       | keep order:false, stats:pseudo |
+-------------------------+---------+-----------+---------------+--------------------------------+
3 rows in set (0.01 sec)

mysql> explain select c_int,c_set from t; 
+-------------------------+---------+-----------+-------------------------------+--------------------------------+
| id                      | estRows | task      | access object                 | operator info                  |
+-------------------------+---------+-----------+-------------------------------+--------------------------------+
| UnionScan_5             | 1.00    | root      |                               |                                |
| └─IndexReader_9         | 1.00    | root      |                               | index:IndexFullScan_8          |
|   └─IndexFullScan_8     | 1.00    | cop[tikv] | table:t, index:c_int_2(c_int) | keep order:false, stats:pseudo |
+-------------------------+---------+-----------+-------------------------------+--------------------------------+
3 rows in set (0.00 sec)

4. What is your TiDB version? (Required)

Release Version: v5.4.0-nightly
Edition: Community
Git Commit Hash: d208b62025017f122a3d030d50ba802526e7ef48
Git Branch: heads/refs/tags/v5.4.0-nightly
UTC Build Time: 2021-12-16 08:12:32
GoVersion: go1.16.4
Race Enabled: false
TiKV Min Version: v3.0.0-60965b006877ca7234adaced7890d7b029ed1306
Check Table Before Drop: false

also in ft-data-inconsistency branch

[vivid392845427]

1、the update operation in the transaction does not match the record, why unionscan is used？
2、query using unionscan，indicating that the update operation writes records, indexread can read the redundant records, dataread cannot , why is the check mutation not detected?

[ekexium]

the update operation in the transaction does not match the record, why unionscan is used？

There is a PUT_index for (batch_)point_get, introduced by #25730

why is the check mutation not detected

It does not call UpdateRecord. The mutation checker is not triggered

[ekexium]

/cc @cfzjywxk

[cfzjywxk]

@vivid392845427 @ekexium
I think the root cause for this phenomenon is the same as #24195, the last query will use index range:

explain select c_int,c_set from t;
+-------------------------+---------+-----------+-------------------------------+--------------------------------+
| id                      | estRows | task      | access object                 | operator info                  |
+-------------------------+---------+-----------+-------------------------------+--------------------------------+
| UnionScan_5             | 1.00    | root      |                               |                                |
| └─IndexReader_9         | 1.00    | root      |                               | index:IndexFullScan_8          |
|   └─IndexFullScan_8     | 1.00    | cop[tikv] | table:t, index:c_int_2(c_int) | keep order:false, stats:pseudo |
+-------------------------+---------+-----------+-------------------------------+--------------------------------+
3 rows in set (0.01 sec)

The unique index c_int_2 is used, and currently in the unionScan executor will not merge duplicated unique index keys so the same result is returnd twice.

If the c_str index is used, as there's no PUT on the index record, so the returned result is as expected.

mysql> explain select c_int,c_set from t use index(c_str);
+-------------------------+---------+-----------+-----------------------------+--------------------------------+
| id                      | estRows | task      | access object               | operator info                  |
+-------------------------+---------+-----------+-----------------------------+--------------------------------+
| UnionScan_5             | 1.00    | root      |                             |                                |
| └─IndexReader_7         | 1.00    | root      |                             | index:IndexFullScan_6          |
|   └─IndexFullScan_6     | 1.00    | cop[tikv] | table:t, index:c_str(c_str) | keep order:false, stats:pseudo |
+-------------------------+---------+-----------+-----------------------------+--------------------------------+
3 rows in set (0.00 sec)

mysql>  select c_int,c_set from t use index(c_str);
+-------+-------+
| c_int | c_set |
+-------+-------+
|    11 | green |
+-------+-------+
1 row in set (0.00 sec)

[cfzjywxk]

In the future sprint doing pessimistic transaction optimizations for example if we could push down the lock operator to tikv nodes, the lock behaviour then would be the same for all executors.
/cc
@MyonKeminta @you06

[you06]

IMO, such PUT operation should not be visible to read operations.

[vivid392845427]

duplicate with 24159，appears when union scan + point_get + select use unqiue index