Wrong result with firstmatch=off , ORDER BY , join_cache_level = 3, innodb
Affects | Status | Importance | Assigned to | Milestone | |
---|---|---|---|---|---|
MariaDB |
Fix Released
|
High
|
Igor Babaev |
Bug Description
The following query:
SELECT *
FROM t1
WHERE (t1.b) IN (
SELECT c
FROM t2
)
ORDER BY t1.a;
returns
a b
3888 20
when executed with firstmatch=off
and
a b
3888 20
3914 17
for all other execution plans.
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; Using filesort; Start temporary
1 PRIMARY t2 ref c c 5 test.t1.b 1 Using index; End temporary
minimal switch: semijoin=
full switch: index_merge=
bzr version-info:
revision-id: <email address hidden>
date: 2011-11-07 16:39:02 +0400
build-date: 2011-11-08 11:48:08 +0200
revno: 3273
branch-nick: maria-5.3
test case:
--source include/
CREATE TABLE t1 (a int NOT NULL , b int) ENGINE=InnoDB;
INSERT INTO t1 VALUES (3914,17)
CREATE TABLE t2 (c int, KEY (c)) ENGINE=InnoDB;
INSERT INTO t2 VALUES (17),(20);
SET SESSION join_cache_level=3;
SET SESSION optimizer_
SELECT *
FROM t1
WHERE (t1.b) IN (
SELECT c
FROM t2
)
ORDER BY t1.a;
Changed in maria: | |
milestone: | none → 5.3 |
assignee: | nobody → Sergey Petrunia (sergefp) |
Changed in maria: | |
importance: | Undecided → High |
Changed in maria: | |
status: | New → Confirmed |
Changed in maria: | |
assignee: | Sergey Petrunia (sergefp) → Igor Babaev (igorb-seattle) |
status: | Confirmed → In Progress |
Changed in maria: | |
status: | In Progress → Fix Committed |
Changed in maria: | |
status: | Fix Committed → Fix Released |