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 Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: DML Severity:S1 (Critical)
Version:8.0 OS:Any
Assigned to: 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.