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;
