MySQL Bugs: #45220: Queries of the pattern - WHERE <col> <condition> HAVING X ORDER BY `pk` fail

Bug #45220	Queries of the pattern - WHERE <col> <condition> HAVING X ORDER BY `pk` fail
Submitted:	31 May 2009 19:02	Modified:	3 Sep 2009 14:46
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:	engine_condition_pushdown, ICP

Description:
Random query generator comparison of 5.1-bugteam -> 5.4 results in the following class of queries failing (difference between different server versions):

Reporting the simplified version of the queries here (as produced by ResultsetComparatorSimplify validator).  Will attach a file with full examples, failures and the simplified queries.

The pattern to the failing queries seems to be:
SELECT <key_column> X FROM C WHERE <column> <condition> HAVING X ORDER BY `pk`;

The <condition> generally seems to be of the pattern 'varchar_key' < 'a', though some failures paired this simple condition with something like WHERE `varchar_nokey` IN (`varchar_key`)

This failure / difference is characterized by extra rows being returned in 5.4 than were present in the 5.1 query:

# 11:58:30 Query: SELECT  OUTR . `datetime_key` AS X FROM C AS OUTR WHERE OUTR . `varchar_nokey` IN ( SELECT  INNR . `varchar_nokey` AS Y FROM CC AS INNR WHERE INNR . `int_nokey` <> 0 ORDER BY INNR . `int_key` ) AND OUTR . `varchar_key` < 'a' HAVING X = '2003-03-15 10:22:07' ORDER BY OUTR . `pk` , OUTR . `pk` failed: result length mismatch between servers (0 vs. 3)
--- /var/folders/Pt/PtJd7NDTGgyOk3+iDGXrQk+++TI/-Tmp-///randgen1711-server0.dump        2009-05-31 11:58:30.000000000 -0400
+++ /var/folders/Pt/PtJd7NDTGgyOk3+iDGXrQk+++TI/-Tmp-///randgen1711-server1.dump        2009-05-31 11:58:30.000000000 -0400
@@ -0,0 +1,3 @@
+0000-00-00 00:00:00
+2001-01-13 00:00:00
+2008-09-11 00:00:00
# 11:58:32 Simplified query:  SELECT `datetime_key` X  FROM C  WHERE `varchar_nokey`  IN ( `varchar_nokey`  )  AND `varchar_key`  < 'a' HAVING X  ORDER  BY `pk`

How to repeat:
Run the Random Query Generator:
./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

And observe the failures.

You should also be able to create a simple grammar file that focuses entirely on the failing pattern to reduce run time.

Suggested fix:
Ensure that 5.4 and 5.1 process such queries correctly and with uniformity.

RQG failure output for this class of queries

Attachment: bug45220_failures.txt (text/plain), 2.31 KiB.

What happens is that in queries containing HAVING and ORDER BY, the HAVING part is not observed at all and all rows are returned. See previous, already closed, bug #38199, bug #38072 , bug #37977.

Test case produced by RQG (this round only found 1 failure of this type from 100k queries)

/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `C` (
  `pk` int(11) NOT NULL AUTO_INCREMENT,
  `date_key` date NOT NULL,
  `date_nokey` date NOT NULL,
  PRIMARY KEY (`pk`),
  KEY `date_key` (`date_key`)
) ENGINE=MyISAM AUTO_INCREMENT=21 DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;
INSERT INTO `C` VALUES (1,'2007-12-01','2007-12-01'),(2,'0000-00-00','0000-00-00'),(3,'0000-00-00','0000-00-00'),(4,'2002-05-07','2002-05-07'),(5,'0000-00-00','0000-00-00'),(6,'0000-00-00','0000-00-00'),(7,'0000-00-00','0000-00-00'),(8,'0000-00-00','0000-00-00'),(9,'0000-00-00','0000-00-00'),(10,'2000-12-18','2000-12-18'),(11,'2005-11-13','2005-11-13'),(12,'0000-00-00','0000-00-00'),(13,'2003-11-12','2003-11-12'),(14,'2006-02-20','2006-02-20'),(15,'2005-02-12','2005-02-12'),(16,'2009-07-20','2009-07-20'),(17,'2005-02-24','2005-02-24'),(18,'0000-00-00','0000-00-00'),(19,'0000-00-00','0000-00-00'),(20,'0000-00-00','0000-00-00');

/*

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  OUTR . `date_nokey` AS X FROM C AS OUTR WHERE ( OUTR . `pk` , OUTR . `pk` ) IN ( SELECT DISTINCT INNR . `pk` AS X , INNR . `pk` AS Y FROM BB AS INNR WHERE INNR . `varchar_key` IS NOT NULL  ) AND OUTR . `date_key` BETWEEN '2005-03-10' AND '2009-03-14' HAVING X <= 3 ORDER BY OUTR . `pk` , OUTR . `pk`;

ORIGINAL DIFF:

--- /var/folders/Pt/PtJd7NDTGgyOk3+iDGXrQk+++TI/-Tmp-///randgen11021-server0.dump	2009-06-16 15:14:23.000000000 +0100
+++ /var/folders/Pt/PtJd7NDTGgyOk3+iDGXrQk+++TI/-Tmp-///randgen11021-server1.dump	2009-06-16 15:14:23.000000000 +0100
@@ -0,0 +1 @@
+2005-11-13

SIMPLIFIED QUERY:

 SELECT `date_nokey` X  FROM C  WHERE ( `pk`  , `pk`  )  IN (  SELECT `pk`  , `pk`  )  AND `date_key`  BETWEEN '2005-03-10' AND '2009-03-14' HAVING X  <=  3  ORDER  BY `pk`   ;

SIMPLIFIED DIFF:

--- /var/folders/Pt/PtJd7NDTGgyOk3+iDGXrQk+++TI/-Tmp-///randgen11021-server0.dump	2009-06-16 15:14:23.000000000 +0100
+++ /var/folders/Pt/PtJd7NDTGgyOk3+iDGXrQk+++TI/-Tmp-///randgen11021-server1.dump	2009-06-16 15:14:23.000000000 +0100
@@ -0,0 +1,3 @@
+2005-11-13
+2006-02-20
+2007-12-01

*/

Setting engine_condition_pushdown to 0 will produce matching result sets on 5.1 and azalea.

Duplicate of the bug#45227.

Duplicate of the bug#45227.

Duplicate of the bug#45227.