MySQL Bugs: #52935: MIN/MAX fails to evaluate HAVING condition, returns incorrect NULL result

Bug #52935	MIN/MAX fails to evaluate HAVING condition, returns incorrect NULL result
Submitted:	19 Apr 2010 9:32	Modified:	27 Jan 2012 13:42
Reporter:	Ole John Aske	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S2 (Serious)
Version:	5.0.89, 5.1.43, 5.1.44, 5.1.46	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
Evaluation of a HAVING condition seems to be skipped by the optimizer in
some cases. EXPLAIN'ing the failing query I get:

explain SELECT MIN(table1.i4), MIN(table2.pk) as min_pk
   FROM t1 as table1, t1 as table2
   WHERE table1.pk = 1
   HAVING min_pk <= 10;

+----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra                                               |
+----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+
|  1 | SIMPLE      | NULL  | NULL | NULL          | NULL | NULL    | NULL | NULL | Impossible WHERE noticed after reading const tables |
+----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+
1 row in set (0.00 sec)

The 'Impossible where' optimization seems to simply const replace min/max with NULL values without taking the optional HAVING condition into consideration.

NOTE1:
   As this bug is similar to both bug#51242 & bug#52051 I have
   cherry picked those fixes into my sandbox and also reproduced 
   this bug with these fixes.

How to repeat:
CREATE TABLE t1 (pk INT PRIMARY KEY, i4 INT);
INSERT INTO t1 VALUES (2,7), (4,7), (6,2), (17,0);

SELECT MIN(table1.i4), MIN(table2.pk) as min_pk
FROM t1 as table1, t1 as table2
WHERE table1.pk = 1;
+----------------+--------+
| MIN(table1.i4) | min_pk |
+----------------+--------+
|           NULL |   NULL |
+----------------+--------+
1 row in set (0.00 sec)

- Which is correct, then I add a HAVING condition to the same query:

SELECT MIN(table1.i4), MIN(table2.pk) as min_pk
FROM t1 as table1, t1 as table2
WHERE table1.pk = 1
HAVING min_pk <= 10;

+----------------+--------+
| MIN(table1.i4) | min_pk |
+----------------+--------+
|           NULL |   NULL |
+----------------+--------+
1 row in set (0.00 sec)

Which is incorrect as 'HAVING min_pk <= 10' should have eliminated this row

Thank you for the bug report. Verified just as described:

openxs@suse:/home2/openxs/dbs/5.1> bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.46-debug Source distribution

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> drop table t1;
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE TABLE t1 (pk INT PRIMARY KEY, i4 INT);
Query OK, 0 rows affected (0.03 sec)

mysql> INSERT INTO t1 VALUES (2,7), (4,7), (6,2), (17,0);
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> SELECT MIN(table1.i4), MIN(table2.pk) as min_pk
    -> FROM t1 as table1, t1 as table2
    -> WHERE table1.pk = 1;
+----------------+--------+
| MIN(table1.i4) | min_pk |
+----------------+--------+
|           NULL |   NULL |
+----------------+--------+
1 row in set (0.01 sec)

mysql> SELECT MIN(table1.i4), MIN(table2.pk) as min_pk FROM t1 as table1, t1 as table2 WHERE table1.pk = 1 HAVING min_pk <= 10;
+----------------+--------+
| MIN(table1.i4) | min_pk |
+----------------+--------+
|           NULL |   NULL |
+----------------+--------+
1 row in set (0.00 sec)

mysql> explain SELECT MIN(table1.i4), MIN(table2.pk) as min_pk FROM t1 as table1, t1 as table2 WHERE table1.pk = 1 HAVING min_pk <= 10;
+----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra                                               |
+----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+
|  1 | SIMPLE      | NULL  | NULL | NULL          | NULL | NULL    | NULL | NULL | Impossible WHERE noticed after reading const tables |
+----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+
1 row in set (0.00 sec)

Compare to these queries that return correct results:

mysql> explain SELECT MIN(table1.i4), MIN(table2.pk) as min_pk FROM t1 as table1, t1 as table2 WHERE table1.pk = 1 HAVING 0;
+----+-------------+-------+------+---------------+------+---------+------+------+-------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra             |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------------+
|  1 | SIMPLE      | NULL  | NULL | NULL          | NULL | NULL    | NULL | NULL | Impossible HAVING |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------------+
1 row in set (0.00 sec)

mysql> explain SELECT MIN(table1.i4), MIN(table2.pk) as min_pk FROM t1 as table1, t1 as table2 WHERE table1.pk = 2 HAVING min_pk <= 10;
+----+-------------+--------+-------+---------------+---------+---------+-------+------+-------------+
| id | select_type | table  | type  | possible_keys | key     | key_len | ref   | rows | Extra       |
+----+-------------+--------+-------+---------------+---------+---------+-------+------+-------------+
|  1 | SIMPLE      | table1 | const | PRIMARY       | PRIMARY | 4       | const |    1 |             |
|  1 | SIMPLE      | table2 | index | NULL          | PRIMARY | 4       | NULL  |    4 | Using index |
+----+-------------+--------+-------+---------------+---------+---------+-------+------+-------------+
2 rows in set (0.00 sec)

Thank you for your bug report. This issue has been committed to our source repository of that product and will be incorporated into the next release.

If necessary, you can access the source repository and build the latest available version, including the bug fix. More information about accessing the source trees is available at

    http://dev.mysql.com/doc/en/installing-source.html

Fixed in 5.6. Documented fix in the 5.6.5 changelog as follows:
 
      A HAVING clause in a query using MIN() or MAX() was sometimes ignored.

Closed.