Bug #59302 Incorrect rows returned for a subquery with left join when ICP is on
Submitted: 5 Jan 2011 9:21 Modified: 31 Jan 2011 10:51
Reporter: SaiKumar V Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S2 (Serious)
Version:5.6.1 OS:Linux (RedHat x86)
Assigned to: Assigned Account CPU Architecture:Any
Tags: index_condition_pushdown, optimizer_switch

[5 Jan 2011 9:21] SaiKumar V
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  );
[11 Jan 2011 14:59] Olav Sandstå
This is likely caused by the same underlying issue as Bug#59259. The query triggers use of the "adaptive hash index" in InnoDB and this causes the evaluation of the pushed index condition to be skipped (verified by instrumenting the relevant code in row_search_for_mysql()).

I am leaving this bug in the current state and will verify that it is solved by the the fix for Bug#59259.
[31 Jan 2011 10:51] Olav Sandstå
I have verified that this bug is solved by the fix for Bug#59259. Closing it as a
duplicate of Bug#59259.