Details
-
Bug
-
Status: Closed (View Workflow)
-
Critical
-
Resolution: Fixed
-
None
-
None
-
None
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://bugs.mysql.com/bug.php?id=58628)