SET SESSION optimizer_switch = 'index_merge=off,index_merge_union=off,index_merge_sort_union=off,index_merge_intersection=off,index_condition_pushdown=off,firstmatch=off,loosescan=off,materialization=off,semijoin=off,partial_match_rowid_merge=off,partial_match_table_scan=off,subquery_cache=on,table_elimination=off';
--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,
`col_varchar_nokey` 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=MARIA AUTO_INCREMENT=30 DEFAULT CHARSET=latin1 PAGE_CHECKSUM=1;
INSERT INTO `CC` VALUES (10,7,8,'v','v');
INSERT INTO `CC` VALUES (11,1,9,'r','r');
INSERT INTO `CC` VALUES (12,5,9,'a','a');
INSERT INTO `CC` VALUES (13,3,186,'m','m');
INSERT INTO `CC` VALUES (14,6,NULL,'y','y');
INSERT INTO `CC` VALUES (15,92,2,'j','j');
INSERT INTO `CC` VALUES (16,7,3,'d','d');
INSERT INTO `CC` VALUES (17,NULL,0,'z','z');
INSERT INTO `CC` VALUES (18,3,133,'e','e');
INSERT INTO `CC` VALUES (19,5,1,'h','h');
INSERT INTO `CC` VALUES (20,1,8,'b','b');
INSERT INTO `CC` VALUES (21,2,5,'s','s');
INSERT INTO `CC` VALUES (22,NULL,5,'e','e');
INSERT INTO `CC` VALUES (23,1,8,'j','j');
INSERT INTO `CC` VALUES (24,0,6,'e','e');
INSERT INTO `CC` VALUES (25,210,51,'f','f');
INSERT INTO `CC` VALUES (26,8,4,'v','v');
INSERT INTO `CC` VALUES (27,7,7,'x','x');
INSERT INTO `CC` VALUES (28,5,6,'m','m');
INSERT INTO `CC` VALUES (29,NULL,4,'c','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,
`col_varchar_nokey` 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=MARIA AUTO_INCREMENT=21 DEFAULT CHARSET=latin1 PAGE_CHECKSUM=1;
INSERT INTO `C` VALUES (1,NULL,2,'w','w');
INSERT INTO `C` VALUES (2,7,9,'m','m');
INSERT INTO `C` VALUES (3,9,3,'m','m');
INSERT INTO `C` VALUES (4,7,9,'k','k');
INSERT INTO `C` VALUES (5,4,NULL,'r','r');
INSERT INTO `C` VALUES (6,2,9,'t','t');
INSERT INTO `C` VALUES (7,6,3,'j','j');
INSERT INTO `C` VALUES (8,8,8,'u','u');
INSERT INTO `C` VALUES (9,NULL,8,'h','h');
INSERT INTO `C` VALUES (10,5,53,'o','o');
INSERT INTO `C` VALUES (11,NULL,0,NULL,NULL);
INSERT INTO `C` VALUES (12,6,5,'k','k');
INSERT INTO `C` VALUES (13,188,166,'e','e');
INSERT INTO `C` VALUES (14,2,3,'n','n');
INSERT INTO `C` VALUES (15,1,0,'t','t');
INSERT INTO `C` VALUES (16,1,1,'c','c');
INSERT INTO `C` VALUES (17,0,9,'m','m');
INSERT INTO `C` VALUES (18,9,5,'y','y');
INSERT INTO `C` VALUES (19,NULL,6,'f','f');
INSERT INTO `C` VALUES (20,4,2,'d','d');
CREATE VIEW view_C AS SELECT * FROM C;
SELECT table1 .`col_varchar_nokey`
FROM C table1 RIGHT JOIN CC LEFT JOIN (
SELECT SUBQUERY1_t2 .*
FROM CC SUBQUERY1_t1 LEFT JOIN C SUBQUERY1_t2 ON SUBQUERY1_t2 .`col_int_key` = SUBQUERY1_t1 .`col_int_nokey` ) table3 STRAIGHT_JOIN ( (
SELECT *
FROM CC ) table4 JOIN ( CC table5 JOIN C table6 ON table5 .`pk` ) ON table5 .`col_varchar_nokey` ) ON table6 .`pk` = table5 .`col_int_key` ON table5 .`col_varchar_nokey` ON table5 .`col_varchar_key`
WHERE table3 .`col_varchar_key` IN (
SELECT `col_varchar_key`
FROM view_C ) AND table1 .`col_varchar_key` OR table1 .`pk` ;
Test case:
SET SESSION optimizer_switch = 'index_ merge=off, index_merge_ union=off, index_merge_ sort_union= off,index_ merge_intersect ion=off, index_condition _pushdown= off,firstmatch= off,loosescan= off,materializa tion=off, semijoin= off,partial_ match_rowid_ merge=off, partial_ match_table_ scan=off, subquery_ cache=on, table_eliminati on=off' ;
--disable_warnings
DROP TABLE /*! IF EXISTS */ CC;
DROP TABLE /*! IF EXISTS */ C;
--enable_warnings
CREATE TABLE `CC` ( varchar_ nokey` varchar(1) DEFAULT NULL, key`,`col_ int_key` ) 'y','y' ); 0,'z',' z'); 5,'e',' e'); 51,'f', 'f'); 4,'c',' c'); varchar_ nokey` varchar(1) DEFAULT NULL, key`,`col_ int_key` ) 0,NULL, NULL); 166,'e' ,'e'); 6,'f',' f');
`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_int_key` (`col_int_key`),
KEY `col_varchar_key` (`col_varchar_
) ENGINE=MARIA AUTO_INCREMENT=30 DEFAULT CHARSET=latin1 PAGE_CHECKSUM=1;
INSERT INTO `CC` VALUES (10,7,8,'v','v');
INSERT INTO `CC` VALUES (11,1,9,'r','r');
INSERT INTO `CC` VALUES (12,5,9,'a','a');
INSERT INTO `CC` VALUES (13,3,186,'m','m');
INSERT INTO `CC` VALUES (14,6,NULL,
INSERT INTO `CC` VALUES (15,92,2,'j','j');
INSERT INTO `CC` VALUES (16,7,3,'d','d');
INSERT INTO `CC` VALUES (17,NULL,
INSERT INTO `CC` VALUES (18,3,133,'e','e');
INSERT INTO `CC` VALUES (19,5,1,'h','h');
INSERT INTO `CC` VALUES (20,1,8,'b','b');
INSERT INTO `CC` VALUES (21,2,5,'s','s');
INSERT INTO `CC` VALUES (22,NULL,
INSERT INTO `CC` VALUES (23,1,8,'j','j');
INSERT INTO `CC` VALUES (24,0,6,'e','e');
INSERT INTO `CC` VALUES (25,210,
INSERT INTO `CC` VALUES (26,8,4,'v','v');
INSERT INTO `CC` VALUES (27,7,7,'x','x');
INSERT INTO `CC` VALUES (28,5,6,'m','m');
INSERT INTO `CC` VALUES (29,NULL,
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,
`col_
PRIMARY KEY (`pk`),
KEY `col_int_key` (`col_int_key`),
KEY `col_varchar_key` (`col_varchar_
) ENGINE=MARIA AUTO_INCREMENT=21 DEFAULT CHARSET=latin1 PAGE_CHECKSUM=1;
INSERT INTO `C` VALUES (1,NULL,2,'w','w');
INSERT INTO `C` VALUES (2,7,9,'m','m');
INSERT INTO `C` VALUES (3,9,3,'m','m');
INSERT INTO `C` VALUES (4,7,9,'k','k');
INSERT INTO `C` VALUES (5,4,NULL,'r','r');
INSERT INTO `C` VALUES (6,2,9,'t','t');
INSERT INTO `C` VALUES (7,6,3,'j','j');
INSERT INTO `C` VALUES (8,8,8,'u','u');
INSERT INTO `C` VALUES (9,NULL,8,'h','h');
INSERT INTO `C` VALUES (10,5,53,'o','o');
INSERT INTO `C` VALUES (11,NULL,
INSERT INTO `C` VALUES (12,6,5,'k','k');
INSERT INTO `C` VALUES (13,188,
INSERT INTO `C` VALUES (14,2,3,'n','n');
INSERT INTO `C` VALUES (15,1,0,'t','t');
INSERT INTO `C` VALUES (16,1,1,'c','c');
INSERT INTO `C` VALUES (17,0,9,'m','m');
INSERT INTO `C` VALUES (18,9,5,'y','y');
INSERT INTO `C` VALUES (19,NULL,
INSERT INTO `C` VALUES (20,4,2,'d','d');
CREATE VIEW view_C AS SELECT * FROM C;
SELECT table1 .`col_varchar_ nokey` nokey` ) ON table6 .`pk` = table5 .`col_int_key` ON table5 .`col_varchar_ nokey` ON table5 .`col_varchar_key`
FROM C table1 RIGHT JOIN CC LEFT JOIN (
SELECT SUBQUERY1_t2 .*
FROM CC SUBQUERY1_t1 LEFT JOIN C SUBQUERY1_t2 ON SUBQUERY1_t2 .`col_int_key` = SUBQUERY1_t1 .`col_int_nokey` ) table3 STRAIGHT_JOIN ( (
SELECT *
FROM CC ) table4 JOIN ( CC table5 JOIN C table6 ON table5 .`pk` ) ON table5 .`col_varchar_
WHERE table3 .`col_varchar_key` IN (
SELECT `col_varchar_key`
FROM view_C ) AND table1 .`col_varchar_key` OR table1 .`pk` ;