#/* Server0: MySQL 5.1.46-gcov-debug-log */ /*!50400 SET SESSION optimizer_switch = 'index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on' */; /*!50400 SET SESSION engine_condition_pushdown = 'ON' */; #/* Begin test case for query 0 */ --disable_warnings DROP TABLE /*! IF EXISTS */ CC; DROP TABLE /*! IF EXISTS */ D; --enable_warnings CREATE TABLE `CC` ( `pk` int(11) NOT NULL AUTO_INCREMENT, `col_int_key` int(11) DEFAULT NULL, `col_varchar_key` 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,8,'v'); INSERT INTO `CC` VALUES (11,9,'r'); INSERT INTO `CC` VALUES (12,9,'a'); INSERT INTO `CC` VALUES (13,186,'m'); INSERT INTO `CC` VALUES (14,NULL,'y'); INSERT INTO `CC` VALUES (15,2,'j'); INSERT INTO `CC` VALUES (16,3,'d'); INSERT INTO `CC` VALUES (17,0,'z'); INSERT INTO `CC` VALUES (18,133,'e'); INSERT INTO `CC` VALUES (19,1,'h'); INSERT INTO `CC` VALUES (20,8,'b'); INSERT INTO `CC` VALUES (21,5,'s'); INSERT INTO `CC` VALUES (22,5,'e'); INSERT INTO `CC` VALUES (23,8,'j'); INSERT INTO `CC` VALUES (24,6,'e'); INSERT INTO `CC` VALUES (25,51,'f'); INSERT INTO `CC` VALUES (26,4,'v'); INSERT INTO `CC` VALUES (27,7,'x'); INSERT INTO `CC` VALUES (28,6,'m'); INSERT INTO `CC` VALUES (29,4,'c'); CREATE TABLE `D` ( `pk` int(11) NOT NULL AUTO_INCREMENT, `col_int_key` int(11) DEFAULT NULL, `col_varchar_key` 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=101 DEFAULT CHARSET=latin1; INSERT INTO `D` VALUES (1,NULL,'r'); INSERT INTO `D` VALUES (2,0,'c'); INSERT INTO `D` VALUES (3,0,'o'); INSERT INTO `D` VALUES (4,7,'c'); INSERT INTO `D` VALUES (5,8,'d'); INSERT INTO `D` VALUES (6,4,'v'); INSERT INTO `D` VALUES (7,6,'m'); INSERT INTO `D` VALUES (8,5,'j'); INSERT INTO `D` VALUES (9,NULL,'f'); INSERT INTO `D` VALUES (10,NULL,'n'); INSERT INTO `D` VALUES (11,8,'z'); INSERT INTO `D` VALUES (12,8,'h'); INSERT INTO `D` VALUES (13,8,'q'); INSERT INTO `D` VALUES (14,1,'w'); INSERT INTO `D` VALUES (15,1,'z'); INSERT INTO `D` VALUES (16,5,'j'); INSERT INTO `D` VALUES (17,2,'a'); INSERT INTO `D` VALUES (18,7,'m'); INSERT INTO `D` VALUES (19,6,'n'); INSERT INTO `D` VALUES (20,4,'e'); INSERT INTO `D` VALUES (21,7,'u'); INSERT INTO `D` VALUES (22,0,'s'); INSERT INTO `D` VALUES (23,9,'u'); INSERT INTO `D` VALUES (24,3,'r'); INSERT INTO `D` VALUES (25,5,'g'); INSERT INTO `D` VALUES (26,1,'o'); INSERT INTO `D` VALUES (27,1,'w'); INSERT INTO `D` VALUES (28,5,'b'); INSERT INTO `D` VALUES (29,9,NULL); INSERT INTO `D` VALUES (30,2,'y'); INSERT INTO `D` VALUES (31,5,'y'); INSERT INTO `D` VALUES (32,248,'u'); INSERT INTO `D` VALUES (33,0,'p'); INSERT INTO `D` VALUES (34,8,'s'); INSERT INTO `D` VALUES (35,1,'e'); INSERT INTO `D` VALUES (36,255,'d'); INSERT INTO `D` VALUES (37,9,'d'); INSERT INTO `D` VALUES (38,9,'c'); INSERT INTO `D` VALUES (39,3,'b'); INSERT INTO `D` VALUES (40,9,'t'); INSERT INTO `D` VALUES (41,6,NULL); INSERT INTO `D` VALUES (42,4,'y'); INSERT INTO `D` VALUES (43,60,'c'); INSERT INTO `D` VALUES (44,7,'d'); INSERT INTO `D` VALUES (45,1,'x'); INSERT INTO `D` VALUES (46,6,'p'); INSERT INTO `D` VALUES (47,4,'e'); INSERT INTO `D` VALUES (48,NULL,'g'); INSERT INTO `D` VALUES (49,8,'x'); INSERT INTO `D` VALUES (50,0,'s'); INSERT INTO `D` VALUES (51,8,'e'); INSERT INTO `D` VALUES (52,151,'l'); INSERT INTO `D` VALUES (53,7,'p'); INSERT INTO `D` VALUES (54,6,'h'); INSERT INTO `D` VALUES (55,NULL,'m'); INSERT INTO `D` VALUES (56,23,'n'); INSERT INTO `D` VALUES (57,2,'v'); INSERT INTO `D` VALUES (58,4,'b'); INSERT INTO `D` VALUES (59,NULL,'x'); INSERT INTO `D` VALUES (60,NULL,'r'); INSERT INTO `D` VALUES (61,77,'t'); INSERT INTO `D` VALUES (62,NULL,'w'); INSERT INTO `D` VALUES (63,NULL,'w'); INSERT INTO `D` VALUES (64,7,'k'); INSERT INTO `D` VALUES (65,1,'a'); INSERT INTO `D` VALUES (66,9,'t'); INSERT INTO `D` VALUES (67,6,'z'); INSERT INTO `D` VALUES (68,2,'e'); INSERT INTO `D` VALUES (69,3,'q'); INSERT INTO `D` VALUES (70,0,'e'); INSERT INTO `D` VALUES (71,NULL,'v'); INSERT INTO `D` VALUES (72,6,'d'); INSERT INTO `D` VALUES (73,3,'u'); INSERT INTO `D` VALUES (74,195,'o'); INSERT INTO `D` VALUES (75,5,'b'); INSERT INTO `D` VALUES (76,2,'c'); INSERT INTO `D` VALUES (77,7,'q'); INSERT INTO `D` VALUES (78,25,NULL); INSERT INTO `D` VALUES (79,NULL,'h'); INSERT INTO `D` VALUES (80,0,'d'); INSERT INTO `D` VALUES (81,98,'w'); INSERT INTO `D` VALUES (82,6,'m'); INSERT INTO `D` VALUES (83,5,'i'); INSERT INTO `D` VALUES (84,0,'w'); INSERT INTO `D` VALUES (85,3,'f'); INSERT INTO `D` VALUES (86,1,'k'); INSERT INTO `D` VALUES (87,1,'v'); INSERT INTO `D` VALUES (88,147,'c'); INSERT INTO `D` VALUES (89,3,'y'); INSERT INTO `D` VALUES (90,3,'h'); INSERT INTO `D` VALUES (91,NULL,NULL); INSERT INTO `D` VALUES (92,2,'t'); INSERT INTO `D` VALUES (93,1,'l'); INSERT INTO `D` VALUES (94,8,'a'); INSERT INTO `D` VALUES (95,8,'r'); INSERT INTO `D` VALUES (96,8,'s'); INSERT INTO `D` VALUES (97,0,'z'); INSERT INTO `D` VALUES (98,1,'j'); INSERT INTO `D` VALUES (99,8,'c'); INSERT INTO `D` VALUES (100,5,'f'); SELECT `col_varchar_key` , MAX( `col_varchar_key` ) FROM D HAVING ( 1 , 2 ) IN ( SELECT `pk` , `col_int_key` FROM CC HAVING `col_int_key` ) ; DROP TABLE CC; DROP TABLE D; #/* 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; --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_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 `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'); SELECT MAX( table1 . `col_varchar_key` ) AS field1 , MAX( table1 . `col_varchar_key` ) AS field2 FROM ( D AS table1 LEFT JOIN CC AS table2 ON (table2 . `col_int_nokey` = table1 . `pk` ) ) WHERE ( table1 . `col_varchar_key` > ANY ( SELECT 'a' UNION SELECT 'i' ) ) AND table1 . `col_varchar_key` = table2 . `col_varchar_key` OR table1 . `col_varchar_nokey` IS NULL HAVING ( 1, 2 ) IN ( SELECT SUBQUERY2_t1 . `pk` AS SUBQUERY2_field1 , SUBQUERY2_t1 . `col_int_key` AS SUBQUERY2_field2 FROM CC AS SUBQUERY2_t1 HAVING (SUBQUERY2_t1 . `col_int_key` = 'x' AND SUBQUERY2_t1 . `col_int_key` > 'c') ) ORDER BY table1 . `col_time_key` , field1, field2 LIMIT 2 ; DROP TABLE CC; DROP TABLE D; #/* End of test case for query 1 */