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
Triage: Triaged: D2 (Serious)

[19 Apr 2010 9:32] Ole John Aske
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
[19 Apr 2010 9:55] Valeriy Kravchuk
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)
[27 Jan 2012 13:42] Jon Stephens
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
[27 Jan 2012 13:43] Jon Stephens
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.