Inconsistent result of the same queries, related to index merge, null

[wjhuang2016]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

CREATE TABLE `87f3b2f7` (
  `12db5ecc` set('nwbk','r5','1ad3u','van','ir1z','y','9m','f1','z','e6yd','wfev') NOT NULL DEFAULT 'ir1z,f1,e6yd',
  `0a968b42` enum('soo2','4s4j','qi9om','8ue','i71o','qon','3','3feh','6o1i','5yebx','d') NOT NULL DEFAULT '8ue',
  `af528200` varchar(66) DEFAULT '13mdezixgcn',
  PRIMARY KEY (`12db5ecc`,`0a968b42`) /*T![clustered_index] CLUSTERED */,
  UNIQUE KEY `e521f911` (`0a968b42`),
  KEY `17d3c2c4` (`12db5ecc`)
) ENGINE=InnoDB DEFAULT CHARSET=ascii COLLATE=ascii_bin COMMENT='9302fd19';

INSERT INTO `87f3b2f7` VALUES('ir1z,f1,e6yd','i71o','13mdezixgcn'),('ir1z,f1,e6yd','d','13mdezixgcn'),('nwbk','8ue','13mdezixgcn');

select     trim( `87f3b2f7`.`12db5ecc` ) as r0 , var_samp( `87f3b2f7`.`0a968b42` ) as r1 from `87f3b2f7` where `87f3b2f7`.`12db5ecc` between 'e6yd' and 'z' or `87f3b2f7`.`0a968b42` <> '8ue' group by `87f3b2f7`.`12db5ecc`  order by r0,r1;

select  /*+ use_index_merge( `87f3b2f7` ) */ /*+ agg_to_cop() stream_agg() */  trim( `87f3b2f7`.`12db5ecc` ) as r0 , var_samp( `87f3b2f7`.`0a968b42` ) as r1 from `87f3b2f7` where `87f3b2f7`.`12db5ecc` between 'e6yd' and 'z' or `87f3b2f7`.`0a968b42` <> '8ue' group by `87f3b2f7`.`12db5ecc`  order by r0,r1;

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

Two queries got the same results.

3. What did you see instead (Required)

mysql> select  /*+ use_index_merge( `87f3b2f7` ) */ /*+ agg_to_cop() stream_agg() */  trim( `87f3b2f7`.`12db5ecc` ) as r0 , var_samp( `87f3b2f7`.`0a968b42` ) as r1 from `87f3b2f7` where `87f3b2f7`.`12db5ecc` between 'e6yd' and 'z' or `87f3b2f7`.`0a968b42` <> '8ue' group by `87f3b2f7`.`12db5ecc`  order by r0,r1;
+--------------+------+
| r0           | r1   |
+--------------+------+
| ir1z,f1,e6yd |   18 |
+--------------+------+
1 row in set (0.01 sec)

mysql> select     trim( `87f3b2f7`.`12db5ecc` ) as r0 , var_samp( `87f3b2f7`.`0a968b42` ) as r1 from `87f3b2f7` where `87f3b2f7`.`12db5ecc` between 'e6yd' and 'z' or `87f3b2f7`.`0a968b42` <> '8ue' group by `87f3b2f7`.`12db5ecc`  order by r0,r1;
+--------------+------+
| r0           | r1   |
+--------------+------+
| ir1z,f1,e6yd |   18 |
| nwbk         | NULL |
+--------------+------+
2 rows in set (0.00 sec)

4. What is your TiDB version? (Required)

master

[chrysan]

Simplified reproduction:

mysql> select   *  from `87f3b2f7` where `87f3b2f7`.`12db5ecc` between 'e6yd' and 'z' or `87f3b2f7`.`0a968b42` <> '8ue';
+--------------+----------+-------------+
| 12db5ecc     | 0a968b42 | af528200    |
+--------------+----------+-------------+
| nwbk         | 8ue      | 13mdezixgcn |
| ir1z,f1,e6yd | i71o     | 13mdezixgcn |
| ir1z,f1,e6yd | d        | 13mdezixgcn |
+--------------+----------+-------------+
3 rows in set (0.00 sec)

mysql> select  /*+ use_index_merge( `87f3b2f7` ) */ *  from `87f3b2f7` where `87f3b2f7`.`12db5ecc` between 'e6yd' and 'z' or `87f3b2f7`.`0a968b42` <> '8ue';
+--------------+----------+-------------+
| 12db5ecc     | 0a968b42 | af528200    |
+--------------+----------+-------------+
| ir1z,f1,e6yd | i71o     | 13mdezixgcn |
| ir1z,f1,e6yd | d        | 13mdezixgcn |
+--------------+----------+-------------+

mysql> explain analyze select  /*+ use_index_merge( `87f3b2f7` ) */ *  from `87f3b2f7` where `87f3b2f7`.`12db5ecc` between 'e6yd' and 'z' or `87f3b2f7`.`0a968b42` <> '8ue';
+---------------------------------+---------+---------+-----------+------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------+------+
| id                              | estRows | actRows | task      | access object                            | execution info                                                                                                                                                                                                                                              | operator info                                                                                                                                                                                                 | memory  | disk |
+---------------------------------+---------+---------+-----------+------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------+------+
| Selection_5                     | 0.80    | 2       | root      |                                          | time:1.32ms, loops:2                                                                                                                                                                                                                                        | or(and(ge(test.87f3b2f7.12db5ecc, "e6yd"), le(test.87f3b2f7.12db5ecc, "z")), ne(test.87f3b2f7.0a968b42, "8ue"))                                                                                               | 2.49 KB | N/A  |
| └─IndexMerge_9                  | 1.00    | 2       | root      |                                          | time:1.22ms, loops:3, index_task:{fetch_handle:543.709µs, merge:5.625µs}, table_task:{num:1, concurrency:5, fetch_row:444.5µs, wait_time:715.958µs}                                                                                                         | type: union                                                                                                                                                                                                   | 40.5 KB | N/A  |
|   ├─TableFullScan_6(Build)      | 0.00    | 0       | cop[tikv] | table:87f3b2f7                           | time:0s, loops:0                                                                                                                                                                                                                                            | keep order:false, stats:pseudo                                                                                                                                                                                | 0 Bytes | N/A  |
|   ├─IndexRangeScan_7(Build)     | 1.00    | 2       | cop[tikv] | table:87f3b2f7, index:e521f911(0a968b42) | time:552.7µs, loops:2, cop_task: {num: 1, max: 423.4µs, proc_keys: 0, rpc_num: 1, rpc_time: 386.3µs, copr_cache_hit_ratio: 0.00, build_task_duration: 29.5µs, max_distsql_concurrency: 1}, tikv_task:{time:353.7µs, loops:0}                                | range:["",""], ["soo2","soo2"], ["4s4j","4s4j"], ["qi9om","qi9om"], ["i71o","i71o"], ["qon","qon"], ["3","3"], ["3feh","3feh"], ["6o1i","6o1i"], ["5yebx","5yebx"], ["d","d"], keep order:false, stats:pseudo | N/A     | N/A  |
|   └─TableRowIDScan_8(Probe)     | 1.00    | 2       | cop[tikv] | table:87f3b2f7                           | time:360.3µs, loops:2, cop_task: {num: 1, max: 295.3µs, proc_keys: 0, rpc_num: 1, rpc_time: 272.5µs, copr_cache_hit_ratio: 0.00, build_task_duration: 7.58µs, max_distsql_concurrency: 1, max_extra_concurrency: 1}, tikv_task:{time:256.9µs, loops:0}      | keep order:false, stats:pseudo                                                                                                                                                                                | N/A     | N/A  |
+---------------------------------+---------+---------+-----------+------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------+------+
5 rows in set (0.00 sec)

There's something wrong when one of the children of IndexMerge(union) is TableFullScan, actRows should be 3 but 0.

@time-and-fate @guo-shaoge please take a look at this issue.

[time-and-fate]

Renamed tables and columns:

CREATE TABLE t (
  a set('nwbk','r5','1ad3u','van','ir1z','y','9m','f1','z','e6yd','wfev') NOT NULL DEFAULT 'ir1z,f1,e6yd',
  b enum('soo2','4s4j','qi9om','8ue','i71o','qon','3','3feh','6o1i','5yebx','d') NOT NULL DEFAULT '8ue',
  c varchar(66) DEFAULT '13mdezixgcn',
  PRIMARY KEY (`a`,`b`) /*T![clustered_index] CLUSTERED */,
  UNIQUE KEY `ib` (`b`),
  KEY `ia` (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=ascii COLLATE=ascii_bin;
INSERT INTO t VALUES('ir1z,f1,e6yd','i71o','13mdezixgcn'),('ir1z,f1,e6yd','d','13mdezixgcn'),('nwbk','8ue','13mdezixgcn');
select * from t where a between 'e6yd' and 'z' or b <> '8ue';
select /*+ use_index_merge(t) */ * from t where a between 'e6yd' and 'z' or b <> '8ue';