Description:
The server (versions 5.0+) is missing a single expected row for the following query:
SELECT table1 .`col_varchar_key`
FROM C table1 LEFT JOIN C table2 ON table2 .`col_int_key` = table1 .`col_int_key`
WHERE table2 .`col_int_nokey` < ALL (
SELECT `col_int_nokey`
FROM CC
WHERE `pk` < table1 .`col_int_nokey` ) ;
The following diff is a result of comparison to Javadb and Postgres:
/* Diff: */
/* --- /tmp//randgen17717-1263237338-server0.dump 2010-01-11 14:15:38.000000000 -0500
# +++ /tmp//randgen17717-1263237338-server1.dump 2010-01-11 14:15:38.000000000 -0500
# @@ -30,6 +30,7 @@
# n
# n
# o
# +r
# t
# t
# t */
Both of the comparison DBMS's return an 'r' row and experimentation can show no reason for MySQL missing this row
EXPLAIN output (5.0):
EXPLAIN SELECT table1 .`col_varchar_key`
FROM C table1 LEFT JOIN C table2 ON table2 .`col_int_key` = table1 .`col_int_key`
WHERE table2 .`col_int_nokey` < ALL (
SELECT `col_int_nokey`
FROM CC
WHERE `pk` < table1 .`col_int_nokey` ) ;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY table1 ALL col_int_key NULL NULL NULL 20
1 PRIMARY table2 ref col_int_key col_int_key 5 test.table1.col_int_key 1 Using where
2 DEPENDENT SUBQUERY CC ALL PRIMARY NULL NULL NULL 20 Using where
How to repeat:
MTR test case:
#/* Server0: MySQL 6.0.14-alpha-gcov-debug-log */
#/* Server1: JavaDB Version N/A */
#/* Begin test case for query 0 */
--disable_warnings
DROP TABLE /*! IF EXISTS */ CC;
DROP TABLE /*! IF EXISTS */ C;
--enable_warnings
CREATE TABLE `CC` (
`pk` int(11) NOT NULL AUTO_INCREMENT,
`col_int_nokey` int(11) DEFAULT NULL,
`col_int_key` int(11) DEFAULT NULL,
`col_varchar_key` varchar(1) DEFAULT NULL,
PRIMARY KEY (`pk`),
KEY `col_int_key` (`col_int_key`),
KEY `col_varchar_key` (`col_varchar_key`,`col_int_key`)
) ENGINE=InnoDB AUTO_INCREMENT=30 DEFAULT CHARSET=latin1;
INSERT INTO `CC` VALUES (10,7,8,'v');
INSERT INTO `CC` VALUES (11,1,9,'r');
INSERT INTO `CC` VALUES (12,5,9,'a');
INSERT INTO `CC` VALUES (13,3,186,'m');
INSERT INTO `CC` VALUES (14,6,NULL,'y');
INSERT INTO `CC` VALUES (15,92,2,'j');
INSERT INTO `CC` VALUES (16,7,3,'d');
INSERT INTO `CC` VALUES (17,NULL,0,'z');
INSERT INTO `CC` VALUES (18,3,133,'e');
INSERT INTO `CC` VALUES (19,5,1,'h');
INSERT INTO `CC` VALUES (20,1,8,'b');
INSERT INTO `CC` VALUES (21,2,5,'s');
INSERT INTO `CC` VALUES (22,NULL,5,'e');
INSERT INTO `CC` VALUES (23,1,8,'j');
INSERT INTO `CC` VALUES (24,0,6,'e');
INSERT INTO `CC` VALUES (25,210,51,'f');
INSERT INTO `CC` VALUES (26,8,4,'v');
INSERT INTO `CC` VALUES (27,7,7,'x');
INSERT INTO `CC` VALUES (28,5,6,'m');
INSERT INTO `CC` VALUES (29,NULL,4,'c');
CREATE TABLE `C` (
`pk` int(11) NOT NULL AUTO_INCREMENT,
`col_int_nokey` int(11) DEFAULT NULL,
`col_int_key` int(11) DEFAULT NULL,
`col_varchar_key` varchar(1) DEFAULT NULL,
PRIMARY KEY (`pk`),
KEY `col_int_key` (`col_int_key`),
KEY `col_varchar_key` (`col_varchar_key`,`col_int_key`)
) ENGINE=InnoDB AUTO_INCREMENT=21 DEFAULT CHARSET=latin1;
INSERT INTO `C` VALUES (1,NULL,2,'w');
INSERT INTO `C` VALUES (2,7,9,'m');
INSERT INTO `C` VALUES (3,9,3,'m');
INSERT INTO `C` VALUES (4,7,9,'k');
INSERT INTO `C` VALUES (5,4,NULL,'r');
INSERT INTO `C` VALUES (6,2,9,'t');
INSERT INTO `C` VALUES (7,6,3,'j');
INSERT INTO `C` VALUES (8,8,8,'u');
INSERT INTO `C` VALUES (9,NULL,8,'h');
INSERT INTO `C` VALUES (10,5,53,'o');
INSERT INTO `C` VALUES (11,NULL,0,NULL);
INSERT INTO `C` VALUES (12,6,5,'k');
INSERT INTO `C` VALUES (13,188,166,'e');
INSERT INTO `C` VALUES (14,2,3,'n');
INSERT INTO `C` VALUES (15,1,0,'t');
INSERT INTO `C` VALUES (16,1,1,'c');
INSERT INTO `C` VALUES (17,0,9,'m');
INSERT INTO `C` VALUES (18,9,5,'y');
INSERT INTO `C` VALUES (19,NULL,6,'f');
INSERT INTO `C` VALUES (20,4,2,'d');
SELECT table1 .`col_varchar_key`
FROM C table1 LEFT JOIN C table2 ON table2 .`col_int_key` = table1 .`col_int_key`
WHERE table2 .`col_int_nokey` < ALL (
SELECT `col_int_nokey`
FROM CC
WHERE `pk` < table1 .`col_int_nokey` ) ;
# This query is just here to help analyze things
# You can view the results of this query to see that the 'r' row should be included
# There are other rows with the same values for the columns used in the queries
# Discard this query on adding this test to the MTR suite.
SELECT table1 .`col_varchar_key`, table2.col_int_nokey, table1.col_int_nokey
FROM C table1 LEFT JOIN C table2 ON table2 .`col_int_key` = table1 .`col_int_key`;
/* Diff: */
/* --- /tmp//randgen17717-1263237338-server0.dump 2010-01-11 14:15:38.000000000 -0500
# +++ /tmp//randgen17717-1263237338-server1.dump 2010-01-11 14:15:38.000000000 -0500
# @@ -30,6 +30,7 @@
# n
# n
# o
# +r
# t
# t
# t */
DROP TABLE CC;
DROP TABLE C;
#/* End of test case for query 0 */
