Wrong result for a query with [NOT] IN subquery predicate if the left part of the predicate is explicit NULL

Bug #1009187 reported by Igor Babaev
6
This bug affects 1 person
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://bugs.mysql.com/bug.php?id=58628)

Related branches

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
Revision history for this message
Oleksandr "Sanja" Byelkin (sanja-byelkin) wrote :

MySQL patch does not help (maybe wrong merge, because there is a lot of changes). MySQL description of the problem looks like right.

Revision history for this message
Oleksandr "Sanja" Byelkin (sanja-byelkin) wrote :

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.

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
To post a comment you must log in.
This report contains Public information  Edit
Everyone can see this information.

Other bug subscribers