executor: indexMergeJoin, indexHashJoin and IndexJoin error

[fzhedu]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. What did you do?

CREATE TABLE `A` (
  `col_int` int(11) DEFAULT NULL,
  `col_varchar_10_utf8` varchar(10) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
  `pk` int(11) NOT NULL AUTO_INCREMENT,
  `col_varchar_10_latin1` varchar(10) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL,
  `col_int_not_null` int(11) NOT NULL,
  `col_varchar_1024_utf8_not_null` varchar(1024) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
  `col_varchar_1024_utf8` varchar(1024) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
  `col_varchar_1024_latin1_not_null` varchar(1024) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL,
  `col_varchar_10_utf8_not_null` varchar(10) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
  `col_varchar_1024_latin1` varchar(1024) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL,
  `col_varchar_10_latin1_not_null` varchar(10) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL,
  PRIMARY KEY (`pk`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin AUTO_INCREMENT=2000001;

INSERT INTO `A` VALUES (5,'t',1,'n',7,'r','z','f','x','e','s');
INSERT INTO `A` VALUES (1,'f',2,'s',6,'j','j','s','x','b','j');
INSERT INTO `A` VALUES (1,'y',3,NULL,5,'k','u','k','e','w','g');
INSERT INTO `A` VALUES (2,'y',4,'v',2,'t',NULL,'c','s','q','l');
INSERT INTO `A` VALUES (3,'o',5,NULL,0,'q',NULL,'c','n','h','h');
INSERT INTO `A` VALUES (6,'w',6,'t',9,'k','b','j','k','q','k');
INSERT INTO `A` VALUES (9,'o',7,'r',0,'z','v','s','r','d','u');
INSERT INTO `A` VALUES (5,'d',8,'y',9,'x','v','q','w','i','t');
INSERT INTO `A` VALUES (2,'w',9,'f',4,'x',NULL,'c','a','h','p');
INSERT INTO `A` VALUES (NULL,'f',10,'j',1,'e','z','n','y','j','x');
INSERT INTO `A` VALUES (0,NULL,11,'r',4,'b',NULL,'p','j','l','q');
INSERT INTO `A` VALUES (3,'p',12,'i',7,'p','p','u','w','h','s');
INSERT INTO `A` VALUES (0,'j',13,NULL,1,'n','i','s','s','n','o');
INSERT INTO `A` VALUES (9,'j',14,'n',3,'h','c','s','w',NULL,'u');
INSERT INTO `A` VALUES (NULL,'j',15,'z',6,'a',NULL,'u','s','r','w');
INSERT INTO `A` VALUES (9,'q',16,'a',8,'a','z','e','w','d','d');
INSERT INTO `A` VALUES (5,'q',17,'x',7,'w','g','w','l','f','k');
INSERT INTO `A` VALUES (0,'c',18,NULL,4,'u','a','r','i','n','u');
INSERT INTO `A` VALUES (1,'a',19,'u',2,'c','v','s','q','x','w');
INSERT INTO `A` VALUES (7,'g',20,'p',6,'q',NULL,'w','g','n','e');
INSERT INTO `A` VALUES (5,'g',21,'e',5,'h','h','f','b','t','j');
INSERT INTO `A` VALUES (1,'k',22,'u',9,'o','n','r','d',NULL,'h');
INSERT INTO `A` VALUES (5,NULL,23,'v',3,'k','c','k','q','o','s');
INSERT INTO `A` VALUES (NULL,NULL,24,NULL,7,'d','p','s','p','x','x');
INSERT INTO `A` VALUES (7,NULL,25,'m',4,'e','l','c','u',NULL,'y');
INSERT INTO `A` VALUES (5,NULL,26,'x',9,'d',NULL,'y','u',NULL,'v');
INSERT INTO `A` VALUES (NULL,'m',27,'y',7,'n','q','u','r','l','e');
INSERT INTO `A` VALUES (2,NULL,28,NULL,4,'w',NULL,'x','z','d','b');
INSERT INTO `A` VALUES (4,'y',29,NULL,6,'z','o','v','l','o','q');
INSERT INTO `A` VALUES (0,'o',30,'c',4,'d','y','y','w','q','d');
INSERT INTO `A` VALUES (1,'r',31,'b',7,'b','c','k','j','b','m');
INSERT INTO `A` VALUES (0,'u',32,'r',1,'y','h','d','d','q','j');
INSERT INTO `A` VALUES (NULL,'s',33,NULL,9,'t','o','o','g','f','r');
INSERT INTO `A` VALUES (7,'u',34,'m',8,'w','o','l','a','s','i');
INSERT INTO `A` VALUES (NULL,'r',35,'i',4,'p','e','l','q',NULL,'e');
INSERT INTO `A` VALUES (2,'c',36,NULL,4,'c','j','v','k',NULL,'b');
INSERT INTO `A` VALUES (3,'n',37,'p',5,'e','a','g','e','p','s');
INSERT INTO `A` VALUES (6,'s',38,'v',7,'j','b','k','e','x','n');
INSERT INTO `A` VALUES (2,'l',39,'e',8,'h','v','q','k','l','h');
INSERT INTO `A` VALUES (NULL,'m',40,'l',8,'q','f','p','g','c','f');
INSERT INTO `A` VALUES (7,'o',41,NULL,7,'h','p','t','o','s','u');
INSERT INTO `A` VALUES (9,'u',42,'m',6,'b','y','q','b','h','n');
INSERT INTO `A` VALUES (NULL,'w',43,'a',8,'i',NULL,'u','w','z','f');
INSERT INTO `A` VALUES (1,'w',44,'p',7,'f',NULL,'d','j','x','i');
INSERT INTO `A` VALUES (3,'z',45,'f',1,'n','s','d','u','o','t');
INSERT INTO `A` VALUES (4,'u',46,'m',5,'s','d','f','k',NULL,'a');
INSERT INTO `A` VALUES (0,NULL,47,'w',2,'t','l','a','x','a','g');
INSERT INTO `A` VALUES (7,'i',48,'x',7,'p','z','o','l','m','c');
INSERT INTO `A` VALUES (6,NULL,49,'r',0,'j','j','k','d','h','u');
INSERT INTO `A` VALUES (NULL,'n',50,'a',1,'o',NULL,'r','r','k','c');
INSERT INTO `A` VALUES (1,'h',51,'l',8,'r','v','k','h','t','s');
INSERT INTO `A` VALUES (NULL,NULL,52,'f',5,'e','i','z','f','e','q');
INSERT INTO `A` VALUES (4,NULL,53,'k',3,'i','b','d','j','u','s');
INSERT INTO `A` VALUES (2,NULL,54,'l',7,'n',NULL,'q','n','b','g');
INSERT INTO `A` VALUES (2,'x',55,'u',0,'k',NULL,'x','s','o','m');
INSERT INTO `A` VALUES (NULL,'t',56,'l',7,'f','r','g','d','t','n');
INSERT INTO `A` VALUES (3,'g',57,NULL,1,'b','k','a','x','q','f');
INSERT INTO `A` VALUES (8,'d',58,'s',3,'h','r','j','u','u','i');
INSERT INTO `A` VALUES (7,'z',59,'i',2,'k','r','x','d','g','u');
INSERT INTO `A` VALUES (NULL,'u',60,'w',7,'t','b','g','a',NULL,'a');
INSERT INTO `A` VALUES (7,'f',61,'p',1,'z','y','t','t','f','z');
INSERT INTO `A` VALUES (7,NULL,62,'x',9,'p','d','o','k',NULL,'q');
INSERT INTO `A` VALUES (1,'w',63,'q',6,'i','s','y','i',NULL,'y');
INSERT INTO `A` VALUES (NULL,NULL,64,'n',1,'m','k','v','b','i','u');
INSERT INTO `A` VALUES (3,'u',65,'k',7,'l','k','o','w','m','l');
INSERT INTO `A` VALUES (3,'m',66,'z',8,'i','y','e','o','f','k');
INSERT INTO `A` VALUES (1,'p',67,NULL,3,'b','u','p','m','h','u');
INSERT INTO `A` VALUES (8,'y',68,'s',0,'g','m','m','d','s','g');
INSERT INTO `A` VALUES (1,'k',69,'l',5,'p','i','h','y','r','b');
INSERT INTO `A` VALUES (0,'k',70,'l',1,'l','b','b','x','s','s');
INSERT INTO `A` VALUES (6,'n',71,'d',8,'v','e','y','h','j','a');
INSERT INTO `A` VALUES (3,NULL,72,'d',0,'k','x','j','f','c','m');
INSERT INTO `A` VALUES (NULL,'u',73,'c',3,'q','c','j','c','h','k');
INSERT INTO `A` VALUES (0,'o',74,NULL,1,'y','k','f','d','l','b');
INSERT INTO `A` VALUES (9,NULL,75,'x',8,'t','d','f','n','y','g');
INSERT INTO `A` VALUES (NULL,'m',76,'o',6,'b','h','q','k','k','o');
INSERT INTO `A` VALUES (NULL,'h',77,NULL,2,'p','a','i','v','f','z');
INSERT INTO `A` VALUES (NULL,'w',78,NULL,6,'g','m','b','y','v','a');
INSERT INTO `A` VALUES (0,'e',79,'y',0,'z',NULL,'v','m',NULL,'x');
INSERT INTO `A` VALUES (4,'g',80,'s',6,'p','k','y','d',NULL,'n');
INSERT INTO `A` VALUES (NULL,'g',81,'o',1,'k','x','t','k','o','z');
INSERT INTO `A` VALUES (7,NULL,82,'g',8,'a','s','f','u','q','c');
INSERT INTO `A` VALUES (7,'h',83,'c',1,'n','c','j','q','f','c');
INSERT INTO `A` VALUES (5,'u',84,'l',0,'k','d','d','a','a','g');
INSERT INTO `A` VALUES (9,'e',85,'d',1,'r','r','z','e','r','s');
INSERT INTO `A` VALUES (1,'g',86,'x',3,'x',NULL,'k','k',NULL,'p');
INSERT INTO `A` VALUES (7,NULL,87,NULL,9,'g','v','g','c',NULL,'v');
INSERT INTO `A` VALUES (5,'q',88,NULL,0,'l','q','u','l','d','i');
INSERT INTO `A` VALUES (5,'o',89,'u',7,'r',NULL,'c','p','l','h');
INSERT INTO `A` VALUES (8,'u',90,'h',1,'t','i','p','m','q','r');
INSERT INTO `A` VALUES (4,NULL,91,'k',2,'j','x','d','r','l','j');
INSERT INTO `A` VALUES (1,NULL,92,'v',7,'p','p','z','h','a','m');
INSERT INTO `A` VALUES (0,'w',93,'m',6,'c','d','j','h',NULL,'b');
INSERT INTO `A` VALUES (5,'i',94,'g',6,'b','e','m','u','x','g');
INSERT INTO `A` VALUES (2,'b',95,'l',2,'g','r','w','h','q','x');
INSERT INTO `A` VALUES (5,NULL,96,'r',0,'r','x','s','z','h','i');
INSERT INTO `A` VALUES (NULL,'p',97,'o',5,'a','e','y','w','l','d');
INSERT INTO `A` VALUES (5,'j',98,NULL,9,'a','i','b','s','r','w');
INSERT INTO `A` VALUES (6,'y',99,NULL,7,'x',NULL,'y','y','x','z');
INSERT INTO `A` VALUES (3,'a',100,'f',3,'b',NULL,'g','j',NULL,'a');


CREATE TABLE `V` (
  `pk` int(11) NOT NULL AUTO_INCREMENT,
  `col_varchar_10_utf8_not_null` varchar(10) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
  `col_varchar_1024_utf8` varchar(1024) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
  `col_int` int(11) DEFAULT NULL,
  `col_varchar_10_latin1_not_null` varchar(10) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL,
  `col_int_not_null` int(11) NOT NULL,
  `col_varchar_1024_latin1` varchar(1024) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL,
  `col_varchar_1024_utf8_not_null` varchar(1024) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
  `col_varchar_1024_latin1_not_null` varchar(1024) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL,
  `col_varchar_10_latin1` varchar(10) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL,
  `col_varchar_10_utf8` varchar(10) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
  PRIMARY KEY (`pk`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin AUTO_INCREMENT=2000001;
/*!40101 SET character_set_client = @saved_cs_client */;

LOCK TABLES `V` WRITE;
/*!40000 ALTER TABLE `V` DISABLE KEYS */;
INSERT INTO `V` VALUES (1,'g',NULL,8,'b',0,'o','s','b','j','k');
INSERT INTO `V` VALUES (2,'f','s',8,'p',9,'q','a','c',NULL,'j');
INSERT INTO `V` VALUES (3,'k','t',9,'f',7,'s','b','r','t','l');
INSERT INTO `V` VALUES (4,'h','q',NULL,'x',1,NULL,'d','c','f',NULL);
INSERT INTO `V` VALUES (5,'a','h',2,'o',9,NULL,'d','g',NULL,'g');
INSERT INTO `V` VALUES (6,'u','c',2,'l',4,'b','s','o','j','l');
INSERT INTO `V` VALUES (7,'m',NULL,8,'j',2,'r','s','d',NULL,NULL);
INSERT INTO `V` VALUES (8,'u','o',4,'w',0,'l','p','a','f','l');
INSERT INTO `V` VALUES (9,'h','i',9,'d',7,'e','f','g','g','f');
INSERT INTO `V` VALUES (10,'f',NULL,3,'s',5,'u','a','z','n','l');
INSERT INTO `V` VALUES (11,'c','u',NULL,'v',3,'o','t','c','g','q');
INSERT INTO `V` VALUES (12,'o',NULL,5,'d',3,NULL,'t','z','t','b');
INSERT INTO `V` VALUES (13,'g','i',0,'o',0,'i','y','m','x','r');
INSERT INTO `V` VALUES (14,'y','b',7,'h',3,'a','h','j','y','a');
INSERT INTO `V` VALUES (15,'l','q',NULL,'d',1,'r','s','d','i','z');
INSERT INTO `V` VALUES (16,'e','q',1,'y',4,'q','i','h','t','b');
INSERT INTO `V` VALUES (17,'g','u',1,'e',2,'e','p','b','j','l');
INSERT INTO `V` VALUES (18,'t','k',9,'y',6,'q','h','n','k',NULL);
INSERT INTO `V` VALUES (19,'h','m',7,'z',0,'t','u','r','x','f');
INSERT INTO `V` VALUES (20,'l','p',NULL,'s',6,NULL,'s','d',NULL,'r');
INSERT INTO `V` VALUES (21,'z','n',NULL,'d',2,'d','z','k','e','a');

2. What did you expect to see?

SELECT /*+ tidb_hj(table1)*/   (  table2 . `pk` ) AS field1 , (  table2 . `col_int` ) AS field3 FROM  A AS table1  JOIN V AS table2 ON  table1 . `pk` =  table2 . `col_int`  and  table1 . `pk` = table2 . `pk` ;
+--------+--------+
| field1 | field3 |
+--------+--------+
|      9 |      9 |
+--------+--------+
1 row in set (0.01 sec)

3. What did you see instead?

SELECT    (  table2 . `pk` ) AS field1 , (  table2 . `col_int` ) AS field3 FROM  A AS table1  JOIN V AS table2 ON  table1 . `pk` =  table2 . `col_int`  and  table1 . `pk` = table2 . `pk` ;
+--------+--------+
| field1 | field3 |
+--------+--------+
|      9 |      9 |
|      9 |      9 |
|      9 |      9 |
+--------+--------+
3 rows in set (0.00 sec)

explain SELECT    (  table2 . `pk` ) AS field1 , (  table2 . `col_int` ) AS field3 FROM  A AS table1  JOIN V AS table2 ON  table1 . `pk` =  table2 . `col_int`  and  table1 . `pk` = table2 . `pk` ;
+------------------------------+---------+-----------+---------------+---------------------------------------------------------------------------------------------------------------------------------------+
| id                           | estRows | task      | access object | operator info                                                                                                                         |
+------------------------------+---------+-----------+---------------+---------------------------------------------------------------------------------------------------------------------------------------+
| IndexMergeJoin_14            | 26.22   | root      |               | inner join, inner:TableReader_12, outer key:randgen_test.v.col_int, randgen_test.v.pk, inner key:randgen_test.a.pk, randgen_test.a.pk |
| ├─TableReader_37(Build)      | 20.98   | root      |               | data:Selection_36                                                                                                                     |
| │ └─Selection_36             | 20.98   | cop[tikv] |               | not(isnull(randgen_test.v.col_int))                                                                                                   |
| │   └─TableFullScan_35       | 21.00   | cop[tikv] | table:table2  | keep order:false, stats:pseudo                                                                                                        |
| └─TableReader_12(Probe)      | 1.00    | root      |               | data:TableRangeScan_11                                                                                                                |
|   └─TableRangeScan_11        | 1.00    | cop[tikv] | table:table1  | range: decided by [randgen_test.v.col_int randgen_test.v.pk], keep order:true, stats:pseudo                                           |
+------------------------------+---------+-----------+---------------+---------------------------------------------------------------------------------------------------------------------------------------+

mysql>  SELECT /*+INL_JOIN(table1,table2,table3,table4,table5,table6,table7,table8,table9,table10,table11,table12)*/   (  table2 . `pk` ) AS field1 , (  table2 . `col_int` ) AS field3 FROM  A AS table1  JOIN V AS table2 ON  table1 . `pk` =  table2 . `col_int`  and  table1 . `pk` = table2 . `pk` ;
+--------+--------+
| field1 | field3 |
+--------+--------+
|      9 |      9 |
|      9 |      9 |
|      9 |      9 |
+--------+--------+
3 rows in set, 1 warning (0.00 sec)

mysql> explain  SELECT /*+INL_JOIN(table1,table2,table3,table4,table5,table6,table7,table8,table9,table10,table11,table12)*/   (  table2 . `pk` ) AS field1 , (  table2 . `col_int` ) AS field3 FROM  A AS table1  JOIN V AS table2 ON  table1 . `pk` =  table2 . `col_int`  and  table1 . `pk` = table2 . `pk` ;
+------------------------------+---------+-----------+---------------+-------------------------------------------------------------------------------------------------------+
| id                           | estRows | task      | access object | operator info                                                                                         |
+------------------------------+---------+-----------+---------------+-------------------------------------------------------------------------------------------------------+
| IndexJoin_19                 | 26.22   | root      |               | inner join, inner:TableReader_18, outer key:test.v.col_int, test.v.pk, inner key:test.a.pk, test.a.pk |
| ├─TableReader_30(Build)      | 20.98   | root      |               | data:Selection_29                                                                                     |
| │ └─Selection_29             | 20.98   | cop[tikv] |               | not(isnull(test.v.col_int))                                                                           |
| │   └─TableFullScan_28       | 21.00   | cop[tikv] | table:table2  | keep order:false, stats:pseudo                                                                        |
| └─TableReader_18(Probe)      | 1.00    | root      |               | data:TableRangeScan_17                                                                                |
|   └─TableRangeScan_17        | 1.00    | cop[tikv] | table:table1  | range: decided by [test.v.col_int test.v.pk], keep order:false, stats:pseudo                          |
+------------------------------+---------+-----------+---------------+-------------------------------------------------------------------------------------------------------+
6 rows in set, 1 warning (0.00 sec)

mysql>  SELECT /*+INL_HASH_JOIN(table1,table2,table3,table4,table5,table6,table7,table8,table9,table10,table11,table12)*/   (  table2 . `pk` ) AS field1 , (  table2 . `col_int` ) AS field3 FROM  A AS table1  JOIN V AS table2 ON  table1 . `pk` =  table2 . `col_int`  and  table1 . `pk` = table2 . `pk` ;
+--------+--------+
| field1 | field3 |
+--------+--------+
|      9 |      9 |
|      9 |      9 |
|      9 |      9 |
+--------+--------+
3 rows in set, 1 warning (0.00 sec)

mysql> explain  SELECT /*+INL_HASH_JOIN(table1,table2,table3,table4,table5,table6,table7,table8,table9,table10,table11,table12)*/   (  table2 . `pk` ) AS field1 , (  table2 . `col_int` ) AS field3 FROM  A AS table1  JOIN V AS table2 ON  table1 . `pk` =  table2 . `col_int`  and  table1 . `pk` = table2 . `pk` ;
+------------------------------+---------+-----------+---------------+-------------------------------------------------------------------------------------------------------+
| id                           | estRows | task      | access object | operator info                                                                                         |
+------------------------------+---------+-----------+---------------+-------------------------------------------------------------------------------------------------------+
| IndexHashJoin_25             | 26.22   | root      |               | inner join, inner:TableReader_18, outer key:test.v.col_int, test.v.pk, inner key:test.a.pk, test.a.pk |
| ├─TableReader_30(Build)      | 20.98   | root      |               | data:Selection_29                                                                                     |
| │ └─Selection_29             | 20.98   | cop[tikv] |               | not(isnull(test.v.col_int))                                                                           |
| │   └─TableFullScan_28       | 21.00   | cop[tikv] | table:table2  | keep order:false, stats:pseudo                                                                        |
| └─TableReader_18(Probe)      | 1.00    | root      |               | data:TableRangeScan_17                                                                                |
|   └─TableRangeScan_17        | 1.00    | cop[tikv] | table:table1  | range: decided by [test.v.col_int test.v.pk], keep order:false, stats:pseudo                          |
+------------------------------+---------+-----------+---------------+-------------------------------------------------------------------------------------------------------+
6 rows in set, 1 warning (0.00 sec)

4. What version of TiDB are you using? (tidb-server -V or run select tidb_version(); on TiDB)

5.7.25-TiDB-v4.0.0-beta-523-g7eba696bb

[XuHuaiyu]

minor case to reproduce this problem:

CREATE TABLE `t1` (
  `id` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ;
CREATE TABLE `t2` (
  `a` int(11) DEFAULT NULL,
  `b` int(11) DEFAULT NULL
);
insert into t1 values(1);
insert into t2 values(1,1), (2,1);

tidb> select /*+ hash_join(t1)*/ * from t1 join t2 on t2.b=t1.id and t2.a=t1.id;
+----+------+------+
| id | a    | b    |
+----+------+------+
|  1 |    1 |    1 |
+----+------+------+
1 row in set (0.01 sec)

tidb> select /*+ inl_join(t1)*/ * from t1 join t2 on t2.b=t1.id and t2.a=t1.id;
+----+------+------+
| id | a    | b    |
+----+------+------+
|  1 |    1 |    1 |
|  1 |    1 |    1 |
+----+------+------+
2 rows in set (0.01 sec)