| 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: | |
| 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
[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.
