Wrong result with exists2in , correlated subquery
Affects | Status | Importance | Assigned to | Milestone | |
---|---|---|---|---|---|
MariaDB |
Fix Released
|
High
|
Oleksandr "Sanja" Byelkin |
Bug 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>
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'),(
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
);
Changed in maria: | |
milestone: | none → 5.3 |
assignee: | nobody → Oleksandr "Sanja" Byelkin (sanja-byelkin) |
Changed in maria: | |
importance: | Undecided → High |
status: | New → In Progress |
Changed in maria: | |
status: | In Progress → Fix Committed |
Changed in maria: | |
status: | Fix Committed → Fix Released |