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:
None 
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version:5.4 OS:Any
Assigned to: Philip Stoev
Tags: ICP, index_condition_pushdown, optimizer_switch
Triage: Triaged: D2 (Serious)

[9 Jul 2009 10:18] Philip Stoev
Description:
When index condition pushdown is enabled, queries of the form

SELECT `pk`, `int_key` field1
FROM B WHERE  `pk`  <  3
HAVING  field1  <  8
ORDER  BY field1;

return a wrong result - extra rows that do not match the HAVING predicate.

How to repeat:
--disable_warnings
DROP TABLE IF EXISTS B;
--enable_warnings

SET SESSION engine_condition_pushdown = 'ON';

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;

INSERT INTO `B` VALUES (1,7),(2,9);

SELECT `pk`, `int_key` field1
FROM B WHERE  `pk`  <  3
HAVING  field1  <  8
ORDER  BY field1;
[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] Valerii 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)