Description:
The MIN() function is not working properly for certain queries without the use of indexes:
For this query (from the test case):
SELECT MIN( table1 .`col_int_key` )
FROM C table1 LEFT JOIN (
SELECT SUBQUERY1_t1 .*
FROM C SUBQUERY1_t1 JOIN ( C SUBQUERY1_t2 STRAIGHT_JOIN BB SUBQUERY1_t3 ON SUBQUERY1_t3 .`pk` = SUBQUERY1_t2 .`pk` ) ON SUBQUERY1_t3 .`col_int_nokey` = SUBQUERY1_t2 .`pk` ) table2 ON table2 .`pk` ORDER BY table1.col_int_key ;
We will either get NULL or 0 depending on whether or not we are using indexes other than the pk, respectively.
Looking at just the rows returned (no MIN), we get identical result sets:
SELECT SUBQUERY1_t1 .*
FROM C SUBQUERY1_t1 JOIN ( C SUBQUERY1_t2 STRAIGHT_JOIN BB SUBQUERY1_t3 ON SUBQUERY1_t3 .`pk` = SUBQUERY1_t2 .`pk` ) ON SUBQUERY1_t3 .`col_int_nokey` = SUBQUERY1_t2 .`pk` ) table2 ON table2 .`pk` ORDER BY table1.col_int_key ;
col_int_key
NULL
0
0
1
2
2
3
3
3
5
5
6
8
8
9
9
9
9
53
166
EXPLAIN (with index):
EXPLAIN SELECT MIN( table1 .`col_int_key` )
FROM C table1 LEFT JOIN (
SELECT SUBQUERY1_t1 .*
FROM C SUBQUERY1_t1 JOIN ( C SUBQUERY1_t2 STRAIGHT_JOIN BB SUBQUERY1_t3 ON SUBQUERY1_t3 .`pk` = SUBQUERY1_t2 .`pk` ) ON SUBQUERY1_t3 .`col_int_nokey` = SUBQUERY1_t2 .`pk` ) table2 ON table2 .`pk` ;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Select tables optimized away
2 DERIVED SUBQUERY1_t1 ALL NULL NULL NULL NULL 20
2 DERIVED SUBQUERY1_t2 index PRIMARY PRIMARY 4 NULL 20 Using index
2 DERIVED SUBQUERY1_t3 ALL PRIMARY NULL NULL NULL 1 Using where
EXPLAIN (without index other than pk):
EEXPLAIN SELECT MIN( table1 .`col_int_key` )
FROM C table1 LEFT JOIN (
SELECT SUBQUERY1_t1 .*
FROM C SUBQUERY1_t1 JOIN ( C SUBQUERY1_t2 STRAIGHT_JOIN BB SUBQUERY1_t3 ON SUBQUERY1_t3 .`pk` = SUBQUERY1_t2 .`pk` ) ON SUBQUERY1_t3 .`col_int_nokey` = SUBQUERY1_t2 .`pk` ) table2 ON table2 .`pk` ;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY <derived2> system NULL NULL NULL NULL 0 const row not found
1 PRIMARY table1 ALL NULL NULL NULL NULL 20
2 DERIVED SUBQUERY1_t1 ALL NULL NULL NULL NULL 20
2 DERIVED SUBQUERY1_t2 index PRIMARY PRIMARY 4 NULL 20 Using index
2 DERIVED SUBQUERY1_t3 ALL PRIMARY NULL NULL NULL 1 Using where
How to repeat:
MTR test case, set up for use with 6.0. Please comment / uncomment SET statements as needed for use with earlier versions of the server:
#/* Server0: MySQL 6.0.14-alpha-gcov-debug-log */
#/*!50400 SET SESSION optimizer_switch = 'firstmatch=off,index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,loosescan=off,materialization=off,semijoin=off' */;
#/*!50400 SET SESSION optimizer_use_mrr = 'force' */;
#/*!50400 SET SESSION engine_condition_pushdown = 'ON' */;
#/*!50400 SET SESSION join_cache_level = 1 */;
#/*!50400 SET SESSION debug = 'd,optimizer_no_icp,optimizer_innodb_ds_mrr' */;
#/* Begin test case for query 0 */
--disable_warnings
DROP TABLE /*! IF EXISTS */ BB;
DROP TABLE /*! IF EXISTS */ C;
--enable_warnings
CREATE TABLE `BB` (
`pk` int(11) NOT NULL AUTO_INCREMENT,
`col_int_nokey` int(11) DEFAULT NULL,
`col_int_key` int(11) DEFAULT NULL,
PRIMARY KEY (`pk`),
KEY `col_int_key` (`col_int_key`),
KEY `col_varchar_key` (`col_int_key`)
) ENGINE=MyISAM AUTO_INCREMENT=11 DEFAULT CHARSET=latin1;
INSERT INTO `BB` VALUES (10,8,8);
CREATE TABLE `C` (
`pk` int(11) NOT NULL AUTO_INCREMENT,
`col_int_nokey` int(11) DEFAULT NULL,
`col_int_key` int(11) DEFAULT NULL,
PRIMARY KEY (`pk`),
KEY `col_int_key` (`col_int_key`),
KEY `col_varchar_key` (`col_int_key`)
) ENGINE=MyISAM AUTO_INCREMENT=21 DEFAULT CHARSET=latin1;
INSERT INTO `C` VALUES (1,NULL,2);
INSERT INTO `C` VALUES (2,7,9);
INSERT INTO `C` VALUES (3,9,3);
INSERT INTO `C` VALUES (4,7,9);
INSERT INTO `C` VALUES (5,4,NULL);
INSERT INTO `C` VALUES (6,2,9);
INSERT INTO `C` VALUES (7,6,3);
INSERT INTO `C` VALUES (8,8,8);
INSERT INTO `C` VALUES (9,NULL,8);
INSERT INTO `C` VALUES (10,5,53);
INSERT INTO `C` VALUES (11,NULL,0);
INSERT INTO `C` VALUES (12,6,5);
INSERT INTO `C` VALUES (13,188,166);
INSERT INTO `C` VALUES (14,2,3);
INSERT INTO `C` VALUES (15,1,0);
INSERT INTO `C` VALUES (16,1,1);
INSERT INTO `C` VALUES (17,0,9);
INSERT INTO `C` VALUES (18,9,5);
INSERT INTO `C` VALUES (19,NULL,6);
INSERT INTO `C` VALUES (20,4,2);
SELECT MIN( table1 .`col_int_key` )
FROM C table1 LEFT JOIN (
SELECT SUBQUERY1_t1 .*
FROM C SUBQUERY1_t1 JOIN ( C SUBQUERY1_t2 STRAIGHT_JOIN BB SUBQUERY1_t3 ON SUBQUERY1_t3 .`pk` = SUBQUERY1_t2 .`pk` ) ON SUBQUERY1_t3 .`col_int_nokey` = SUBQUERY1_t2 .`pk` ) table2 ON table2 .`pk` ;
#/* End of test case for query 0 */
#/* Begin test case for query 1 */
ALTER TABLE C DROP KEY col_int_key;
ALTER TABLE C DROP KEY col_varchar_key;
ALTER TABLE BB DROP KEY col_int_key;
ALTER TABLE BB DROP KEY col_varchar_key;
SELECT MIN( table1 .`col_int_key` )
FROM C table1 LEFT JOIN (
SELECT SUBQUERY1_t1 .*
FROM C SUBQUERY1_t1 JOIN ( C SUBQUERY1_t2 STRAIGHT_JOIN BB SUBQUERY1_t3 ON SUBQUERY1_t3 .`pk` = SUBQUERY1_t2 .`pk` ) ON SUBQUERY1_t3 .`col_int_nokey` = SUBQUERY1_t2 .`pk` ) table2 ON table2 .`pk` /* TRANSFORM_OUTCOME_UNORDERED_MATCH */;
DROP TABLE BB;
DROP TABLE C;
#/* End of test case for query 1 */
