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 on 2012-06-05
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

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

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