MySQL Bugs: #46077: wrong result: HAVING + ORDER BY + MyISAM + ICP returns extra rows

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	CPU Architecture:	Any
Tags:	ICP, index_condition_pushdown, optimizer_switch

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;

cannot repeat with 6.0-codebase-bugfixing

asked Philip to re-test

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)

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.

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

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)

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".

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

Pushed into mysql-next-mr (revid:alik@sun.com-20100816062819-bluwgdq8q4xysmlg) (version source revid:alik@sun.com-20100816062612-enatdwnv809iw3s9) (pib:20)

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)