Bug #45227 Queries with IN + condition, HAVING, ORDER BY 'pk' and LIMIT produce bad results
Submitted: 31 May 2009 22:48 Modified: 22 Nov 2010 0:46
Reporter: Patrick Crews Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version:5.4 OS:Any
Assigned to: Evgeny Potemkin CPU Architecture:Any
Tags: 5.4, having, limit, Optimizer, regression, RQG, subquery

[31 May 2009 22:48] Patrick Crews
Description:
Queries of the pattern:
SELECT <col_1> <alias> FROM <table> WHERE <col_list> IN <subquery> AND <condition> HAVING <alias> ORDER BY `pk' LIMIT <digit>

are producing differing result sets when run against 5.1-bugteam and 5.4.

The failures are rare (2/100,000) and fail in that 5.4 returns rows when 5.1-bugteam does not:

# 17:09:44 Query: SELECT  GRANDPARENT1 . `int_key` AS G1 FROM CC AS GRANDPARENT1 WHERE ( GRANDPARENT1 . `varchar_nokey` , GRANDPARENT1 . `varchar_key` ) IN ( SELECT  PARENT1 . `varchar_nokey` AS P1 , PARENT1 . `varchar_nokey` AS P2 FROM CC AS PARENT1 LEFT JOIN BB AS PARENT2 ON ( PARENT1 . `int_key` = PARENT2 . `int_nokey` )  ORDER BY PARENT1 . `varchar_key` ) AND GRANDPARENT1 . `int_key` < 7 HAVING G1 > '2000-10-19 20:24:07' ORDER BY GRANDPARENT1 . `pk` LIMIT 8 failed: result length mismatch between servers (0 vs. 8)
--- /var/folders/Pt/PtJd7NDTGgyOk3+iDGXrQk+++TI/-Tmp-///randgen3502-server0.dump        2009-05-31 17:09:44.000000000 -0400
+++ /var/folders/Pt/PtJd7NDTGgyOk3+iDGXrQk+++TI/-Tmp-///randgen3502-server1.dump        2009-05-31 17:09:44.000000000 -0400
@@ -0,0 +1,8 @@
+0
+0
+0
+1
+2
+3
+3
+3
# 17:09:47 Simplified query:  SELECT `int_key` G1  FROM CC  WHERE ( `varchar_nokey`  , `varchar_key`  )  IN (  SELECT `varchar_nokey`  , `varchar_nokey`  )  AND `int_key`  <  7  HAVING G1  ORDER  BY `pk`  LIMIT  8

How to repeat:
Run the RQG:
./runall.pl \
  --basedir1=<path>/mysql-5.1-bugteam \
  --basedir2=<path>/mysql-azalea \
  --grammar=conf/subquery_semijoin_nested.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

Or you can create a simple grammar file focusing on the failure pattern.

Suggested fix:
Ensure this query pattern is being processed correctly.
[16 Jun 2009 14:19] Patrick Crews
Test case (produced by RQG):
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `CC` (
  `int_nokey` int(11) NOT NULL,
  `int_key` int(11) NOT NULL,
  `varchar_key` varchar(1) NOT NULL,
  `varchar_nokey` varchar(1) NOT NULL,
  KEY `int_key` (`int_key`),
  KEY `varchar_key` (`varchar_key`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;
INSERT INTO `CC` VALUES (0,8,'q','q'),(5,8,'m','m'),(7,3,'j','j'),(1,2,'z','z'),(8,2,'a','a'),(2,6,'',''),(1,8,'e','e'),(8,9,'t','t'),(5,2,'q','q'),(4,6,'b','b'),(5,5,'w','w'),(3,2,'m','m'),(0,4,'x','x'),(8,9,'',''),(0,6,'w','w'),(4,5,'x','x'),(0,0,'e','e'),(0,0,'e','e'),(2,8,'p','p'),(0,0,'x','x');

/*

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  GRANDPARENT1 . `varchar_nokey` AS G1 FROM CC AS GRANDPARENT1 WHERE GRANDPARENT1 . `int_nokey` IN ( SELECT  PARENT1 . `int_nokey` AS P1 FROM C AS PARENT1 LEFT JOIN C AS PARENT2 ON ( PARENT1 . `int_key` <= PARENT2 . `pk` ) WHERE ( ( PARENT1 . `varchar_nokey` < GRANDPARENT1 . `varchar_key` ) AND ( PARENT1 . `varchar_key` <= 'j' ) )  ) AND ( GRANDPARENT1 . `date_nokey` BETWEEN '2001-03-13' AND '2004-10-19' AND GRANDPARENT1 . `int_key` <= 4 ) HAVING G1 >= NULL ORDER BY GRANDPARENT1 . `varchar_key` LIMIT 6;

ORIGINAL DIFF:

--- /var/folders/Pt/PtJd7NDTGgyOk3+iDGXrQk+++TI/-Tmp-///randgen10619-server0.dump	2009-06-16 15:05:07.000000000 +0100
+++ /var/folders/Pt/PtJd7NDTGgyOk3+iDGXrQk+++TI/-Tmp-///randgen10619-server1.dump	2009-06-16 15:05:07.000000000 +0100
@@ -0,0 +1 @@
+q

SIMPLIFIED QUERY:

 SELECT `varchar_nokey` G1  FROM CC  WHERE `int_nokey`  AND '2004-10-19' AND `int_key`  <=  4  HAVING G1  ORDER  BY `varchar_key`  LIMIT  6   ;

SIMPLIFIED DIFF:

--- /var/folders/Pt/PtJd7NDTGgyOk3+iDGXrQk+++TI/-Tmp-///randgen10619-server0.dump	2009-06-16 15:05:08.000000000 +0100
+++ /var/folders/Pt/PtJd7NDTGgyOk3+iDGXrQk+++TI/-Tmp-///randgen10619-server1.dump	2009-06-16 15:05:08.000000000 +0100
@@ -0,0 +1,5 @@
+a
+j
+m
+q
+z

*/
[14 Jul 2009 22:26] Patrick Crews
Not affected by optimizer_switch, optimizer_use_mrr, or engine_condition_pushdown settings.
[3 Sep 2009 12:51] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/82331

2864 Evgeny Potemkin	2009-09-03
      Bug#45227: Lost HAVING clause led to a wrong result.
      
      The per-table condition is stored in the select_cond variable for each table.
      When index condition pushdown is used it is saved prior to
      pushing to pre_idx_push_select_cond variable. After that condition is split
      and pushable part is pushed and another part is left in the select_cond
      variable. If condition is fully pushed the select_cond variable is set to NULL.
      When there is no GROUP BY clause or aggregate functions HAVING condition is appended
      to select_cond variable.
      The test_if_skip_sort_order function tries to find an index to use for sorting
      instead of filesort and to do so it restores original condition. It assumes that
      condition is fully saved in the pre_idx_push_select_cond and simply overwrites the
      select_cond variable, thus loosing the appended HAVING condition.
      
      Now the test_if_skip_sort_order function saves original condition from
      select_cond variable prior to overwriting it and restores it before
      returning.
      The JOIN_TAB::set_cond function added to correctly set per-table condition.
     @ mysql-test/r/select.result
        A test case added for the bug#45277.
     @ mysql-test/t/select.test
        A test case added for the bug#45277.
     @ sql/sql_select.cc
        Bug#45227: Lost HAVING clause led to a wrong result.
        
        Now the test_if_skip_sort_order function saves original condition from
        select_cond variable prior to overwriting it and restores it before
        returning.
     @ sql/sql_select.h
        Bug#45227: Lost HAVING clause led to a wrong result.
        
        The JOIN_TAB::set_cond function added to correctly set per-table condition.
[21 Sep 2009 9:10] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/83853

2827 Evgeny Potemkin	2009-09-21 [merge]
      Auto-merged fix for the bug#45227.
[30 Sep 2009 8:18] Bugs System
Pushed into 6.0.14-alpha (revid:alik@sun.com-20090929093622-1mooerbh12e97zux) (version source revid:alik@sun.com-20090923103200-kyo2bakdo6tfb2fb) (merge vers: 6.0.14-alpha) (pib:11)
[6 Nov 2009 14:59] Evgeny Potemkin
HAVING clause could be lost if an index for ORDER BY is available. This wrongly allows additional rows to be returned.
[13 Nov 2009 2:08] Paul DuBois
Noted in 6.0.14 changelog.

A HAVING clause could be lost if an index for ORDER BY was available,
incorrectly allowing additional rows to be returned.

Setting report to NDI pending push to 5.5.x.
[13 May 2010 16:40] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/108265

3173 Evgeny Potemkin	2010-05-13
      Bug#45227: Lost HAVING clause led to a wrong result.
      
      The per-table condition is stored in the select_cond variable for each table.
      When index condition pushdown is used it is saved prior to
      pushing to pre_idx_push_select_cond variable. After that condition is split
      and pushable part is pushed and another part is left in the select_cond
      variable. If condition is fully pushed the select_cond variable is set to NULL.
      When there is no GROUP BY clause or aggregate functions HAVING condition is appended
      to select_cond variable.
      The test_if_skip_sort_order function tries to find an index to use for sorting
      instead of filesort and to do so it restores original condition. It assumes that
      condition is fully saved in the pre_idx_push_select_cond and simply overwrites the
      select_cond variable, thus loosing the appended HAVING condition.
      
      Now the test_if_skip_sort_order function saves original condition from
      select_cond variable prior to overwriting it and restores it before
      returning.
      The JOIN_TAB::set_cond function added to correctly set per-table condition.
      Original revid:epotemkin@mysql.com-20090903125059-z195j43fd6nvdws2
     @ mysql-test/r/select.result
        A test case added for the bug#45277.
     @ mysql-test/r/select_jcl6.result
        A test case added for the bug#45277.
     @ mysql-test/t/select.test
        A test case added for the bug#45277.
     @ sql/sql_select.cc
        Bug#45227: Lost HAVING clause led to a wrong result.
        
        Now the test_if_skip_sort_order function saves original condition from
        select_cond variable prior to overwriting it and restores it before
        returning.
     @ sql/sql_select.h
        Bug#45227: Lost HAVING clause led to a wrong result.
        
        The JOIN_TAB::set_cond function added to correctly set per-table condition.
[16 Aug 2010 6:32] Bugs System
Pushed into mysql-next-mr (revid:alik@sun.com-20100816062819-bluwgdq8q4xysmlg) (version source revid:alik@sun.com-20100816062612-enatdwnv809iw3s9) (pib:20)
[13 Nov 2010 16:16] Bugs System
Pushed into mysql-trunk 5.6.99-m5 (revid:alexander.nozdrin@oracle.com-20101113155825-czmva9kg4n31anmu) (version source revid:vasil.dimov@oracle.com-20100629074804-359l9m9gniauxr94) (merge vers: 5.6.99-m4) (pib:21)
[22 Nov 2010 0:46] Paul DuBois
Noted in 5.6.1 changelog.