Details
-
Bug
-
Status: Confirmed (View Workflow)
-
Major
-
Resolution: Unresolved
-
10.6, 12.1.2, 12.2.1
-
None
-
None
-
ubuntu 22.04
Description
When executing a query containing a `CASE` expression that evaluates a nested `NOT EXISTS` subquery inside a `WHERE` clause filtering with an `EXISTS` subquery, the result set incorrectly returns `NULL` for all rows. However, wrapping the same `CASE` expression with `COUNT()` unexpectedly returns 1 instead of 0, indicating a mismatch between row-level evaluation and aggregation. This inconsistency arises specifically when using `LIKE` comparisons with empty strings (''), suggesting improper handling of empty-string pattern matching in subquery correlation or aggregation contexts.
CREATE TABLE t0(c0 VARCHAR(10)); |
CREATE TABLE t1 LIKE t0; |
INSERT INTO t1 VALUES (''); |
INSERT INTO t0 VALUES (''),(' '); |
|
|
-- expect: 0; actual: 1
|
-- MySQL and PostgreSQL return 0
|
SELECT COUNT(CASE WHEN NOT EXISTS (SELECT 1 FROM t1 WHERE t0.c0 LIKE t1.c0) THEN 1 ELSE NULL END) FROM t0 WHERE EXISTS (SELECT 1 FROM t1 WHERE t0.c0 LIKE t1.c0); |
+--------------------------------------------------------------------------------------------+ |
| COUNT(CASE WHEN NOT EXISTS (SELECT 1 FROM t1 WHERE t0.c0 LIKE t1.c0) THEN 1 ELSE NULL END) | |
+--------------------------------------------------------------------------------------------+ |
| 1 |
|
+--------------------------------------------------------------------------------------------+ |
1 row in set (0.001 sec) |
|
|
-- this query's result proves that the above query's result is incorrect
|
SELECT CASE WHEN NOT EXISTS (SELECT 1 FROM t1 WHERE t0.c0 LIKE t1.c0) THEN 1 ELSE NULL END FROM t0 WHERE EXISTS (SELECT 1 FROM t1 WHERE t0.c0 LIKE t1.c0); |
+-------------------------------------------------------------------------------------+ |
| CASE WHEN NOT EXISTS (SELECT 1 FROM t1 WHERE t0.c0 LIKE t1.c0) THEN 1 ELSE NULL END | |
+-------------------------------------------------------------------------------------+ |
| NULL | |
| NULL | |
+-------------------------------------------------------------------------------------+ |
2 rows in set (0.000 sec) |