/*!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 = 'disable' */;
/*!50400 SET SESSION engine_condition_pushdown = 0 */;
/*!50400 SET SESSION join_cache_level = 1 */;


#/* Begin test case for query 0 */

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

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


 
SELECT  MIN( table2 .`pk`  )  
FROM C table2  JOIN D table3  ON table2 .`varchar_key`  
WHERE table3 .`int_key`  AND 'j' 
HAVING ( 'r' , 'o' )  IN (  
SELECT `varchar_nokey`  , `varchar_key`  
FROM C  )   ;

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



SELECT   MIN( table2 . `int_key` ) AS field1 , MIN( table2 . `pk` ) AS field2 , CONCAT ( table2 . `varchar_key` , table2 . `varchar_nokey` ) AS field3 , ( ( table2 . `pk` ) - ( table2 . `int_key` ) ) AS field4 
FROM ( C AS table1 INNER JOIN ( ( C AS table2 RIGHT OUTER JOIN D AS table3 ON (table3 . `int_key` = table2 . `varchar_key` ) ) ) ON (table3 . `pk` = table2 . `pk` ) ) 
WHERE (  table3 . `int_key`  IN ( 
SELECT   SUBQUERY1_t1 . `int_key` AS SUBQUERY1_field1 
FROM ( D AS SUBQUERY1_t1 RIGHT  JOIN BB AS SUBQUERY1_t2 ON (SUBQUERY1_t2 . `varchar_key` = SUBQUERY1_t1 . `varchar_key` ) )  
GROUP BY SUBQUERY1_field1  ) ) AND table1 . `varchar_key`  IN ('j', 'f') AND table1 . `varchar_key` IS  NULL  
HAVING  ( 'r', 'o' )  IN ( 
SELECT   SUBQUERY2_t1 . `varchar_nokey` AS SUBQUERY2_field1 , SUBQUERY2_t1 . `varchar_key` AS SUBQUERY2_field2 
FROM ( C AS SUBQUERY2_t1 STRAIGHT_JOIN B AS SUBQUERY2_t2 ON (SUBQUERY2_t2 . `varchar_nokey` = SUBQUERY2_t1 . `varchar_key` ) )    );

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