Details
-
Type:
Bug
-
Status: Closed
-
Priority:
Critical
-
Resolution: Fixed
-
Affects Version/s: None
-
Fix Version/s: None
-
Component/s: None
-
Labels:
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)
Gliffy Diagrams
Attachments
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
Re: Wrong result for a query with [NOT] IN subquery predicate if the left part of the predicate is explicit NULL
The causes of the problem ("fixed" part of the index) mentioned in the MySQL patch are correct.
But the patch based on http://lists.mysql.com/commits/142813 which subsitute unique subquery engine with index one.
Launchpad bug id: 1009187
Re: Wrong result for a query with [NOT] IN subquery predicate if the left part of the predicate is explicit NULL
MySQL patch does not help (maybe wrong merge, because there is a lot of changes). MySQL description of the problem looks like right.