[MDEV-2702] LP:823189 - Wrong result with NOT IN + RIGHT JOIN + views Created: 2011-08-09  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: Major
Reporter: Philip Stoev (Inactive) Assignee: Igor Babaev
Resolution: Fixed Votes: 0
Labels: Launchpad

Attachments: XML File LPexportBug823189.xml    

 Description   

The following query:

SELECT * FROM v1, t2
WHERE t2.a NOT IN (
        SELECT t3.b
        FROM t3
        RIGHT JOIN t4 ON ( t4.d = t3.d )
        WHERE t4.d >= v1.d
);

returns 2 rows even though the same query using base table instead of a view v1 returns no rows. PostgreSQL also returns no rows in both cases.

test case:

CREATE TABLE t1 ( d varchar(32) , e int );
INSERT INTO t1 VALUES ('y',0),('w',0);

CREATE TABLE t2 ( a int , b int , c int , d varchar(1), e varchar(1));
INSERT INTO t2 VALUES (10,8,7,'b','b');

CREATE TABLE t3 ( a int , b int , c int , d varchar(1), e varchar(1));

CREATE TABLE t4 ( d varchar(32) , e int );
INSERT INTO t4 VALUES ('y',0),('w',0);

CREATE VIEW v1 AS SELECT * FROM t1;

SELECT * FROM v1, t2
WHERE t2.a NOT IN (
        SELECT t3.b
        FROM t3
        RIGHT JOIN t4 ON ( t4.d = t3.d )
        WHERE t4.d >= v1.d
);

Repeatable in maria-5.3, maria-5.2, mysql-5.5

 Comments   
Comment by Philip Stoev (Inactive) [ 2011-08-09 ]

Wrong result with NOT IN + RIGHT JOIN + views
Repeatable in maria-5.3, maria-5.2, mysql-5.5

The following query:

SELECT * FROM v1, t2
WHERE t2.a NOT IN (
SELECT t3.b
FROM t3
RIGHT JOIN t4 ON ( t4.d = t3.d )
WHERE t4.d >= v1.d
);

returns 2 rows even though the same query using base table instead of a view v1 returns no rows. PostgreSQL also returns no rows in both cases.

test case:

CREATE TABLE t1 ( d varchar(32) , e int );
INSERT INTO t1 VALUES ('y',0),('w',0);

CREATE TABLE t2 ( a int , b int , c int , d varchar(1), e varchar(1));
INSERT INTO t2 VALUES (10,8,7,'b','b');

CREATE TABLE t3 ( a int , b int , c int , d varchar(1), e varchar(1));

CREATE TABLE t4 ( d varchar(32) , e int );
INSERT INTO t4 VALUES ('y',0),('w',0);

CREATE VIEW v1 AS SELECT * FROM t1;

SELECT * FROM v1, t2
WHERE t2.a NOT IN (
SELECT t3.b
FROM t3
RIGHT JOIN t4 ON ( t4.d = t3.d )
WHERE t4.d >= v1.d
);

Comment by Igor Babaev [ 2011-08-12 ]

Re: Wrong result with NOT IN + RIGHT JOIN + views
A fix for the bug was pushed into the 5.3 tree.
Probably it makes sense to downport the fix into 5.2 as well.

Comment by Rasmus Johansson (Inactive) [ 2011-12-13 ]

Launchpad bug id: 823189

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