[MDEV-2984] LP:884657 - Wrong result with exists2in , correlated subquery Created: 2011-11-01  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: Oleksandr Byelkin
Resolution: Fixed Votes: 0
Labels: Launchpad

Attachments: XML File LPexportBug884657.xml    

 Description   

This query:

SELECT *
FROM t1, t2
WHERE EXISTS (
SELECT a
FROM t3
WHERE t3.b = t1.a
AND t3.b <> t2.b
);
returns no rows in exists2in , even though the correct result is:

----------+

a b

----------+

c v
c v
c x
c i
c e
c p
c s
c j
c z
c a
c q
c y
c r
c v
c r

----------+

explain with exists2in:

id select_type table type possible_keys key key_len ref rows filtered Extra

------------------------------------------------------------------------------

1 PRIMARY t1 system NULL NULL NULL NULL 1 100.00  
1 PRIMARY t2 ALL NULL NULL NULL NULL 20 100.00  
2 DEPENDENT SUBQUERY t3 system NULL NULL NULL NULL 1 100.00  
Note 1276 Field or reference 'test.t1.a' of SELECT #2 was resolved in SELECT #1
Note 1276 Field or reference 'test.t2.b' of SELECT #2 was resolved in SELECT #1
Note 1003 select 'c' AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t1` join `test`.`t2` where <expr_cache><'c',`test`.`t2`.`b`>(<in_optimizer>('c',<exists>(select 'c' from `test`.`t3` where (('c' <> `test`.`t2`.`b`) and (<cache>('c') = 'c')))))

bzr version-info:

revision-id: <email address hidden>
date: 2011-10-31 13:44:24 +0200
build-date: 2011-11-01 11:23:44 +0200
revno: 3256
branch-nick: maria-5.3-exists2in

test case:

CREATE TABLE t1 ( a varchar(1)) ;
INSERT INTO t1 VALUES ('c');

CREATE TABLE t2 ( b varchar(1)) ;
INSERT INTO t2 VALUES ('v'),('v'),('c'),(NULL),('x'),('i'),('e'),('p'),('s'),('j'),('z'),('c'),('a'),('q'),('y'),(NULL),('r'),('v'),(NULL),('r');

CREATE TABLE t3 ( a int NOT NULL , b varchar(1)) ;
INSERT INTO t3 VALUES (29,'c');

SELECT *
FROM t1, t2
WHERE EXISTS (
SELECT a
FROM t3
WHERE t3.b = t1.a
AND t3.b <> t2.b
);

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

Launchpad bug id: 884657

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