Bug #93214 LIMIT is applied before HAVING when you have a subquery

• Submitted: 15 Nov 2018 19:15
• Modified: 17 Apr 2019 23:18
• Reporter: Stephen Sigwart
• Status: Closed
• Category: MySQL Server: DML
• Severity: S1 (Critical)
• Version: 8.0
• OS: Any
• CPU Architecture: Any

[15 Nov 2018 19:15] Stephen Sigwart

Description:
The LIMIT is being applied before HAVING in the following.  Both SELECT queries should return 1 row.

mysql> CREATE TABLE test (id INT PRIMARY KEY, value INT);
Query OK, 0 rows affected (0.15 sec)

mysql> INSERT INTO test VALUES (1, 99), (2,98), (3, 97);
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> SELECT t1.id, (SELECT t2.value FROM test t2 WHERE t1.id = t2.id) AS sub_value FROM test t1 HAVING sub_value = 99 ORDER BY value LIMIT 1;
Empty set (0.00 sec)

mysql> SELECT t1.id, (SELECT t2.value FROM test t2 WHERE t1.id = t2.id) AS sub_value FROM test t1 HAVING sub_value = 99 ORDER BY value LIMIT 3;
+----+-----------+
| id | sub_value |
+----+-----------+
|  1 |        99 |
+----+-----------+
1 row in set (0.00 sec)

The subquery on the same table is pointless here, but the bug is also present when joining on other tables.

How to repeat:
On MySQL 8.0, run:

CREATE TABLE test (id INT PRIMARY KEY, value INT);
INSERT INTO test VALUES (1, 99), (2,98), (3, 97);
SELECT t1.id, (SELECT t2.value FROM test t2 WHERE t1.id = t2.id) AS sub_value FROM test t1 HAVING sub_value = 99 ORDER BY value LIMIT 1;

[16 Nov 2018 7:15] MySQL Verification Team

Thank you for the bug report.

[19 Nov 2018 12:04] Roy Lyseng

Posted by developer:
 
Regression from Bug#25466100: Refactor const_item processing

[17 Apr 2019 23:18] Jon Stephens

Documented fix in the MySQL 8.0.17 changelog, as follows:

    LIMIT was applied before HAVING in queries with subselects.

Closed.