Details
-
Bug
-
Status: Closed (View Workflow)
-
Critical
-
Resolution: Fixed
-
12.1.2
-
None
-
Not for Release Notes
Description
MariaDB's `derived_merge` optimizer feature incorrectly evaluates HAVING clauses containing complex boolean expressions when merging derived tables. This causes rows that should be filtered out by the HAVING clause to be incorrectly retained in the result set.
How to reproduce:
CREATE TABLE t1 (c1 NUMERIC); |
INSERT INTO t1(c1) VALUES (54559273), (-70416), (-9762545), (1), (1); |
|
|
SELECT MAX(ca2) |
FROM (SELECT c1 AS ca1, c1 AS ca2 FROM t1) AS ta1 |
GROUP BY ca1 |
HAVING (COUNT(ca2) NOT LIKE (ca1)) IS NOT TRUE; |
+----------+ |
| MAX(ca2) | |
+----------+ |
| -9762545 |
|
| -70416 |
|
| 54559273 |
|
+----------+ |
3 rows in set |
|
|
SET optimizer_switch='derived_merge=off'; |
SELECT MAX(ca2) |
FROM (SELECT c1 AS ca1, c1 AS ca2 FROM t1) AS ta1 |
GROUP BY ca1 |
HAVING (COUNT(ca2) NOT LIKE (ca1)) IS NOT TRUE; |
Empty set (0.001 sec) |
|