Test case:
--disable_warnings --source include/have_innodb.inc
SET SESSION join_cache_level=9; SET SESSION optimizer_switch='mrr_sort_keys=off';
CREATE TABLE `D` ( `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, `col_varchar_nokey` varchar(1) DEFAULT NULL, PRIMARY KEY (`pk`), KEY `col_varchar_key` (`col_varchar_key`,`col_int_key`) ) ENGINE=InnoDB AUTO_INCREMENT=101 DEFAULT CHARSET=latin1; INSERT INTO `D` VALUES (1,6,NULL,'r','r'); INSERT INTO `D` VALUES (2,8,0,'c','c'); INSERT INTO `D` VALUES (97,7,0,'z','z'); INSERT INTO `D` VALUES (98,1,1,'j','j'); INSERT INTO `D` VALUES (99,7,8,'c','c'); INSERT INTO `D` VALUES (100,2,5,'f','f'); SELECT table1 .`col_varchar_key` FROM D table1 STRAIGHT_JOIN ( D table3 JOIN D table4 ON table4 .`pk` = table3 .`col_int_nokey` ) ON table4 .`col_varchar_nokey` ;
Test case:
--disable_warnings have_innodb. inc
--source include/
SET SESSION join_cache_level=9; switch= 'mrr_sort_ keys=off' ;
SET SESSION optimizer_
CREATE TABLE `D` ( varchar_ nokey` varchar(1) DEFAULT NULL, key`,`col_ int_key` ) nokey` ;
`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,
`col_
PRIMARY KEY (`pk`),
KEY `col_varchar_key` (`col_varchar_
) ENGINE=InnoDB AUTO_INCREMENT=101 DEFAULT CHARSET=latin1;
INSERT INTO `D` VALUES (1,6,NULL,'r','r');
INSERT INTO `D` VALUES (2,8,0,'c','c');
INSERT INTO `D` VALUES (97,7,0,'z','z');
INSERT INTO `D` VALUES (98,1,1,'j','j');
INSERT INTO `D` VALUES (99,7,8,'c','c');
INSERT INTO `D` VALUES (100,2,5,'f','f');
SELECT table1 .`col_varchar_key`
FROM D table1 STRAIGHT_JOIN ( D table3 JOIN D table4 ON table4 .`pk` = table3 .`col_int_nokey` ) ON table4 .`col_varchar_