#/* Server0: MySQL 5.1.46-gcov-debug-log */


#/* Begin test case for query 1 */

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

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




SELECT DISTINCT   SUM(  table2 . `col_int_key` ) AS field1 , SUM(  table1 . `col_int_key` ) AS field2 
FROM ( (  
SELECT   SUBQUERY1_t1 . * 
FROM D AS SUBQUERY1_t1 
WHERE  SUBQUERY1_t1 . `pk` != SOME ( 
SELECT 5 UNION  
SELECT 1 ) ) AS table1 STRAIGHT_JOIN (  
SELECT   SUBQUERY2_t1 . * 
FROM B AS SUBQUERY2_t1  ) AS table2 ON (table2 . `col_varchar_nokey` = table1 . `col_varchar_key`  ) ) 
WHERE (  NOT EXISTS ( 
SELECT DISTINCT  SUBQUERY3_t1 . `col_varchar_nokey` AS SUBQUERY3_field1 
FROM ( B AS SUBQUERY3_t1 LEFT OUTER JOIN A AS SUBQUERY3_t2 ON (SUBQUERY3_t2 . `col_varchar_key` = SUBQUERY3_t1 . `col_varchar_key`  ) ) 
WHERE SUBQUERY3_t1 . `col_varchar_key` = table2 . `col_varchar_key` ) ) AND ( table1 . `pk` = 93 AND table1 . `col_varchar_key` = 'h' ) OR ( table1 . `col_int_nokey` IS NOT NULL AND table1 . `pk` = table1 . `col_int_key` )  
HAVING  ( 'k', 't' )  IN ( 
SELECT   SUBQUERY4_t2 . `col_varchar_nokey` AS SUBQUERY4_field1 , SUM(  SUBQUERY4_t1 . `col_varchar_key` ) AS SUBQUERY4_field2 
FROM ( CC AS SUBQUERY4_t1 INNER JOIN C AS SUBQUERY4_t2 ON (SUBQUERY4_t2 . `col_int_key` = SUBQUERY4_t1 . `pk`  ) )    ) 
ORDER BY table1 . `col_time_key` DESC , field1, field2 
LIMIT 1000 OFFSET 9
;

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