Bug #46077 | wrong result: HAVING + ORDER BY + MyISAM + ICP returns extra rows | ||
---|---|---|---|
Submitted: | 9 Jul 2009 10:18 | Modified: | 14 Nov 2009 10:04 |
Reporter: | Philip Stoev | Email Updates: | |
Status: | Can't repeat | Impact on me: | |
Category: | MySQL Server: Optimizer | Severity: | S3 (Non-critical) |
Version: | 5.4 | OS: | Any |
Assigned to: | Philip Stoev | CPU Architecture: | Any |
Tags: | ICP, index_condition_pushdown, optimizer_switch |
[9 Jul 2009 10:18]
Philip Stoev
[8 Oct 2009 13:27]
Guilhem Bichot
cannot repeat with 6.0-codebase-bugfixing
[8 Oct 2009 14:36]
Guilhem Bichot
asked Philip to re-test
[8 Oct 2009 14:54]
Valeriy Kravchuk
Not repeatable for me on recent 5.1.40, trunk (5.4.5) and mysql-6.0-codebase (6.0.14): 77-52-242-160:6.0-codebase openxs$ bin/mysql -uroot test Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 Server version: 6.0.14-alpha-debug Source distribution Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> SET SESSION engine_condition_pushdown = 'ON'; Query OK, 0 rows affected (0.00 sec) mysql> mysql> CREATE TABLE `B` ( -> `pk` int(11) NOT NULL AUTO_INCREMENT, -> `int_key` int(11) NOT NULL, -> PRIMARY KEY (`pk`), -> KEY `int_key` (`int_key`) -> ) ENGINE=MyISAM AUTO_INCREMENT=3 DEFAULT CHARSET=latin1; Query OK, 0 rows affected (0.06 sec) mysql> mysql> INSERT INTO `B` VALUES (1,7),(2,9); Query OK, 2 rows affected (0.00 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> mysql> SELECT `pk`, `int_key` field1 -> FROM B WHERE `pk` < 3 -> HAVING field1 < 8 -> ORDER BY field1; +----+--------+ | pk | field1 | +----+--------+ | 1 | 7 | +----+--------+ 1 row in set (0.01 sec)
[9 Oct 2009 19:37]
Guilhem Bichot
this was fixed by epotemkin@mysql.com-20090903125059-z195j43fd6nvdws2 which was about Bug#45227 "Lost HAVING clause led to a wrong result." We see the present bug is about HAVING and extra rows, makes sense. I'll add a testcase for this bug.
[15 Oct 2009 9:00]
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/86904 3654 Guilhem Bichot 2009-10-15 Testcases for those "can't repeat" bugs: BUG#45928 "Differing query results depending on MRR and engine_condition_pushdown settings" BUG#46077 "wrong result: HAVING + ORDER BY + MyISAM + ICP returns extra rows" both fixed by epotemkin@mysql.com-20090903125059-z195j43fd6nvdws2 BUG#43578 "MyISAM&Maria gives wrong rows with range access ORDER BY DESC on date index" fixed by mattias.jonsson@sun.com-20090828115631-9zjv27kc3xmgvd5x
[31 Oct 2009 8:20]
Bugs System
Pushed into 6.0.14-alpha (revid:alik@sun.com-20091031081410-qkxmjsdzjmj840aq) (version source revid:guilhem@mysql.com-20091015085142-7zs7mouolfiy49fz) (merge vers: 6.0.14-alpha) (pib:13)
[14 Nov 2009 0:00]
Bugs System
No feedback was provided for this bug for over a month, so it is being suspended automatically. If you are able to provide the information that was originally requested, please do so and change the status of the bug back to "Open".
[14 May 2010 11:16]
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/108312 3174 Guilhem Bichot 2010-05-14 Backporting of guilhem@mysql.com-20091015085142-7zs7mouolfiy49fz : Testcases for those "can't repeat" bugs: BUG#45928 "Differing query results depending on MRR and engine_condition_pushdown settings" BUG#46077 "wrong result: HAVING + ORDER BY + MyISAM + ICP returns extra rows" both fixed by epotemkin@mysql.com-20090903125059-z195j43fd6nvdws2 BUG#43578 "MyISAM&Maria gives wrong rows with range access ORDER BY DESC on date index" fixed by mattias.jonsson@sun.com-20090828115631-9zjv27kc3xmgvd5x
[16 Aug 2010 6:39]
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:26]
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)