/*!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=off,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 */ D;
DROP TABLE /*! IF EXISTS */ B;
--enable_warnings

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


 
SELECT  COUNT( table2 .`varchar_nokey`  )  , (  
SELECT SUBQUERY1_t1 .`int_nokey`  
FROM D SUBQUERY1_t1  STRAIGHT_JOIN B SUBQUERY1_t2  ON SUBQUERY1_t2 .`pk`  
WHERE SUBQUERY1_t2 .`varchar_nokey`  <= table1 .`varchar_key`  )  
FROM D table1  JOIN D table2  ON table1 .`varchar_nokey`  
WHERE (  
SELECT `varchar_nokey`  
FROM D  
WHERE table2 .`varchar_nokey`  )  AND table1 .`pk`  =  230   ;

DROP TABLE D;
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 */ D;
DROP TABLE /*! IF EXISTS */ BB;
DROP TABLE /*! IF EXISTS */ B;
--enable_warnings

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



SELECT DISTINCT  table1 . `varchar_key` AS field1 , SUM( table1 . `int_key` ) AS field2 , COUNT( table2 . `varchar_nokey` ) AS field3 ,  ( 
SELECT   SUM( SUBQUERY1_t1 . `int_nokey` ) AS SUBQUERY1_field1 
FROM ( D AS SUBQUERY1_t1 STRAIGHT_JOIN ( ( B AS SUBQUERY1_t2 INNER JOIN BB AS SUBQUERY1_t3 ON (SUBQUERY1_t3 . `pk` = SUBQUERY1_t2 . `int_key` ) ) ) ON (SUBQUERY1_t3 . `pk` = SUBQUERY1_t2 . `pk` ) ) 
WHERE SUBQUERY1_t2 . `varchar_nokey` <= table1 . `varchar_key` ) AS field4 ,  ( 
SELECT   MAX( SUBQUERY2_t1 . `pk` ) AS SUBQUERY2_field1 
FROM ( B AS SUBQUERY2_t1 ) 
WHERE SUBQUERY2_t1 . `int_key` <> table1 . `int_nokey` ) AS field5 
FROM ( D AS table1 LEFT  JOIN D AS table2 ON (table2 . `time_key` = table1 . `varchar_nokey` ) ) 
WHERE (  NOT EXISTS ( 
SELECT   SUBQUERY3_t1 . `varchar_nokey` AS SUBQUERY3_field1 
FROM ( D AS SUBQUERY3_t1 INNER JOIN CC AS SUBQUERY3_t2 ON (SUBQUERY3_t2 . `varchar_nokey` = SUBQUERY3_t1 . `varchar_key` ) ) 
WHERE SUBQUERY3_t1 . `varchar_nokey` <> table2 . `varchar_nokey` ) ) AND table1 . `pk` = 230 AND table2 . `varchar_nokey` > 'w'   
ORDER BY field1 DESC, field1, field2, field3, field4, field5 
LIMIT 1;

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