Index merge wrongly ignore some predicates of generated column

[joechenrh]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

CREATE TABLE t3 (
  id int PRIMARY KEY,
  c1 varchar(256),
  c2 varchar(256) GENERATED ALWAYS AS (concat(c1, c1)) VIRTUAL,
  KEY (id)
);
 
insert into t3(id, c1) values (50, 'c');

SELECT /*+ USE_INDEX_MERGE(`t3`)*/ id FROM `t3` WHERE c2 BETWEEN 'a' AND 'b' GROUP BY id HAVING id < 100 or id > 0;

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

Empty set

3. What did you see instead (Required)

mysql> SELECT /*+ USE_INDEX_MERGE(`t3`)*/ id FROM `t3` WHERE c2 BETWEEN 'a' AND 'b' GROUP BY id HAVING id < 100 or id > 0;
+----+
| id |
+----+
| 50 |
+----+
1 row in set (0.01 sec)

4. What is your TiDB version? (Required)

[joechenrh]

mysql> explain SELECT /*+ USE_INDEX_MERGE(`t3`)*/ id FROM `t3` WHERE c2 BETWEEN 'a' AND 'b' GROUP BY id HAVING id < 100 or id > 0;
+------------------------------------+---------+-----------+------------------------+---------------------------------------------------------------------------------------+
| id                                 | estRows | task      | access object          | operator info                                                                         |
+------------------------------------+---------+-----------+------------------------+---------------------------------------------------------------------------------------+
| Projection_7                       | 0.02    | root      |                        | test.t3.id                                                                            |
| └─Projection_14                    | 1.00    | root      |                        | test.t3.id, test.t3.c2                                                                |
|   └─IndexMerge_12                  | 1.00    | root      |                        | type: union                                                                           |
|     ├─IndexRangeScan_9(Build)      | 1.00    | cop[tikv] | table:t3, index:id(id) | range:[-inf,100), keep order:false, stats:partial[id:unInitialized, c2:unInitialized] |
|     ├─TableRangeScan_10(Build)     | 1.00    | cop[tikv] | table:t3               | range:(0,+inf], keep order:false, stats:partial[id:unInitialized, c2:unInitialized]   |
|     └─TableRowIDScan_11(Probe)     | 1.00    | cop[tikv] | table:t3               | keep order:false, stats:partial[id:unInitialized, c2:unInitialized]                   |
+------------------------------------+---------+-----------+------------------------+---------------------------------------------------------------------------------------+
6 rows in set (0.00 sec)

mysql> explain SELECT /*+ USE_INDEX_MERGE(`t3`)*/ id FROM `t3` WHERE c1 BETWEEN 'a' AND 'b' GROUP BY id HAVING id < 100 or id > 0;
+----------------------------------+---------+-----------+------------------------+---------------------------------------------------------------------------------------+
| id                               | estRows | task      | access object          | operator info                                                                         |
+----------------------------------+---------+-----------+------------------------+---------------------------------------------------------------------------------------+
| Projection_7                     | 0.02    | root      |                        | test.t3.id                                                                            |
| └─IndexMerge_13                  | 1.00    | root      |                        | type: union                                                                           |
|   ├─IndexRangeScan_9(Build)      | 1.00    | cop[tikv] | table:t3, index:id(id) | range:[-inf,100), keep order:false, stats:partial[id:unInitialized, c1:unInitialized] |
|   ├─TableRangeScan_10(Build)     | 1.00    | cop[tikv] | table:t3               | range:(0,+inf], keep order:false, stats:partial[id:unInitialized, c1:unInitialized]   |
|   └─Selection_12(Probe)          | 1.00    | cop[tikv] |                        | ge(test.t3.c1, "a"), le(test.t3.c1, "b")                                              |
|     └─TableRowIDScan_11          | 1.00    | cop[tikv] | table:t3               | keep order:false, stats:partial[id:unInitialized, c1:unInitialized]                   |
+----------------------------------+---------+-----------+------------------------+---------------------------------------------------------------------------------------+
6 rows in set (0.00 sec)

From the plan we can see that the condition c2 BETWEEN 'a' AND 'b' is missed