Wrong result with derived_with_keys, correlated subquery
Affects | Status | Importance | Assigned to | Milestone | |
---|---|---|---|---|---|
MariaDB |
Fix Released
|
Undecided
|
Igor Babaev |
Bug Description
The following query:
SELECT * FROM t1 WHERE t1.b IN ( SELECT v2.a FROM v2 WHERE v2.b = t1.a );
returns no rows when executed with derived_with_keys and
a b
5 4
when executed otherwise.
explain:
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 2 Using where
2 DEPENDENT SUBQUERY <derived3> ref key0 key0 5 test.t1.a 2 Using where
3 DERIVED t2 ALL NULL NULL NULL NULL 2
minimal switch: derived_
full switch: index_merge=
bzr version-info
revision-id: <email address hidden>
date: 2011-10-13 11:23:59 +0200
build-date: 2011-10-13 14:15:52 +0300
revno: 3232
branch-nick: maria-5.3
test case:
CREATE TABLE t1 ( a int , b int ) ;
INSERT INTO t1 VALUES (5,4),(9,8);
CREATE TABLE t2 ( a int , b int ) ;
INSERT INTO t2 VALUES (4,5),(5,1);
CREATE ALGORITHM=TEMPTABLE VIEW v2 AS SELECT * FROM t2;
SET SESSION optimizer_
SELECT * FROM t1 WHERE t1.b IN ( SELECT v2.a FROM v2 WHERE v2.b = t1.a );
Changed in maria: | |
milestone: | none → 5.3 |
assignee: | nobody → Igor Babaev (igorb-seattle) |
Changed in maria: | |
status: | New → Fix Committed |
Changed in maria: | |
status: | Fix Committed → Fix Released |
This bug was fixed by the patch for bug #874006.