Wrong result for a query with [NOT] IN subquery predicate if the left part of the predicate is explicit NULL
Affects | Status | Importance | Assigned to | Milestone | |
---|---|---|---|---|---|
MariaDB |
Fix Released
|
Critical
|
Timour Katchaounov |
Bug Description
Let's create and populate tables t1 and subq with the following commands:
CREATE TABLE t1 (pk INT NOT NULL, i INT);
INSERT INTO t1 VALUES (0,NULL), (1,NULL), (2,NULL), (3,NULL);
CREATE TABLE t2 (pk INT NOT NULL, i INT NOT NULL, PRIMARY KEY(i,pk));
INSERT INTO t2 VALUES (0,0), (1,1), (2,2), (3,3);
Then the query
SELECT * FROM t1 WHERE NULL NOT IN (SELECT i FROM t2 WHERE t2.pk = t1.pk)
is expected to return an empty set.
However in MariaDB 5.1/5.2/5.3/5.5 we have:
MariaDB [test]> SELECT * FROM t1 WHERE NULL NOT IN (SELECT i FROM t2 WHERE t2.pk = t1.pk);
+----+------+
| pk | i |
+----+------+
| 1 | NULL |
| 2 | NULL |
| 3 | NULL |
+----+------+
We also have wrong results for the query
SELECT * FROM t1 WHERE NULL IN (SELECT i FROM t2 WHERE t2.pk = t1.pk) IS UNKNOWN:
MariaDB [test]> SELECT * FROM t1 WHERE NULL IN (SELECT i FROM t2 WHERE t2.pk = t1.pk) IS UNKNOWN;
+----+------+
| pk | i |
+----+------+
| 0 | NULL |
+----+------+
This bug supposedly is fixed mysql-5.6 (see http://
Changed in maria: | |
status: | New → Confirmed |
importance: | Undecided → Critical |
assignee: | nobody → Oleksandr "Sanja" Byelkin (sanja-byelkin) |
milestone: | none → 5.2 |
Changed in maria: | |
status: | Confirmed → In Progress |
Changed in maria: | |
assignee: | Oleksandr "Sanja" Byelkin (sanja-byelkin) → Timour Katchaounov (timour) |
Changed in maria: | |
status: | In Progress → Fix Committed |
Changed in maria: | |
status: | Fix Committed → Fix Released |
MySQL patch does not help (maybe wrong merge, because there is a lot of changes). MySQL description of the problem looks like right.