Description:
When ICP is on (Default) in 5.6.1 ,incorrect rows returned for a query with left join and condition < (less then)
mysql> SELECT `pk` FROM C WHERE `pk` IN ( SELECT PARENT1 .`pk` FROM CC PARENT1 LEFT JOIN BB ON BB.`col_int_nokey`=PARENT1.col_int_nokey WHERE PARENT1 .`pk` < 0 );
+----+
| pk |
+----+
| 10 |
| 11 |
| 12 |
| 13 |
| 14 |
| 15 |
| 16 |
| 17 |
| 18 |
| 19 |
| 20 |
+----+
11 rows in set (0.00 sec)
mysql> explain SELECT `pk` FROM C WHERE `pk` IN ( SELECT PARENT1 .`pk` FROM CC PARENT1 LEFT JOIN BB ON BB.`col_int_nokey`=PARENT1.col_int_nokey WHERE PARENT1 .`pk` < 0 );
+----+--------------------+---------+--------+---------------+---------+---------+------+------+-----------------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------------+---------+--------+---------------+---------+---------+------+------+-----------------------------------------------------------+
| 1 | PRIMARY | C | index | NULL | PRIMARY | 4 | NULL | 20 | Using where; Using index |
| 2 | DEPENDENT SUBQUERY | PARENT1 | eq_ref | PRIMARY | PRIMARY | 4 | func | 1 | Using index condition |
| 2 | DEPENDENT SUBQUERY | BB | ALL | NULL | NULL | NULL | NULL | 1 | Using where; Using join buffer (BNL, incremental buffers) |
+----+--------------------+---------+--------+---------------+---------+---------+------+------+-----------------------------------------------------------+
3 rows in set (0.00 sec)
mysql> set optimizer_switch='index_condition_pushdown=off';Query OK, 0 rows affected (0.00 sec)
mysql> SELECT `pk` FROM C WHERE `pk` IN ( SELECT PARENT1 .`pk` FROM CC PARENT1 LEFT JOIN BB ON BB.`col_int_nokey`=PARENT1.col_int_nokey WHERE PARENT1 .`pk` < 0 );
Empty set (0.00 sec)
mysql> explain SELECT `pk` FROM C WHERE `pk` IN ( SELECT PARENT1 .`pk` FROM CC PARENT1 LEFT JOIN BB ON BB.`col_int_nokey`=PARENT1.col_int_nokey WHERE PARENT1 .`pk` < 0 );
+----+--------------------+---------+--------+---------------+---------+---------+------+------+-----------------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------------+---------+--------+---------------+---------+---------+------+------+-----------------------------------------------------------+
| 1 | PRIMARY | C | index | NULL | PRIMARY | 4 | NULL | 20 | Using where; Using index |
| 2 | DEPENDENT SUBQUERY | PARENT1 | eq_ref | PRIMARY | PRIMARY | 4 | func | 1 | Using where |
| 2 | DEPENDENT SUBQUERY | BB | ALL | NULL | NULL | NULL | NULL | 1 | Using where; Using join buffer (BNL, incremental buffers) |
+----+--------------------+---------+--------+---------------+---------+---------+------+------+-----------------------------------------------------------+
3 rows in set (0.00 sec)
How to repeat:
Found on 5.6.1 release binary, mysql Ver 14.14 Distrib 5.6.1-m5
The RQG grammar used was subquery_semijoin_nested.yy , with seed=1293457140.
Steps to repro:-
CREATE TABLE `CC` (
`pk` int(11) NOT NULL AUTO_INCREMENT,
`col_int_nokey` int(11) DEFAULT NULL,
`col_varchar_nokey` varchar(1) DEFAULT NULL,
PRIMARY KEY (`pk`)
) ENGINE=InnoDB AUTO_INCREMENT=30 DEFAULT CHARSET=latin1;
INSERT INTO `CC` VALUES (10,7,'v');
INSERT INTO `CC` VALUES (11,1,'r');
INSERT INTO `CC` VALUES (12,5,'a');
INSERT INTO `CC` VALUES (13,3,'m');
INSERT INTO `CC` VALUES (14,6,'y');
INSERT INTO `CC` VALUES (15,92,'j');
INSERT INTO `CC` VALUES (16,7,'d');
INSERT INTO `CC` VALUES (17,NULL,'z');
INSERT INTO `CC` VALUES (18,3,'e');
INSERT INTO `CC` VALUES (19,5,'h');
INSERT INTO `CC` VALUES (20,1,'b');
INSERT INTO `CC` VALUES (21,2,'s');
INSERT INTO `CC` VALUES (22,NULL,'e');
INSERT INTO `CC` VALUES (23,1,'j');
INSERT INTO `CC` VALUES (24,0,'e');
INSERT INTO `CC` VALUES (25,210,'f');
INSERT INTO `CC` VALUES (26,8,'v');
INSERT INTO `CC` VALUES (27,7,'x');
INSERT INTO `CC` VALUES (28,5,'m');
INSERT INTO `CC` VALUES (29,NULL,'c');
CREATE TABLE `BB` (
`pk` int(11) NOT NULL AUTO_INCREMENT,
`col_int_nokey` int(11) DEFAULT NULL,
`col_varchar_nokey` varchar(1) DEFAULT NULL,
PRIMARY KEY (`pk`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=latin1;
INSERT INTO `BB` VALUES (10,8,NULL);
CREATE TABLE `C` (
`pk` int(11) NOT NULL AUTO_INCREMENT,
`col_int_nokey` int(11) DEFAULT NULL,
`col_varchar_nokey` varchar(1) DEFAULT NULL,
PRIMARY KEY (`pk`)
) ENGINE=InnoDB AUTO_INCREMENT=21 DEFAULT CHARSET=latin1;
INSERT INTO `C` VALUES (1,NULL,'w');
INSERT INTO `C` VALUES (2,7,'m');
INSERT INTO `C` VALUES (3,9,'m');
INSERT INTO `C` VALUES (4,7,'k');
INSERT INTO `C` VALUES (5,4,'r');
INSERT INTO `C` VALUES (6,2,'t');
INSERT INTO `C` VALUES (7,6,'j');
INSERT INTO `C` VALUES (8,8,'u');
INSERT INTO `C` VALUES (9,NULL,'h');
INSERT INTO `C` VALUES (10,5,'o');
INSERT INTO `C` VALUES (11,NULL,NULL);
INSERT INTO `C` VALUES (12,6,'k');
INSERT INTO `C` VALUES (13,188,'e');
INSERT INTO `C` VALUES (14,2,'n');
INSERT INTO `C` VALUES (15,1,'t');
INSERT INTO `C` VALUES (16,1,'c');
INSERT INTO `C` VALUES (17,0,'m');
INSERT INTO `C` VALUES (18,9,'y');
INSERT INTO `C` VALUES (19,NULL,'f');
INSERT INTO `C` VALUES (20,4,'d');
SELECT `pk` FROM C WHERE `pk` IN ( SELECT PARENT1 .`pk` FROM CC PARENT1 LEFT JOIN BB ON BB.`col_int_nokey`=PARENT1.col_int_nokey WHERE PARENT1 .`pk` < 0 );