Details
Major Description
Hi, I found a bug in MariaDB 12.2.2. A query using `INTERSECT` between two contradictory `WHERE` clauses—one with `NOT EXISTS` and the other with `EXISTS` on identical correlated conditions—should logically yield an empty set. However, MariaDB 12.2.2 returns a row instead.
CREATE TABLE t0(c0 REAL UNSIGNED); |
CREATE TABLE t1(c0 REAL ZEROFILL UNIQUE); |
INSERT INTO t0 VALUES (1),(2); |
INSERT INTO t1 VALUES (1); |
-- expect "empty set", actually return 1
|
SELECT t0.c0 FROM t0 WHERE NOT EXISTS (SELECT 1 FROM t1 WHERE UNCOMPRESSED_LENGTH(t0.c0) AND t0.c0 = t1.c0) INTERSECT SELECT t0.c0 FROM t0 WHERE EXISTS (SELECT 1 FROM t1 WHERE UNCOMPRESSED_LENGTH(t0.c0) AND t0.c0 = t1.c0); |
+------+ |
| c0 |
|
+------+ |
| 1 |
|
+------+ |
SELECT t0.c0 FROM t0 WHERE NOT EXISTS (SELECT 1 FROM t1 WHERE UNCOMPRESSED_LENGTH(t0.c0) AND t0.c0 = t1.c0); |
+------+ |
| c0 |
|
+------+ |
| 1 |
|
| 2 |
|
+------+ |
SELECT t0.c0 FROM t0 WHERE EXISTS (SELECT 1 FROM t1 WHERE UNCOMPRESSED_LENGTH(t0.c0) AND t0.c0 = t1.c0); |
+------+ |
| c0 |
|
+------+ |
| 1 |
|
+------+ |