[MDEV-2156] LP:1009187 - Wrong result for a query with [NOT] IN subquery predicate if the left part of the predicate is explicit NULL Created: 2012-06-05  Updated: 2015-02-02  Resolved: 2012-10-04

Status: Closed
Project: MariaDB Server
Component/s: None
Affects Version/s: None
Fix Version/s: None

Type: Bug Priority: Critical
Reporter: Igor Babaev Assignee: Timour Katchaounov (Inactive)
Resolution: Fixed Votes: 0
Labels: Launchpad

Attachments: XML File LPexportBug1009187.xml    

 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)

 Comments   
Comment by Oleksandr Byelkin [ 2012-06-29 ]

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.

Comment by Oleksandr Byelkin [ 2012-07-06 ]

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.

Comment by Rasmus Johansson (Inactive) [ 2012-09-18 ]

Launchpad bug id: 1009187

Generated at Thu Feb 08 06:39:57 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.