Cannot use IndexMerge in transaction #29901
Description
Bug Report
Please answer these questions before submitting your issue. Thanks!
1. Minimal reproduce step (Required)
drop table if exists t1;
create table t1(c1 int, c2 int, key(c1), key(c2));
begin;
explain select /*+ use_index_merge(t1) */ * from t1 where c1 < 10 or c2 < 10;
insert into t1 values(1, 1);
explain select /*+ use_index_merge(t1) */ * from t1 where c1 < 10 or c2 < 10;
commit;
2. What did you expect to see? (Required)
By using hint, we can use IndexMerge
3. What did you see instead (Required)
MySQL [test]> explain select /*+ use_index_merge(t1) */ * from t1 where c1 < 10 or c2 < 10;
+-------------------------------+---------+-----------+------------------------+-------------------------------------------------+
| id | estRows | task | access object | operator info |
+-------------------------------+---------+-----------+------------------------+-------------------------------------------------+
| IndexMerge_8 | 5542.21 | root | | |
| ├─IndexRangeScan_5(Build) | 3323.33 | cop[tikv] | table:t1, index:c1(c1) | range:[-inf,10), keep order:false, stats:pseudo |
| ├─IndexRangeScan_6(Build) | 3323.33 | cop[tikv] | table:t1, index:c2(c2) | range:[-inf,10), keep order:false, stats:pseudo |
| └─TableRowIDScan_7(Probe) | 5542.21 | cop[tikv] | table:t1 | keep order:false, stats:pseudo |
+-------------------------------+---------+-----------+------------------------+-------------------------------------------------+
4 rows in set (0.01 sec)
MySQL [test]> insert into t1 values(1, 1);
Query OK, 1 row affected (0.00 sec)
MySQL [test]> explain select /*+ use_index_merge(t1) */ * from t1 where c1 < 10 or c2 < 10;
+-----------------------------+----------+-----------+---------------+--------------------------------------------+
| id | estRows | task | access object | operator info |
+-----------------------------+----------+-----------+---------------+--------------------------------------------+
| Projection_5 | 5542.21 | root | | test.t1.c1, test.t1.c2 |
| └─UnionScan_6 | 5542.21 | root | | or(lt(test.t1.c1, 10), lt(test.t1.c2, 10)) |
| └─TableReader_9 | 5542.21 | root | | data:Selection_8 |
| └─Selection_8 | 5542.21 | cop[tikv] | | or(lt(test.t1.c1, 10), lt(test.t1.c2, 10)) |
| └─TableFullScan_7 | 10000.00 | cop[tikv] | table:t1 | keep order:false, stats:pseudo |
+-----------------------------+----------+-----------+---------------+--------------------------------------------+
5 rows in set, 1 warning (0.00 sec)
4. What is your TiDB version? (Required)
mysql> select tidb_version();
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tidb_version()
|
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Release Version: v5.4.0-alpha-140-g68b5e0658c
Edition: Community
Git Commit Hash: 68b5e06
Git Branch: index_merge
UTC Build Time: 2021-11-18 07:12:21
GoVersion: go1.16.4
Race Enabled: false
TiKV Min Version: v3.0.0-60965b006877ca7234adaced7890d7b029ed1306
Check Table Before Drop: false |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)