MySQL Bugs: #45222: Differing query results between 5.1 and 5.4 for certain JOIN pattern

Bug #45222	Differing query results between 5.1 and 5.4 for certain JOIN pattern
Submitted:	31 May 2009 19:45	Modified:	2 Sep 2009 11:40
Reporter:	Patrick Crews	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S3 (Non-critical)
Version:	5.4	OS:	Any
Assigned to:	Assigned Account	CPU Architecture:	Any
Tags:	alias, ON clause, Optimizer, RQG

Description:
Discovered a pattern of failing queries via comparing 5.1-bugteam to 5.4 with the Random Query Generator.  Only viewed this pattern one time in my run, so not much information on defining the failure is currently available.

Simplest form of failure:  
SELECT OUTR .`int_nokey`  FROM B OUTR2  JOIN C OUTR  ON OUTR2 .`pk`  = OUTR .`int_key`  WHERE OUTR .`int_key`

# 12:03:15 Query: SELECT DISTINCT OUTR . `int_nokey` AS X FROM B AS OUTR2 LEFT JOIN C AS OUTR ON ( OUTR2 . `pk` = OUTR . `int_key` ) WHERE OUTR . `int_key` IN ( SELECT DISTINCT INNR . `int_key` AS Y FROM BB AS INNR2 LEFT JOIN CC AS INNR ON ( INNR2 . `varchar_nokey` <= INNR . `varchar_key` ) WHERE INNR . `varchar_key` > 'c'  ) AND ( OUTR . `varchar_key` < 'g' XOR OUTR . `varchar_key` = 'w' )  ORDER BY OUTR . `int_nokey` , OUTR . `pk` failed: result length mismatch between servers (1 vs. 0)
--- /var/folders/Pt/PtJd7NDTGgyOk3+iDGXrQk+++TI/-Tmp-///randgen1711-server0.dump        2009-05-31 12:03:15.000000000 -0400
+++ /var/folders/Pt/PtJd7NDTGgyOk3+iDGXrQk+++TI/-Tmp-///randgen1711-server1.dump        2009-05-31 12:03:15.000000000 -0400
@@ -1 +0,0 @@
-7
# 12:03:20 Simplified query:  SELECT OUTR .`int_nokey`  FROM B OUTR2  JOIN C OUTR  ON OUTR2 .`pk`  = OUTR .`int_key`  WHERE OUTR .`int_key`

How to repeat:
Run the RQG:
./runall.pl \
  --basedir1=<path>/mysql-5.1-bugteam \
  --basedir2=<path>/mysql-azalea \
  --grammar=conf/subquery_semijoin.yy \
  --threads=1 \
  --queries=1000 \
  --mysqld2=--init-file=<path>/mysql-test-extra-6.0/mysql-test/gentest/init/no_subquery.sql \
  --validator=ResultsetComparatorSimplify \
  --queries=100000

It should also be possible to create a simple grammar file that focuses on the particular query pattern noted here.

Suggested fix:
Ensure that 5.4 processes queries properly.

RQG-generated .test file for reproducing this:

/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `C` (
  `pk` int(11) NOT NULL AUTO_INCREMENT,
  `int_nokey` int(11) NOT NULL,
  PRIMARY KEY (`pk`)
) ENGINE=MyISAM AUTO_INCREMENT=21 DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;
INSERT INTO `C` VALUES (1,9),(2,0),(3,8),(4,3),(5,7),(6,0),(7,1),(8,9),(9,0),(10,9),(11,0),(12,5),(13,0),(14,8),(15,8),(16,5),(17,1),(18,7),(19,0),(20,9);
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `B` (
  `pk` int(11) NOT NULL AUTO_INCREMENT,
  `int_nokey` int(11) NOT NULL,
  PRIMARY KEY (`pk`)
) ENGINE=MyISAM AUTO_INCREMENT=3 DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;
INSERT INTO `B` VALUES (1,5),(2,0);

/*

OPTIMIZER SETTINGS:

*/

SET SESSION optimizer_switch = 'firstmatch=off,index_merge=off,index_merge_union=off,index_merge_sort_union=off,index_merge_intersection=off,loosescan=off,materialization=off,semijoin=off';
SET SESSION optimizer_use_mrr = 'disable';
SET SESSION engine_condition_pushdown = '1';
SET SESSION join_cache_level = '1';
SET GLOBAL optimizer_switch = 'firstmatch=off,index_merge=off,index_merge_union=off,index_merge_sort_union=off,index_merge_intersection=off,loosescan=off,materialization=off,semijoin=off';
SET GLOBAL optimizer_use_mrr = 'disable';
SET GLOBAL engine_condition_pushdown = '1';
SET GLOBAL join_cache_level = '1';

/*

ORIGINAL QUERY:

SELECT DISTINCT SUM( OUTR . `int_nokey` ) AS X FROM B AS OUTR2 LEFT JOIN C AS OUTR ON ( OUTR2 . `pk` = OUTR . `pk` ) WHERE OUTR . `pk` IN ( SELECT  INNR . `int_key` AS Y FROM CC AS INNR WHERE INNR . `varchar_nokey` <> INNR . `varchar_nokey` AND INNR . `time_key` > INNR . `datetime_nokey` ORDER BY INNR . `varchar_nokey` ) AND OUTR . `varchar_nokey` IS NULL XOR OUTR . `int_nokey` <= 1 HAVING X <> '2006-11-02 22:30:40' ORDER BY OUTR . `varchar_nokey` , OUTR . `pk`;

ORIGINAL DIFF:

--- /var/folders/Pt/PtJd7NDTGgyOk3+iDGXrQk+++TI/-Tmp-///randgen19289-server0.dump	2009-06-17 08:00:22.000000000 +0100
+++ /var/folders/Pt/PtJd7NDTGgyOk3+iDGXrQk+++TI/-Tmp-///randgen19289-server1.dump	2009-06-17 08:00:22.000000000 +0100
@@ -1 +0,0 @@
-0

SIMPLIFIED QUERY:

 SELECT OUTR .`int_nokey`  FROM B OUTR2  JOIN C OUTR  ON OUTR2 .`pk`  = OUTR .`pk`  WHERE OUTR .`pk`   ;

SIMPLIFIED DIFF:

--- /var/folders/Pt/PtJd7NDTGgyOk3+iDGXrQk+++TI/-Tmp-///randgen19289-server0.dump	2009-06-17 08:00:22.000000000 +0100
+++ /var/folders/Pt/PtJd7NDTGgyOk3+iDGXrQk+++TI/-Tmp-///randgen19289-server1.dump	2009-06-17 08:00:22.000000000 +0100
@@ -1,2 +1 @@
-0
 9

*/

Not affected by the values of optimizer_switch, optimizer_use_mrr, or engine_condition_pushdown

Duplicate of the bug#43600.