#/* Server0: MySQL 6.0.14-alpha-gcov-debug-log */

/*!50400 SET SESSION optimizer_switch = 'index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,firstmatch=on,loosescan=on,materialization=on,semijoin=on,mrr=on,mrr_cost_based=off,index_condition_pushdown=on' */;


#/* Begin test case for query 0 */

--disable_warnings
DROP TABLE /*! IF EXISTS */ CC;
DROP TABLE /*! IF EXISTS */ C;
DROP TABLE /*! IF EXISTS */ BB;
DROP TABLE /*! IF EXISTS */ B;
--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=MyISAM AUTO_INCREMENT=30 DEFAULT CHARSET=latin1;
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=MyISAM AUTO_INCREMENT=21 DEFAULT CHARSET=latin1;
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 TABLE `BB` (
  `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=MyISAM AUTO_INCREMENT=11 DEFAULT CHARSET=latin1;
INSERT INTO `BB` VALUES (10,8,8,NULL,NULL);
CREATE TABLE `B` (
  `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=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=latin1;
INSERT INTO `B` VALUES (1,1,7,'f','f');


 
SELECT table1 .`col_varchar_nokey`  
FROM C table1  STRAIGHT_JOIN C table2  ON table2 .`col_int_key`  
WHERE ( table1 .`col_varchar_nokey`  , table2 .`col_varchar_nokey`  )  IN (  
SELECT SUBQUERY2_t1 .`col_varchar_nokey`  , SUBQUERY2_t1 .`col_varchar_nokey`  
FROM B SUBQUERY2_t1  LEFT  JOIN BB  ON SUBQUERY2_t1 .`pk`  AND (  3  ,  7  )  IN (  
SELECT `col_int_nokey`  ,  MIN( `pk`  )  
FROM CC  )  )   ;

DROP TABLE CC;
DROP TABLE C;
DROP TABLE BB;
DROP TABLE B;
#/* End of test case for query 0 */

#/* Begin test case for query 1 */

--disable_warnings
DROP TABLE /*! IF EXISTS */ CC;
DROP TABLE /*! IF EXISTS */ C;
DROP TABLE /*! IF EXISTS */ BB;
DROP TABLE /*! IF EXISTS */ B;
--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=MyISAM AUTO_INCREMENT=30 DEFAULT CHARSET=latin1;
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=MyISAM AUTO_INCREMENT=21 DEFAULT CHARSET=latin1;
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 TABLE `BB` (
  `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=MyISAM AUTO_INCREMENT=11 DEFAULT CHARSET=latin1;
INSERT INTO `BB` VALUES (10,8,8,NULL,NULL);
CREATE TABLE `B` (
  `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=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=latin1;
INSERT INTO `B` VALUES (1,1,7,'f','f');




SELECT    table1 . `col_varchar_nokey` AS field1 , table1 . `col_varchar_nokey` AS field2 
FROM ( C AS table1 STRAIGHT_JOIN ( ( C AS table2 STRAIGHT_JOIN (  
SELECT   SUBQUERY1_t2 . * 
FROM ( C AS SUBQUERY1_t1 INNER JOIN ( CC AS SUBQUERY1_t2 STRAIGHT_JOIN C AS SUBQUERY1_t3 ON (SUBQUERY1_t3 . `pk` = SUBQUERY1_t2 . `col_int_key`  ) ) ON (SUBQUERY1_t3 . `col_varchar_key` = SUBQUERY1_t2 . `col_varchar_key`  ) )  ) AS table3 ON (table3 . `col_varchar_key` = table2 . `col_varchar_key`  ) ) ) ON (table3 . `pk` = table2 . `col_int_key`  ) ) 
WHERE (  ( table1 . `col_varchar_nokey` , table2 . `col_varchar_nokey` )  IN ( 
SELECT   SUBQUERY2_t1 . `col_varchar_nokey` AS SUBQUERY2_field1 , SUBQUERY2_t1 . `col_varchar_nokey` AS SUBQUERY2_field2 
FROM ( B AS SUBQUERY2_t1 LEFT OUTER JOIN BB AS SUBQUERY2_t2 ON (SUBQUERY2_t2 . `col_int_key` = SUBQUERY2_t1 . `pk`  AND ( 3, 7 )  IN ( 
SELECT DISTINCT  CHILD_SUBQUERY1_t1 . `col_int_nokey` AS CHILD_SUBQUERY1_field1 , MIN(  CHILD_SUBQUERY1_t1 . `pk` ) AS child_subquery1_field2 
FROM ( CC AS CHILD_SUBQUERY1_t1 STRAIGHT_JOIN B AS CHILD_SUBQUERY1_t2 ON (CHILD_SUBQUERY1_t2 . `col_int_key` = CHILD_SUBQUERY1_t1 . `col_int_key` ) ) 
WHERE CHILD_SUBQUERY1_t1 . `col_varchar_nokey` < CHILD_SUBQUERY1_t2 . `col_varchar_nokey`   ) ) )    ) ) AND table3 . `col_varchar_key` < table1 . `col_varchar_nokey`   
ORDER BY CONCAT ( table2 . `col_varchar_nokey`, table2 . `col_varchar_key` ), field1, field2 
LIMIT 100
;

DROP TABLE CC;
DROP TABLE C;
DROP TABLE BB;
DROP TABLE B;
#/* End of test case for query 1 */