/*!50400 SET SESSION optimizer_switch = 'firstmatch=on,index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,loosescan=on,materialization=on,semijoin=on' */;
/*!50400 SET SESSION optimizer_use_mrr = 'force' */;
/*!50400 SET SESSION engine_condition_pushdown = 'ON' */;
/*!50400 SET SESSION join_cache_level = 1 */;


#/* Begin test case for query 0 */

--disable_warnings
DROP TABLE /*! IF EXISTS */ B;
--enable_warnings

CREATE TABLE `B` (
  `varchar_key` varchar(1) DEFAULT NULL,
  `varchar_nokey` varchar(1) DEFAULT NULL,
  KEY `varchar_key` (`varchar_key`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
INSERT INTO `B` VALUES ('f','f');


 
SELECT `varchar_key`  
FROM (  
SELECT * 
FROM B  ) table2  
HAVING ( `varchar_key`  , table1  )  IN (  
SELECT `varchar_nokey`  
FROM B  )   ;

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

#/* Begin test case for query 1 */

--disable_warnings
DROP TABLE /*! IF EXISTS */ D;
DROP TABLE /*! IF EXISTS */ C;
DROP TABLE /*! IF EXISTS */ BB;
DROP TABLE /*! IF EXISTS */ B;
--enable_warnings

CREATE TABLE `D` (
  `pk` int(11) NOT NULL AUTO_INCREMENT,
  `int_nokey` int(11) DEFAULT NULL,
  `int_key` int(11) DEFAULT NULL,
  `date_key` date DEFAULT NULL,
  `varchar_key` varchar(1) DEFAULT NULL,
  `varchar_nokey` varchar(1) DEFAULT NULL,
  PRIMARY KEY (`pk`),
  KEY `int_key` (`int_key`),
  KEY `date_key` (`date_key`),
  KEY `varchar_key` (`varchar_key`,`int_key`)
) ENGINE=MyISAM AUTO_INCREMENT=101 DEFAULT CHARSET=latin1;
INSERT INTO `D` VALUES (1,6,NULL,'2003-05-12','r','r'),(2,8,0,'2003-01-07','c','c'),(3,6,0,NULL,'o','o'),(4,6,7,'2005-03-12','c','c'),(5,3,8,'2000-08-02','d','d'),(6,9,4,'1900-01-01','v','v'),(7,2,6,'2006-07-06','m','m'),(8,1,5,'2006-12-24','j','j'),(9,8,NULL,'2004-11-16','f','f'),(10,0,NULL,'2002-09-09','n','n'),(11,9,8,NULL,'z','z'),(12,8,8,'2008-06-24','h','h'),(13,NULL,8,'2001-04-21','q','q'),(14,0,1,'2003-11-22','w','w'),(15,5,1,'2004-09-12','z','z'),(16,1,5,'2006-06-20','j','j'),(17,1,2,NULL,'a','a'),(18,6,7,'2001-11-25','m','m'),(19,6,6,'2004-10-26','n','n'),(20,1,4,'2005-01-19','e','e'),(21,8,7,'2008-07-06','u','u'),(22,1,0,'1900-01-01','s','s'),(23,0,9,'1900-01-01','u','u'),(24,4,3,'2004-06-08','r','r'),(25,9,5,'2007-02-20','g','g'),(26,8,1,'2008-06-18','o','o'),(27,5,1,'2008-05-15','w','w'),(28,9,5,'2005-10-06','b','b'),(29,5,9,NULL,NULL,NULL),(30,NULL,2,'2006-10-12','y','y'),(31,NULL,5,'2005-01-24','y','y'),(32,105,248,'2009-06-27','u','u'),(33,0,0,NULL,'p','p'),(34,3,8,NULL,'s','s'),(35,1,1,'1900-01-01','e','e'),(36,75,255,'2005-12-22','d','d'),(37,9,9,'2005-05-03','d','d'),(38,7,9,'2003-05-27','c','c'),(39,NULL,3,'2006-05-25','b','b'),(40,NULL,9,NULL,'t','t'),(41,4,6,'2009-01-04',NULL,NULL),(42,0,4,'2009-02-14','y','y'),(43,204,60,'2003-01-16','c','c'),(44,0,7,'1900-01-01','d','d'),(45,9,1,'2007-06-26','x','x'),(46,8,6,'2004-03-27','p','p'),(47,7,4,NULL,'e','e'),(48,8,NULL,'2005-06-06','g','g'),(49,NULL,8,'2003-03-02','x','x'),(50,6,0,'2004-05-13','s','s'),(51,5,8,'2005-09-13','e','e'),(52,2,151,'2005-10-03','l','l'),(53,3,7,'2005-10-14','p','p'),(54,7,6,NULL,'h','h'),(55,NULL,NULL,'2005-09-16','m','m'),(56,145,23,'2005-03-10','n','n'),(57,0,2,'2000-06-19','v','v'),(58,1,4,'2002-11-03','b','b'),(59,7,NULL,'2009-01-05','x','x'),(60,3,NULL,'2003-05-22','r','r'),(61,NULL,77,'2005-07-02','t','t'),(62,2,NULL,'1900-01-01','w','w'),(63,2,NULL,'2006-06-21','w','w'),(64,2,7,NULL,'k','k'),(65,8,1,'2005-12-16','a','a'),(66,6,9,'2004-11-05','t','t'),(67,1,6,NULL,'z','z'),(68,NULL,2,'2004-09-14','e','e'),(69,1,3,'2002-04-06','q','q'),(70,0,0,NULL,'e','e'),(71,4,NULL,'2002-11-13','v','v'),(72,1,6,'2006-05-27','d','d'),(73,1,3,'2000-12-22','u','u'),(74,27,195,'2004-02-21','o','o'),(75,4,5,'2009-05-15','b','b'),(76,6,2,'2008-12-12','c','c'),(77,2,7,'2000-04-15','q','q'),(78,248,25,NULL,NULL,NULL),(79,NULL,NULL,'2001-10-18','h','h'),(80,9,0,'2008-05-25','d','d'),(81,75,98,'2004-12-02','w','w'),(82,2,6,'2002-02-15','m','m'),(83,9,5,'2002-03-03','i','i'),(84,4,0,NULL,'w','w'),(85,0,3,'2003-01-26','f','f'),(86,0,1,'2001-12-19','k','k'),(87,1,1,'2001-08-07','v','v'),(88,119,147,'2005-02-16','c','c'),(89,1,3,'2006-06-10','y','y'),(90,7,3,NULL,'h','h'),(91,2,NULL,'2005-04-06',NULL,NULL),(92,7,2,'2003-04-27','t','t'),(93,2,1,'2005-10-13','l','l'),(94,6,8,'2003-10-02','a','a'),(95,4,8,'2005-09-09','r','r'),(96,5,8,NULL,'s','s'),(97,7,0,'2006-02-15','z','z'),(98,1,1,'1900-01-01','j','j'),(99,7,8,'2003-12-24','c','c'),(100,2,5,'2001-07-26','f','f');
CREATE TABLE `C` (
  `pk` int(11) NOT NULL AUTO_INCREMENT,
  `int_nokey` int(11) DEFAULT NULL,
  `int_key` int(11) DEFAULT NULL,
  `date_key` date DEFAULT NULL,
  `varchar_key` varchar(1) DEFAULT NULL,
  `varchar_nokey` varchar(1) DEFAULT NULL,
  PRIMARY KEY (`pk`),
  KEY `int_key` (`int_key`),
  KEY `date_key` (`date_key`),
  KEY `varchar_key` (`varchar_key`,`int_key`)
) ENGINE=MyISAM AUTO_INCREMENT=21 DEFAULT CHARSET=latin1;
INSERT INTO `C` VALUES (1,NULL,2,NULL,'w','w'),(2,7,9,'2001-09-19','m','m'),(3,9,3,'2004-09-12','m','m'),(4,7,9,NULL,'k','k'),(5,4,NULL,'2002-07-19','r','r'),(6,2,9,'2002-12-16','t','t'),(7,6,3,'2006-02-08','j','j'),(8,8,8,'2006-08-28','u','u'),(9,NULL,8,'2001-04-14','h','h'),(10,5,53,'2000-01-05','o','o'),(11,NULL,0,'2003-12-06',NULL,NULL),(12,6,5,'1900-01-01','k','k'),(13,188,166,'2002-11-27','e','e'),(14,2,3,NULL,'n','n'),(15,1,0,'2003-05-27','t','t'),(16,1,1,'2005-05-03','c','c'),(17,0,9,'2001-04-18','m','m'),(18,9,5,'2005-12-27','y','y'),(19,NULL,6,'2004-08-20','f','f'),(20,4,2,'1900-01-01','d','d');
CREATE TABLE `BB` (
  `pk` int(11) NOT NULL AUTO_INCREMENT,
  `int_nokey` int(11) DEFAULT NULL,
  `int_key` int(11) DEFAULT NULL,
  `date_key` date DEFAULT NULL,
  `varchar_key` varchar(1) DEFAULT NULL,
  `varchar_nokey` varchar(1) DEFAULT NULL,
  PRIMARY KEY (`pk`),
  KEY `int_key` (`int_key`),
  KEY `date_key` (`date_key`),
  KEY `varchar_key` (`varchar_key`,`int_key`)
) ENGINE=MyISAM AUTO_INCREMENT=11 DEFAULT CHARSET=latin1;
INSERT INTO `BB` VALUES (10,8,8,'2002-02-21',NULL,NULL);
CREATE TABLE `B` (
  `pk` int(11) NOT NULL AUTO_INCREMENT,
  `int_nokey` int(11) DEFAULT NULL,
  `int_key` int(11) DEFAULT NULL,
  `date_key` date DEFAULT NULL,
  `varchar_key` varchar(1) DEFAULT NULL,
  `varchar_nokey` varchar(1) DEFAULT NULL,
  PRIMARY KEY (`pk`),
  KEY `int_key` (`int_key`),
  KEY `date_key` (`date_key`),
  KEY `varchar_key` (`varchar_key`,`int_key`)
) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=latin1;
INSERT INTO `B` VALUES (1,1,7,'1900-01-01','f','f');


 
SELECT   table1 . `date_key` AS field1 , table2 . `varchar_key` AS field2 , MAX( table1 . `int_nokey` ) AS field3 , MAX( table1 . `varchar_key` ) AS field4 
FROM ( C AS table1 RIGHT OUTER JOIN ( ( (  ( 
SELECT   SUBQUERY1_t1 . * 
FROM ( B AS SUBQUERY1_t1 STRAIGHT_JOIN ( ( D AS SUBQUERY1_t2 RIGHT OUTER JOIN C AS SUBQUERY1_t3 ON (SUBQUERY1_t3 . `int_key` = SUBQUERY1_t2 . `pk` ) ) ) ON (SUBQUERY1_t3 . `int_key` = SUBQUERY1_t2 . `int_nokey` ) ) 
WHERE SUBQUERY1_t1 . `int_nokey` < 6 ) ) AS table2 RIGHT OUTER JOIN B AS table3 ON (table3 . `date_key` = table2 . `varchar_nokey` ) ) ) ON (table3 . `int_key` = table2 . `pk` ) ) 
WHERE (  table3 . `varchar_key`  IN ( 
SELECT   SUBQUERY2_t2 . `varchar_key` AS SUBQUERY2_field1 
FROM ( B AS SUBQUERY2_t1 RIGHT  JOIN BB AS SUBQUERY2_t2 ON (SUBQUERY2_t2 . `pk` = SUBQUERY2_t1 . `pk` ) ) 
WHERE SUBQUERY2_t1 . `int_nokey` > table2 . `int_nokey` AND SUBQUERY2_t1 . `int_key` <= table1 . `int_nokey` ) ) AND ( table1 . `varchar_key`  IN ('b') OR table1 . `int_key` > 203 AND table1 . `int_key` < ( 203 + 18 ) )  
GROUP BY field1, field2 
HAVING  ( table2 . `varchar_key` , table1 . `varchar_key` )  IN ( 
SELECT   SUBQUERY3_t2 . `varchar_key` AS SUBQUERY3_field1 , SUBQUERY3_t2 . `varchar_nokey` AS SUBQUERY3_field2 
FROM ( B AS SUBQUERY3_t1 STRAIGHT_JOIN ( ( B AS SUBQUERY3_t2 STRAIGHT_JOIN BB AS SUBQUERY3_t3 ON (SUBQUERY3_t3 . `int_nokey` = SUBQUERY3_t2 . `int_key` ) ) ) ON (SUBQUERY3_t3 . `int_key` = SUBQUERY3_t2 . `int_key` ) ) 
WHERE SUBQUERY3_t3 . `int_key` <= SUBQUERY3_t1 . `pk`   ) 
ORDER BY table1 . `date_key` , table2 .`pk` DESC , table1 . `int_key` , table3 .`pk` ASC, field1, field2, field3, field4 
LIMIT 100 ;

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