Wrong result with exists2in , correlated subquery

[Philip Stoev]

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
);