/* Server0: version = 5.4.4-alpha-debug-log */ /* Server1: version = 5.1.38-debug-log */ /* The value of optimizer_switch is distinct between the two servers: */ 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: */ 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: */ 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 */ C; DROP TABLE /*! IF EXISTS */ D; --enable_warnings CREATE TABLE `C` ( `int_nokey` int(11) DEFAULT NULL, `int_key` int(11) DEFAULT NULL, `varchar_nokey` varchar(1) DEFAULT NULL, KEY `int_key` (`int_key`), KEY `varchar_key` (`int_key`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; INSERT INTO `C` VALUES (7,9,'m'),(9,3,'m'),(7,9,'k'),(4,NULL,'r'),(2,9,'t'),(6,3,'j'),(8,8,'u'),(NULL,8,'h'),(5,53,'o'),(NULL,0,NULL),(6,5,'k'),(188,166,'e'),(2,3,'n'),(1,0,'t'),(1,1,'c'),(0,9,'m'),(9,5,'y'),(NULL,6,'f'),(4,2,'d'),(6,NULL,'r'); CREATE TABLE `D` ( `int_nokey` int(11) DEFAULT NULL, `int_key` int(11) DEFAULT NULL, `varchar_nokey` varchar(1) DEFAULT NULL, KEY `int_key` (`int_key`), KEY `varchar_key` (`int_key`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; INSERT INTO `D` VALUES (8,0,'c'),(6,0,'o'),(6,7,'c'),(3,8,'d'),(9,4,'v'),(2,6,'m'),(1,5,'j'),(8,NULL,'f'),(0,NULL,'n'),(9,8,'z'),(8,8,'h'),(NULL,8,'q'),(0,1,'w'),(5,1,'z'),(1,5,'j'),(1,2,'a'),(6,7,'m'),(6,6,'n'),(1,4,'e'),(8,7,'u'),(1,0,'s'),(0,9,'u'),(4,3,'r'),(9,5,'g'),(8,1,'o'),(5,1,'w'),(9,5,'b'),(5,9,NULL),(NULL,2,'y'),(NULL,5,'y'),(105,248,'u'),(0,0,'p'),(3,8,'s'),(1,1,'e'),(75,255,'d'),(9,9,'d'),(7,9,'c'),(NULL,3,'b'),(NULL,9,'t'),(4,6,NULL),(0,4,'y'),(204,60,'c'),(0,7,'d'),(9,1,'x'),(8,6,'p'),(7,4,'e'),(8,NULL,'g'),(NULL,8,'x'),(6,0,'s'),(5,8,'e'),(2,151,'l'),(3,7,'p'),(7,6,'h'),(NULL,NULL,'m'),(145,23,'n'),(0,2,'v'),(1,4,'b'),(7,NULL,'x'),(3,NULL,'r'),(NULL,77,'t'),(2,NULL,'w'),(2,NULL,'w'),(2,7,'k'),(8,1,'a'),(6,9,'t'),(1,6,'z'),(NULL,2,'e'),(1,3,'q'),(0,0,'e'),(4,NULL,'v'),(1,6,'d'),(1,3,'u'),(27,195,'o'),(4,5,'b'),(6,2,'c'),(2,7,'q'),(248,25,NULL),(NULL,NULL,'h'),(9,0,'d'),(75,98,'w'),(2,6,'m'),(9,5,'i'),(4,0,'w'),(0,3,'f'),(0,1,'k'),(1,1,'v'),(119,147,'c'),(1,3,'y'),(7,3,'h'),(2,NULL,NULL),(7,2,'t'),(2,1,'l'),(6,8,'a'),(4,8,'r'),(5,8,'s'),(7,0,'z'),(1,1,'j'),(7,8,'c'),(2,5,'f'),(1,4,'g'); SELECT table2 .`int_key` field12 , ( SELECT SUM( `int_nokey` ) FROM D WHERE `int_key` = table1 .`int_key` ) FROM C table1 RIGHT JOIN C table2 ON table1 .`varchar_nokey` GROUP BY field12 ; /* Diff: */ /* --- /var/folders/Pt/PtJd7NDTGgyOk3+iDGXrQk+++TI/-Tmp-///randgen39076-1250350990-server0.dump 2009-08-15 11:43:10.000000000 -0400 # +++ /var/folders/Pt/PtJd7NDTGgyOk3+iDGXrQk+++TI/-Tmp-///randgen39076-1250350990-server1.dump 2009-08-15 11:43:10.000000000 -0400 # @@ -1,11 +1,11 @@ # -0 36 # -1 36 # -166 36 # -2 36 # -3 36 # -5 36 # -53 36 # -6 36 # -8 36 # -9 36 # -NULL 36 # +0 NULL # +1 NULL # +166 NULL # +2 NULL # +3 NULL # +5 NULL # +53 NULL # +6 NULL # +8 NULL # +9 NULL # +NULL NULL */ DROP TABLE C; 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 */ C; DROP TABLE /*! IF EXISTS */ D; DROP TABLE /*! IF EXISTS */ BB; --enable_warnings CREATE TABLE `CC` ( `pk` int(11) NOT NULL AUTO_INCREMENT, `int_nokey` int(11) DEFAULT NULL, `int_key` int(11) DEFAULT NULL, `date_key` date 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 `date_key` (`date_key`), KEY `time_key` (`time_key`), KEY `datetime_key` (`datetime_key`), KEY `varchar_key` (`varchar_key`,`int_key`) ) ENGINE=InnoDB AUTO_INCREMENT=30 DEFAULT CHARSET=latin1; INSERT INTO `CC` VALUES (10,3,186,'2005-02-15','19:53:05','2008-05-26 12:27:10','m','m'),(11,6,NULL,NULL,'19:18:56','2004-12-14 16:37:30','y','y'),(12,92,2,'2008-11-04','10:55:12','2003-02-11 21:19:41','j','j'),(13,7,3,'2004-09-04','00:25:00','2009-10-18 02:27:49','d','d'),(14,NULL,0,'2006-06-05','12:35:47','2000-09-26 07:45:57','z','z'),(15,3,133,'1900-01-01','19:53:03',NULL,'e','e'),(16,5,1,'1900-01-01','17:53:30','2005-11-10 12:40:29','h','h'),(17,1,8,'1900-01-01','11:35:49','2009-04-25 00:00:00','b','b'),(18,2,5,'2005-01-13',NULL,'2002-11-27 00:00:00','s','s'),(19,NULL,5,'2006-05-21','06:01:40','2004-01-26 20:32:32','e','e'),(20,1,8,'2003-09-08','05:45:11','2007-10-26 11:41:40','j','j'),(21,0,6,'2006-12-23','00:00:00','2005-10-07 00:00:00','e','e'),(22,210,51,'2006-10-15','00:00:00','2000-07-15 05:00:34','f','f'),(23,8,4,'2005-04-06','06:11:01','2000-04-03 16:33:32','v','v'),(24,7,7,'2008-04-07','13:02:46',NULL,'x','x'),(25,5,6,'2006-10-10','21:44:25','2001-04-25 01:26:12','m','m'),(26,NULL,4,'1900-01-01','22:43:58','2000-12-27 00:00:00','c','c'),(27,6,7,'1900-01-01',NULL,'2005-10-04 00:00:00','m','m'),(28,9,3,'2005-05-07','06:43:09','2004-03-08 02:50:35','b','b'),(29,3,2,'2000-07-19','12:20:22','2004-09-24 09:21:35','x','x'); CREATE TABLE `C` ( `pk` int(11) NOT NULL AUTO_INCREMENT, `int_nokey` int(11) DEFAULT NULL, `int_key` int(11) DEFAULT NULL, `date_key` date 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 `date_key` (`date_key`), KEY `time_key` (`time_key`), KEY `datetime_key` (`datetime_key`), KEY `varchar_key` (`varchar_key`,`int_key`) ) ENGINE=InnoDB AUTO_INCREMENT=21 DEFAULT CHARSET=latin1; INSERT INTO `C` VALUES (1,7,9,'2001-09-19','20:25:14',NULL,'m','m'),(2,9,3,'2004-09-12','13:47:24','1900-01-01 00:00:00','m','m'),(3,7,9,NULL,'19:24:11','2009-07-25 00:00:00','k','k'),(4,4,NULL,'2002-07-19','15:59:13',NULL,'r','r'),(5,2,9,'2002-12-16','00:00:00','2008-07-27 00:00:00','t','t'),(6,6,3,'2006-02-08','15:15:04','2002-11-13 16:37:31','j','j'),(7,8,8,'2006-08-28','11:32:06','1900-01-01 00:00:00','u','u'),(8,NULL,8,'2001-04-14','18:32:33','2003-12-10 00:00:00','h','h'),(9,5,53,'2000-01-05','15:19:25','2001-12-21 22:38:22','o','o'),(10,NULL,0,'2003-12-06','19:03:19','2008-12-13 23:16:44',NULL,NULL),(11,6,5,'1900-01-01','00:39:46','2005-08-15 12:39:41','k','k'),(12,188,166,'2002-11-27',NULL,NULL,'e','e'),(13,2,3,NULL,'00:00:00','2006-09-11 12:06:14','n','n'),(14,1,0,'2003-05-27','13:12:11','2007-12-15 12:39:34','t','t'),(15,1,1,'2005-05-03','04:56:48','2005-08-09 00:00:00','c','c'),(16,0,9,'2001-04-18','19:56:05','2001-09-02 22:50:02','m','m'),(17,9,5,'2005-12-27','19:35:19','2005-12-16 22:58:11','y','y'),(18,NULL,6,'2004-08-20','05:03:03','2007-04-19 00:19:53','f','f'),(19,4,2,'1900-01-01','18:38:59','1900-01-01 00:00:00','d','d'),(20,6,NULL,'2003-05-12',NULL,'2000-09-12 00:00:00','r','r'); CREATE TABLE `D` ( `pk` int(11) NOT NULL AUTO_INCREMENT, `int_nokey` int(11) DEFAULT NULL, `int_key` int(11) DEFAULT NULL, `date_key` date 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 `date_key` (`date_key`), KEY `time_key` (`time_key`), KEY `datetime_key` (`datetime_key`), KEY `varchar_key` (`varchar_key`,`int_key`) ) ENGINE=InnoDB AUTO_INCREMENT=101 DEFAULT CHARSET=latin1; INSERT INTO `D` VALUES (1,8,0,'2003-01-07','14:34:45','2004-08-10 09:09:31','c','c'),(2,6,0,NULL,'11:49:48','2005-03-21 04:31:40','o','o'),(3,6,7,'2005-03-12','18:12:55','2002-10-25 23:50:35','c','c'),(4,3,8,'2000-08-02','18:30:05','2001-04-01 21:14:04','d','d'),(5,9,4,'1900-01-01','14:19:30','2005-03-12 06:02:34','v','v'),(6,2,6,'2006-07-06','05:20:04','2001-05-06 14:49:12','m','m'),(7,1,5,'2006-12-24','20:29:31','2004-04-25 00:00:00','j','j'),(8,8,NULL,'2004-11-16','07:08:09','2001-03-22 18:38:43','f','f'),(9,0,NULL,'2002-09-09','14:49:14','2006-04-25 21:03:02','n','n'),(10,9,8,NULL,'00:00:00','2009-09-07 18:40:43','z','z'),(11,8,8,'2008-06-24','09:58:06','2004-03-23 00:00:00','h','h'),(12,NULL,8,'2001-04-21',NULL,'2009-04-15 00:08:29','q','q'),(13,0,1,'2003-11-22','18:24:16','2000-04-21 00:00:00','w','w'),(14,5,1,'2004-09-12','17:39:57','2000-02-17 19:41:23','z','z'),(15,1,5,'2006-06-20','08:23:21','2003-09-20 07:38:14','j','j'),(16,1,2,NULL,NULL,'2000-11-28 20:42:12','a','a'),(17,6,7,'2001-11-25','21:50:46','2005-06-12 11:13:17','m','m'),(18,6,6,'2004-10-26','12:33:17','1900-01-01 00:00:00','n','n'),(19,1,4,'2005-01-19','03:06:43','2006-02-09 20:41:06','e','e'),(20,8,7,'2008-07-06','03:46:14','2004-05-22 01:05:57','u','u'),(21,1,0,'1900-01-01','20:34:52','2004-03-04 13:46:31','s','s'),(22,0,9,'1900-01-01',NULL,'1900-01-01 00:00:00','u','u'),(23,4,3,'2004-06-08','10:41:20','2004-10-20 07:20:19','r','r'),(24,9,5,'2007-02-20','08:43:11','2006-04-17 00:00:00','g','g'),(25,8,1,'2008-06-18',NULL,'2000-10-27 00:00:00','o','o'),(26,5,1,'2008-05-15','10:17:51','2007-04-14 08:54:06','w','w'),(27,9,5,'2005-10-06','06:34:09','2008-04-12 17:03:52','b','b'),(28,5,9,NULL,'21:22:47','2007-02-19 17:37:09',NULL,NULL),(29,NULL,2,'2006-10-12','04:02:32','1900-01-01 00:00:00','y','y'),(30,NULL,5,'2005-01-24','02:33:14','2001-10-10 08:32:27','y','y'),(31,105,248,'2009-06-27','16:32:56',NULL,'u','u'),(32,0,0,NULL,'21:32:42','2001-12-16 05:31:53','p','p'),(33,3,8,NULL,'23:04:47','2003-07-19 18:03:28','s','s'),(34,1,1,'1900-01-01','22:05:43','2001-03-27 11:44:10','e','e'),(35,75,255,'2005-12-22','02:05:45','2008-06-15 02:13:00','d','d'),(36,9,9,'2005-05-03','00:00:00','2009-03-14 21:29:56','d','d'),(37,7,9,'2003-05-27','18:09:07','2005-01-02 00:00:00','c','c'),(38,NULL,3,'2006-05-25','10:54:06','2007-07-16 04:44:07','b','b'),(39,NULL,9,NULL,'23:15:50','2003-08-26 21:38:26','t','t'),(40,4,6,'2009-01-04','10:17:40','2004-04-19 04:18:47',NULL,NULL),(41,0,4,'2009-02-14','03:37:09','2000-01-06 20:32:48','y','y'),(42,204,60,'2003-01-16','22:26:06','2006-06-23 13:27:17','c','c'),(43,0,7,'1900-01-01','17:10:38','2007-11-27 00:00:00','d','d'),(44,9,1,'2007-06-26','00:00:00','2002-04-03 12:06:51','x','x'),(45,8,6,'2004-03-27','17:08:49','2008-12-28 09:47:42','p','p'),(46,7,4,NULL,'19:04:40','2002-04-04 10:07:54','e','e'),(47,8,NULL,'2005-06-06','20:53:28','2003-04-26 02:55:13','g','g'),(48,NULL,8,'2003-03-02','11:46:03',NULL,'x','x'),(49,6,0,'2004-05-13',NULL,'2009-02-19 03:17:06','s','s'),(50,5,8,'2005-09-13','10:58:07','1900-01-01 00:00:00','e','e'),(51,2,151,'2005-10-03','00:00:00','2000-11-10 08:20:01','l','l'),(52,3,7,'2005-10-14','09:43:15','2008-02-10 00:00:00','p','p'),(53,7,6,NULL,'21:40:32','1900-01-01 00:00:00','h','h'),(54,NULL,NULL,'2005-09-16','00:17:44',NULL,'m','m'),(55,145,23,'2005-03-10','16:47:26','2001-02-05 02:01:50','n','n'),(56,0,2,'2000-06-19','00:00:00','2000-10-28 08:44:25','v','v'),(57,1,4,'2002-11-03','05:25:59','2005-03-20 10:53:59','b','b'),(58,7,NULL,'2009-01-05','00:00:00','2001-06-02 13:54:13','x','x'),(59,3,NULL,'2003-05-22','20:33:04','1900-01-01 00:00:00','r','r'),(60,NULL,77,'2005-07-02','00:46:12','2009-07-16 13:05:43','t','t'),(61,2,NULL,'1900-01-01','00:00:00','2009-03-26 23:16:20','w','w'),(62,2,NULL,'2006-06-21','02:13:59','2003-02-06 18:12:15','w','w'),(63,2,7,NULL,'02:54:47','2006-06-05 03:22:51','k','k'),(64,8,1,'2005-12-16','18:13:59','2002-02-10 05:47:27','a','a'),(65,6,9,'2004-11-05','13:53:08','2001-08-01 08:50:52','t','t'),(66,1,6,NULL,'22:21:30','1900-01-01 00:00:00','z','z'),(67,NULL,2,'2004-09-14','11:41:50',NULL,'e','e'),(68,1,3,'2002-04-06','15:20:02','1900-01-01 00:00:00','q','q'),(69,0,0,NULL,NULL,'2000-09-23 00:00:00','e','e'),(70,4,NULL,'2002-11-13',NULL,'2007-07-09 08:32:49','v','v'),(71,1,6,'2006-05-27','07:51:52','2000-01-05 00:00:00','d','d'),(72,1,3,'2000-12-22','00:00:00','2000-09-24 00:00:00','u','u'),(73,27,195,'2004-02-21',NULL,'2005-05-06 00:00:00','o','o'),(74,4,5,'2009-05-15',NULL,'2000-03-11 00:00:00','b','b'),(75,6,2,'2008-12-12','12:31:05','2001-09-02 16:17:35','c','c'),(76,2,7,'2000-04-15','00:00:00','2006-04-25 05:43:44','q','q'),(77,248,25,NULL,'01:16:45','2009-10-25 22:04:02',NULL,NULL),(78,NULL,NULL,'2001-10-18','20:38:54','2004-08-06 00:00:00','h','h'),(79,9,0,'2008-05-25','00:30:15','2001-11-27 05:07:57','d','d'),(80,75,98,'2004-12-02','23:46:36','2009-06-28 03:18:39','w','w'),(81,2,6,'2002-02-15','19:03:13','2000-03-12 00:00:00','m','m'),(82,9,5,'2002-03-03','10:54:27',NULL,'i','i'),(83,4,0,NULL,'00:25:47','2007-10-20 00:00:00','w','w'),(84,0,3,'2003-01-26','08:44:27','2009-09-27 00:00:00','f','f'),(85,0,1,'2001-12-19','08:15:38','2002-07-16 00:00:00','k','k'),(86,1,1,'2001-08-07','19:56:21','2005-02-20 00:00:00','v','v'),(87,119,147,'2005-02-16','00:00:00',NULL,'c','c'),(88,1,3,'2006-06-10','20:50:52','2001-07-16 00:00:00','y','y'),(89,7,3,NULL,'03:54:39','2009-05-20 21:04:12','h','h'),(90,2,NULL,'2005-04-06','23:58:17','2002-03-13 10:55:40',NULL,NULL),(91,7,2,'2003-04-27','12:54:58','2005-07-12 00:00:00','t','t'),(92,2,1,'2005-10-13','04:02:43','2006-07-22 09:46:34','l','l'),(93,6,8,'2003-10-02','11:31:12','2001-09-01 00:00:00','a','a'),(94,4,8,'2005-09-09','20:20:04','2002-05-27 18:38:45','r','r'),(95,5,8,NULL,'00:22:24',NULL,'s','s'),(96,7,0,'2006-02-15','10:09:31',NULL,'z','z'),(97,1,1,'1900-01-01',NULL,'2009-08-08 22:38:53','j','j'),(98,7,8,'2003-12-24','18:45:35',NULL,'c','c'),(99,2,5,'2001-07-26','11:49:25','2007-04-25 05:08:49','f','f'),(100,1,4,'2001-05-13','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, `date_key` date 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 `date_key` (`date_key`), KEY `time_key` (`time_key`), KEY `datetime_key` (`datetime_key`), KEY `varchar_key` (`varchar_key`,`int_key`) ) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=latin1; INSERT INTO `BB` VALUES (10,1,9,'2006-06-14','19:48:31','1900-01-01 00:00:00','r','r'),(11,5,9,'2002-09-12','00:00:00','2006-12-03 09:37:26','a','a'); SELECT DISTINCT SQL_SMALL_RESULT ( SELECT MIN( SUBQUERY1_t1 . `pk` ) AS SUBQUERY1_field1 FROM ( BB AS SUBQUERY1_t1 ) WHERE SUBQUERY1_t1 . `varchar_key` < table1 . `varchar_nokey` AND SUBQUERY1_t1 . `varchar_key` != SUBQUERY1_t1 . `varchar_nokey` ) AS field1 , table2 . `date_key` AS field2 , table1 . `int_key` AS field3 , table1 . `pk` AS field4 , CONCAT ( table2 . `varchar_nokey` , table1 . `varchar_key` ) AS field5 , table2 . `int_key` AS field6 , CONCAT ( table2 . `varchar_nokey` , table2 . `varchar_key` ) AS field7 , table1 . `datetime_key` AS field8 , table1 . `time_key` AS field9 , table1 . `date_key` AS field10 , table2 . `varchar_nokey` AS field11 , ( ( table1 . `int_nokey` ) * ( table2 . `int_key` ) ) AS field12 , table1 . `time_key` AS field13 , ( SELECT SUM( SUBQUERY2_t1 . `int_nokey` ) AS SUBQUERY2_field1 FROM ( D AS SUBQUERY2_t1 LEFT JOIN CC AS SUBQUERY2_t2 ON (SUBQUERY2_t2 . `varchar_key` = SUBQUERY2_t1 . `varchar_key` ) ) WHERE SUBQUERY2_t1 . `int_key` = table1 . `int_key` AND SUBQUERY2_t2 . `pk` <= SUBQUERY2_t2 . `pk` ) AS field14 FROM ( C AS table1 RIGHT OUTER JOIN C AS table2 ON (table2 . `pk` = table1 . `varchar_nokey` ) ) WHERE ( table2 . `varchar_key` < ALL ( SELECT SUBQUERY3_t1 . `varchar_nokey` AS SUBQUERY3_field1 FROM ( BB AS SUBQUERY3_t1 ) WHERE SUBQUERY3_t1 . `int_nokey` >= table2 . `int_nokey` AND SUBQUERY3_t1 . `varchar_nokey` > table1 . `varchar_key` ) ) AND table1 . `varchar_key` = 'g' OR ( ( 'm', 'k' ) IN ( SELECT SUBQUERY4_t2 . `varchar_nokey` AS SUBQUERY4_field1 , SUBQUERY4_t2 . `varchar_key` AS SUBQUERY4_field2 FROM ( CC AS SUBQUERY4_t1 RIGHT OUTER JOIN BB AS SUBQUERY4_t2 ON (SUBQUERY4_t2 . `int_nokey` = SUBQUERY4_t1 . `pk` ) ) WHERE SUBQUERY4_t1 . `varchar_key` <> SUBQUERY4_t1 . `varchar_nokey` ) OR table1 . `pk` IS NULL ) GROUP BY field1, field2, field3, field4, field5, field6, field7, field8, field9, field10, field11, field12, field13, field14 ORDER BY CONCAT ( table2 . `varchar_key`, table2 . `varchar_key` ) , table1 . `varchar_key` ASC , table1 . `pk` , table2 .`pk` ASC , field14 ASC , field13 ASC, field1, field2, field3, field4, field5, field6, field7, field8, field9, field10, field11, field12, field13, field14 LIMIT 2 OFFSET 1; /* Diff: */ /* --- /var/folders/Pt/PtJd7NDTGgyOk3+iDGXrQk+++TI/-Tmp-///randgen39076-1250350993-server0.dump 2009-08-15 11:43:13.000000000 -0400 # +++ /var/folders/Pt/PtJd7NDTGgyOk3+iDGXrQk+++TI/-Tmp-///randgen39076-1250350993-server1.dump 2009-08-15 11:43:13.000000000 -0400 # @@ -1,2 +1,2 @@ # -NULL 1900-01-01 NULL NULL NULL 2 dd NULL NULL NULL d NULL NULL 26 # -NULL 2005-05-03 NULL NULL NULL 1 cc NULL NULL NULL c NULL NULL 26 # +NULL 1900-01-01 NULL NULL NULL 2 dd NULL NULL NULL d NULL NULL NULL # +NULL 2005-05-03 NULL NULL NULL 1 cc NULL NULL NULL c NULL NULL NULL */ DROP TABLE CC; DROP TABLE C; DROP TABLE D; DROP TABLE BB; #/* End of test case for query 1 */