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