[MDEV-4996] Wrong result (missing rows) with impossible condition in NOT IN subquery Created: 2013-09-05  Updated: 2013-09-09  Resolved: 2013-09-09

Status: Closed
Project: MariaDB Server
Component/s: None
Affects Version/s: 10.0.4, 5.5.32, 5.3.12
Fix Version/s: 5.5.33a, 10.0.6, 5.3.13

Type: Bug Priority: Minor
Reporter: Elena Stepanova Assignee: Igor Babaev
Resolution: Fixed Votes: 0
Labels: None


 Description   

The first test case returns 1 row on 5.3, 5.5, 10.0, and two rows on 5.2, MySQL 5.5, MySQL 5.6. Two rows is the correct result.

CREATE TABLE t1 (a INT, c1 VARCHAR(1)) ENGINE=MyISAM;
 
INSERT INTO t1 VALUES (2,'x'),(8,'d');
 
 
 
CREATE TABLE t2 (m INT, n INT, c2 VARCHAR(1)) ENGINE=MyISAM;
 
INSERT INTO t2 VALUES (0, 5, 'x'),(1, 4,'p');
 
 
 
SELECT * FROM t1 WHERE c1 NOT IN ( 
  SELECT t2a.c2 FROM t2 AS t2a, t2 AS t2b, t2 AS t2c 
  WHERE t2c.c2 = t2b.c2 AND ( t2a.m = t2b.n OR 0 ) AND ( t2b.m != a OR t2b.m = t2a.m )
 
);

Actual result:

a	c1
 
8	d

Expected result:

a	c1
 
2	x
 
8	d

The second test case is a variation of the first one, only instead of the constant '0' condition we use IN subquery from a constant (empty) table. This test case returns the correct result on 5.3, but fails on 5.5.

CREATE TABLE t1 (a INT, c1 VARCHAR(1)) ENGINE=MyISAM;
 
INSERT INTO t1 VALUES (2,'x'),(8,'d');
 
 
 
CREATE TABLE t2 (m INT, n INT, c2 VARCHAR(1)) ENGINE=MyISAM;
 
INSERT INTO t2 VALUES (0, 5, 'x'),(1, 4,'p');
 
 
 
CREATE TABLE t3 (i INT) ENGINE=MyISAM;
 
 
 
SELECT * FROM t1 WHERE c1 NOT IN ( 
  SELECT t2a.c2 FROM t2 AS t2a, t2 AS t2b, t2 AS t2c 
  WHERE t2c.c2 = t2b.c2 AND ( t2a.m = t2b.n OR 1 IN ( SELECT i FROM t3 ) ) AND ( t2b.m != a OR t2b.m = t2a.m )
 
);

5.3 result:

a	c1
 
2	x
 
8	d

5.5 result:

a	c1
 
8	d
 

bzr version-info
 
revision-id: igor@askmonty.org-20130830040242-tns2ir3y9rht6n19
 
revno: 3687
 
branch-nick: 5.3
 

bzr version-info
 
revision-id: psergey@askmonty.org-20130903144107-csv06zecpkp27oj0
 
revno: 3876
 
branch-nick: 5.5



 Comments   
Comment by Igor Babaev [ 2013-09-09 ]

The fix for the bug was pushed into the 5.3 tree and merged into the 5.5 tree.

Generated at Thu Feb 08 07:00:51 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.