/* Server0: version = 5.4.4-alpha-gcov-debug-log */ /* Server1: version = 5.1.38-debug-log */ /* The value of optimizer_switch is distinct between the two servers: */ /* Server 0 : 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' */; # Server 1 : SET SESSION optimizer_switch = 'index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on' ; /* The value of optimizer_use_mrr is distinct between the two servers: */ /* Server 0 : SET SESSION optimizer_use_mrr = 'force' */; # Server 1 : SET SESSION optimizer_use_mrr = '' ; /* The value of engine_condition_pushdown is common between the two servers: */ /*!50400 SET SESSION engine_condition_pushdown = ON */; /* The value of join_cache_level is distinct between the two servers: */ /* Server 0 : SET SESSION join_cache_level = 1; */ # Server 1 : SET SESSION join_cache_level = '' ; #/* Begin test case for query 0 */ --disable_warnings DROP TABLE /*! IF EXISTS */ CC; DROP TABLE /*! IF EXISTS */ B; --enable_warnings CREATE TABLE `CC` ( `pk` int(11) NOT NULL AUTO_INCREMENT, `varchar_key` varchar(1) DEFAULT NULL, `varchar_nokey` varchar(1) DEFAULT NULL, PRIMARY KEY (`pk`), KEY `varchar_key` (`varchar_key`) ) ENGINE=MyISAM AUTO_INCREMENT=30 DEFAULT CHARSET=latin1; INSERT INTO `CC` VALUES (10,'m','m'),(11,'y','y'),(12,'j','j'),(13,'d','d'),(14,'z','z'),(15,'e','e'),(16,'h','h'),(17,'b','b'),(18,'s','s'),(19,'e','e'),(20,'j','j'),(21,'e','e'),(22,'f','f'),(23,'v','v'),(24,'x','x'),(25,'m','m'),(26,'c','c'),(27,'m','m'),(28,'b','b'),(29,'x','x'); CREATE TABLE `B` ( `pk` int(11) NOT NULL AUTO_INCREMENT, `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,'f','f'),(2,'w','w'); SELECT MAX( table1 .`pk` ) FROM CC table1 JOIN B table2 ON table2 .`varchar_nokey` ORDER BY table2 .`varchar_key` ; /* Diff: */ /* --- /var/folders/Pt/PtJd7NDTGgyOk3+iDGXrQk+++TI/-Tmp-///randgen95989-1250168155-server0.dump 2009-08-13 08:55:55.000000000 -0400 # +++ /var/folders/Pt/PtJd7NDTGgyOk3+iDGXrQk+++TI/-Tmp-///randgen95989-1250168155-server1.dump 2009-08-13 08:55:55.000000000 -0400 # @@ -0,0 +1 @@ # +NULL */ DROP TABLE CC; 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, `datetime_key` datetime 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 `datetime_key` (`datetime_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','2008-05-26 12:27:10','m','m'),(11,6,NULL,'19:18:56','2004-12-14 16:37:30','y','y'),(12,92,2,'10:55:12','2003-02-11 21:19:41','j','j'),(13,7,3,'00:25:00','2009-10-18 02:27:49','d','d'),(14,NULL,0,'12:35:47','2000-09-26 07:45:57','z','z'),(15,3,133,'19:53:03',NULL,'e','e'),(16,5,1,'17:53:30','2005-11-10 12:40:29','h','h'),(17,1,8,'11:35:49','2009-04-25 00:00:00','b','b'),(18,2,5,NULL,'2002-11-27 00:00:00','s','s'),(19,NULL,5,'06:01:40','2004-01-26 20:32:32','e','e'),(20,1,8,'05:45:11','2007-10-26 11:41:40','j','j'),(21,0,6,'00:00:00','2005-10-07 00:00:00','e','e'),(22,210,51,'00:00:00','2000-07-15 05:00:34','f','f'),(23,8,4,'06:11:01','2000-04-03 16:33:32','v','v'),(24,7,7,'13:02:46',NULL,'x','x'),(25,5,6,'21:44:25','2001-04-25 01:26:12','m','m'),(26,NULL,4,'22:43:58','2000-12-27 00:00:00','c','c'),(27,6,7,NULL,'2005-10-04 00:00:00','m','m'),(28,9,3,'06:43:09','2004-03-08 02:50:35','b','b'),(29,3,2,'12:20:22','2004-09-24 09:21:35','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, `datetime_key` datetime 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 `datetime_key` (`datetime_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','2004-08-10 09:09:31','c','c'),(2,6,0,'11:49:48','2005-03-21 04:31:40','o','o'),(3,6,7,'18:12:55','2002-10-25 23:50:35','c','c'),(4,3,8,'18:30:05','2001-04-01 21:14:04','d','d'),(5,9,4,'14:19:30','2005-03-12 06:02:34','v','v'),(6,2,6,'05:20:04','2001-05-06 14:49:12','m','m'),(7,1,5,'20:29:31','2004-04-25 00:00:00','j','j'),(8,8,NULL,'07:08:09','2001-03-22 18:38:43','f','f'),(9,0,NULL,'14:49:14','2006-04-25 21:03:02','n','n'),(10,9,8,'00:00:00','2009-09-07 18:40:43','z','z'),(11,8,8,'09:58:06','2004-03-23 00:00:00','h','h'),(12,NULL,8,NULL,'2009-04-15 00:08:29','q','q'),(13,0,1,'18:24:16','2000-04-21 00:00:00','w','w'),(14,5,1,'17:39:57','2000-02-17 19:41:23','z','z'),(15,1,5,'08:23:21','2003-09-20 07:38:14','j','j'),(16,1,2,NULL,'2000-11-28 20:42:12','a','a'),(17,6,7,'21:50:46','2005-06-12 11:13:17','m','m'),(18,6,6,'12:33:17','1900-01-01 00:00:00','n','n'),(19,1,4,'03:06:43','2006-02-09 20:41:06','e','e'),(20,8,7,'03:46:14','2004-05-22 01:05:57','u','u'),(21,1,0,'20:34:52','2004-03-04 13:46:31','s','s'),(22,0,9,NULL,'1900-01-01 00:00:00','u','u'),(23,4,3,'10:41:20','2004-10-20 07:20:19','r','r'),(24,9,5,'08:43:11','2006-04-17 00:00:00','g','g'),(25,8,1,NULL,'2000-10-27 00:00:00','o','o'),(26,5,1,'10:17:51','2007-04-14 08:54:06','w','w'),(27,9,5,'06:34:09','2008-04-12 17:03:52','b','b'),(28,5,9,'21:22:47','2007-02-19 17:37:09',NULL,NULL),(29,NULL,2,'04:02:32','1900-01-01 00:00:00','y','y'),(30,NULL,5,'02:33:14','2001-10-10 08:32:27','y','y'),(31,105,248,'16:32:56',NULL,'u','u'),(32,0,0,'21:32:42','2001-12-16 05:31:53','p','p'),(33,3,8,'23:04:47','2003-07-19 18:03:28','s','s'),(34,1,1,'22:05:43','2001-03-27 11:44:10','e','e'),(35,75,255,'02:05:45','2008-06-15 02:13:00','d','d'),(36,9,9,'00:00:00','2009-03-14 21:29:56','d','d'),(37,7,9,'18:09:07','2005-01-02 00:00:00','c','c'),(38,NULL,3,'10:54:06','2007-07-16 04:44:07','b','b'),(39,NULL,9,'23:15:50','2003-08-26 21:38:26','t','t'),(40,4,6,'10:17:40','2004-04-19 04:18:47',NULL,NULL),(41,0,4,'03:37:09','2000-01-06 20:32:48','y','y'),(42,204,60,'22:26:06','2006-06-23 13:27:17','c','c'),(43,0,7,'17:10:38','2007-11-27 00:00:00','d','d'),(44,9,1,'00:00:00','2002-04-03 12:06:51','x','x'),(45,8,6,'17:08:49','2008-12-28 09:47:42','p','p'),(46,7,4,'19:04:40','2002-04-04 10:07:54','e','e'),(47,8,NULL,'20:53:28','2003-04-26 02:55:13','g','g'),(48,NULL,8,'11:46:03',NULL,'x','x'),(49,6,0,NULL,'2009-02-19 03:17:06','s','s'),(50,5,8,'10:58:07','1900-01-01 00:00:00','e','e'),(51,2,151,'00:00:00','2000-11-10 08:20:01','l','l'),(52,3,7,'09:43:15','2008-02-10 00:00:00','p','p'),(53,7,6,'21:40:32','1900-01-01 00:00:00','h','h'),(54,NULL,NULL,'00:17:44',NULL,'m','m'),(55,145,23,'16:47:26','2001-02-05 02:01:50','n','n'),(56,0,2,'00:00:00','2000-10-28 08:44:25','v','v'),(57,1,4,'05:25:59','2005-03-20 10:53:59','b','b'),(58,7,NULL,'00:00:00','2001-06-02 13:54:13','x','x'),(59,3,NULL,'20:33:04','1900-01-01 00:00:00','r','r'),(60,NULL,77,'00:46:12','2009-07-16 13:05:43','t','t'),(61,2,NULL,'00:00:00','2009-03-26 23:16:20','w','w'),(62,2,NULL,'02:13:59','2003-02-06 18:12:15','w','w'),(63,2,7,'02:54:47','2006-06-05 03:22:51','k','k'),(64,8,1,'18:13:59','2002-02-10 05:47:27','a','a'),(65,6,9,'13:53:08','2001-08-01 08:50:52','t','t'),(66,1,6,'22:21:30','1900-01-01 00:00:00','z','z'),(67,NULL,2,'11:41:50',NULL,'e','e'),(68,1,3,'15:20:02','1900-01-01 00:00:00','q','q'),(69,0,0,NULL,'2000-09-23 00:00:00','e','e'),(70,4,NULL,NULL,'2007-07-09 08:32:49','v','v'),(71,1,6,'07:51:52','2000-01-05 00:00:00','d','d'),(72,1,3,'00:00:00','2000-09-24 00:00:00','u','u'),(73,27,195,NULL,'2005-05-06 00:00:00','o','o'),(74,4,5,NULL,'2000-03-11 00:00:00','b','b'),(75,6,2,'12:31:05','2001-09-02 16:17:35','c','c'),(76,2,7,'00:00:00','2006-04-25 05:43:44','q','q'),(77,248,25,'01:16:45','2009-10-25 22:04:02',NULL,NULL),(78,NULL,NULL,'20:38:54','2004-08-06 00:00:00','h','h'),(79,9,0,'00:30:15','2001-11-27 05:07:57','d','d'),(80,75,98,'23:46:36','2009-06-28 03:18:39','w','w'),(81,2,6,'19:03:13','2000-03-12 00:00:00','m','m'),(82,9,5,'10:54:27',NULL,'i','i'),(83,4,0,'00:25:47','2007-10-20 00:00:00','w','w'),(84,0,3,'08:44:27','2009-09-27 00:00:00','f','f'),(85,0,1,'08:15:38','2002-07-16 00:00:00','k','k'),(86,1,1,'19:56:21','2005-02-20 00:00:00','v','v'),(87,119,147,'00:00:00',NULL,'c','c'),(88,1,3,'20:50:52','2001-07-16 00:00:00','y','y'),(89,7,3,'03:54:39','2009-05-20 21:04:12','h','h'),(90,2,NULL,'23:58:17','2002-03-13 10:55:40',NULL,NULL),(91,7,2,'12:54:58','2005-07-12 00:00:00','t','t'),(92,2,1,'04:02:43','2006-07-22 09:46:34','l','l'),(93,6,8,'11:31:12','2001-09-01 00:00:00','a','a'),(94,4,8,'20:20:04','2002-05-27 18:38:45','r','r'),(95,5,8,'00:22:24',NULL,'s','s'),(96,7,0,'10:09:31',NULL,'z','z'),(97,1,1,NULL,'2009-08-08 22:38:53','j','j'),(98,7,8,'18:45:35',NULL,'c','c'),(99,2,5,'11:49:25','2007-04-25 05:08:49','f','f'),(100,1,4,'12:52:58','2006-12-15 00:00:00','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, `datetime_key` datetime 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 `datetime_key` (`datetime_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','1900-01-01 00:00:00','r','r'),(11,5,9,'00:00:00','2006-12-03 09:37:26','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, `datetime_key` datetime 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 `datetime_key` (`datetime_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','2005-02-05 00:00:00','f','f'),(2,NULL,2,'11:28:45','2004-10-11 18:13:16','w','w'); SELECT CONCAT ( table1 . `varchar_nokey` , table2 . `varchar_nokey` ) AS field1 , MIN( table1 . `varchar_key` ) AS field2 , table2 . `int_key` AS field3 , MIN( table1 . `pk` ) AS field4 , MAX( table1 . `pk` ) AS field5 , table2 . `time_key` AS field6 FROM ( CC AS table1 INNER JOIN ( ( B AS table2 INNER JOIN ( ( SELECT SUBQUERY1_t1 . * FROM ( BB AS SUBQUERY1_t1 ) WHERE ( SUBQUERY1_t1 . `varchar_nokey` = 'a' AND EXISTS ( ( SELECT CHILD_SUBQUERY1_t1 . `pk` AS CHILD_SUBQUERY1_field1 FROM ( CC AS CHILD_SUBQUERY1_t1 RIGHT JOIN D AS CHILD_SUBQUERY1_t2 ON (CHILD_SUBQUERY1_t2 . `varchar_key` = CHILD_SUBQUERY1_t1 . `varchar_key` ) ) WHERE ( CHILD_SUBQUERY1_t1 . `pk` >= 6 AND CHILD_SUBQUERY1_t2 . `varchar_key` = CHILD_SUBQUERY1_t1 . `varchar_key` ) ) ) ) ) ) AS table3 ON (table3 . `int_key` = table2 . `datetime_key` ) ) ) ON (table3 . `int_key` = table2 . `varchar_nokey` ) ) WHERE ( EXISTS ( ( SELECT SUBQUERY2_t2 . `varchar_nokey` AS SUBQUERY2_field1 FROM ( B AS SUBQUERY2_t1 INNER JOIN D AS SUBQUERY2_t2 ON (SUBQUERY2_t2 . `pk` = SUBQUERY2_t1 . `int_nokey` ) ) ) ) ) OR ( table1 . `varchar_key` = 'u' AND table1 . `int_key` = 111 ) OR ( table1 . `pk` IS NULL AND table1 . `int_key` <> table2 . `int_nokey` ) ORDER BY CONCAT ( table2 . `varchar_key`, table2 . `varchar_key` ); /* Diff: */ /* --- /var/folders/Pt/PtJd7NDTGgyOk3+iDGXrQk+++TI/-Tmp-///randgen95989-1250168159-server0.dump 2009-08-13 08:55:59.000000000 -0400 # +++ /var/folders/Pt/PtJd7NDTGgyOk3+iDGXrQk+++TI/-Tmp-///randgen95989-1250168159-server1.dump 2009-08-13 08:55:59.000000000 -0400 # @@ -0,0 +1 @@ # +NULL NULL NULL NULL NULL NULL */ DROP TABLE CC; DROP TABLE D; DROP TABLE BB; DROP TABLE B; #/* End of test case for query 1 */