Details
-
Bug
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
10.11, 11.4, 11.8, 12.3, 13.1, 12.3.3
-
MariaDB 12.3.3
Default configuration
-
Unexpected results
-
A correlated EXISTS predicate could return an incorrect result for VARCHAR comparison combined with a LIKE condition.
Description
A correlated EXISTS query returns an incorrect result compared with an
equivalent INNER JOIN using the same predicate.
Test case:
CREATE TABLE l(x VARCHAR(1));
INSERT INTO l VALUES (' '), ('');
SELECT 'INNER_JOIN' AS q, COUNT
AS n
FROM l
JOIN (SELECT '0' AS x) r
ON l.x < r.x AND l.x LIKE '';
SELECT 'EXISTS' AS q, COUNT
AS n
FROM l
WHERE EXISTS (
SELECT 1
FROM (SELECT '0' AS x) r
WHERE l.x < r.x AND l.x LIKE ''
);
Actual result:
-------------+
| q | n |
-------------+
| INNER_JOIN | 1 |
-------------+
---------+
| q | n |
---------+
| EXISTS | 0 |
---------+
Expected result:
The EXISTS query should also return:
---------+
| q | n |
---------+
| EXISTS | 1 |
---------+
Reason:
The derived table on the right side contains exactly one row:
('0')
Both queries use the same matching condition:
l.x < r.x AND l.x LIKE ''
The INNER JOIN finds one matching row.
Since the right-hand side contains only one row, the corresponding EXISTS
condition should be TRUE for the same left-hand row. Therefore, counting
the rows selected by EXISTS should also produce 1.
However, MariaDB 12.3.3 returns 0 for the EXISTS form while the INNER JOIN
form returns 1.
I have not determined the root cause. The issue appears to involve the
evaluation or optimization of the correlated EXISTS predicate.
Thanks.