Details
-
Type:
Bug
-
Status: Closed
-
Priority:
Major
-
Resolution: Fixed
-
Affects Version/s: None
-
Fix Version/s: None
-
Component/s: None
-
Labels:
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
Gliffy Diagrams
Attachments
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
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.
Launchpad bug id: 823189
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
);