Wrong result with semijoin_with_cache=ON, join_cache_level>=3, ORDER BY
Affects | Status | Importance | Assigned to | Milestone | |
---|---|---|---|---|---|
MariaDB |
Fix Released
|
Critical
|
Igor Babaev |
Bug Description
The following query
SELECT * FROM t1, t2 alias
WHERE b IN (
SELECT d FROM t2
WHERE c <= alias.c )
ORDER BY a, d;
produces only one row with semijoin_
Reproducible with Aria and MyISAM, but not with InnoDB.
revno: 3324
revision-id: <email address hidden>
also reproducible on 3315.
MySQL 5.5.18 and MariaDB 5.2.9 with default settings return two rows (as expected).
Minimal optimizer_switch: semijoin_
Full optimizer_switch: index_merge=
EXPLAIN with semijoin_
EXPLAIN
SELECT * FROM t1, t2 alias
WHERE b IN (
SELECT d FROM t2
WHERE c <= alias.c )
ORDER BY a, d;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 system NULL NULL NULL NULL 1 Using filesort
1 PRIMARY alias ALL NULL NULL NULL NULL 2
1 PRIMARY t2 hash_ALL NULL #hash#$hj 5 const 2Using where; Start temporary; End temporary; Using join buffer (flat, BNLH join)
EXPLAIN with semijoin_
EXPLAIN
SELECT * FROM t1, t2 alias
WHERE b IN (
SELECT d FROM t2
WHERE c <= alias.c )
ORDER BY a, d;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 system NULL NULL NULL NULL 1 Using temporary; Using filesort
1 PRIMARY alias ALL NULL NULL NULL NULL 2
1 PRIMARY t2 ALL NULL NULL NULL NULL 2 Using where; Start temporary; End temporary
EXPLAIN with semijoin_
EXPLAIN
SELECT * FROM t1, t2 alias
WHERE b IN (
SELECT d FROM t2
WHERE c <= alias.c )
ORDER BY a, d;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 system NULL NULL NULL NULL 1 Using temporary; Using filesort
1 PRIMARY alias ALL NULL NULL NULL NULL 2
1 PRIMARY t2 ALL NULL NULL NULL NULL 2 Using where; Start temporary; End temporary; Using join buffer (flat, BNL join)
Test case:
SET join_cache_level = 3; # reproducible with >=3
SET optimizer_
CREATE TABLE t1 ( a INT, b INT ) ENGINE=Aria;
INSERT INTO t1 VALUES (8,10);
CREATE TABLE t2 ( c INT, d INT ) ENGINE=Aria;
INSERT INTO t2 VALUES (8,10);
INSERT INTO t2 VALUES (9,11);
SELECT * FROM t1, t2 alias
WHERE b IN (
SELECT d FROM t2
WHERE c <= alias.c )
ORDER BY a, d;
Changed in maria: | |
milestone: | none → 5.3 |
assignee: | nobody → Sergey Petrunia (sergefp) |
Changed in maria: | |
importance: | Undecided → Critical |
status: | New → Confirmed |
Changed in maria: | |
assignee: | Sergey Petrunia (sergefp) → Igor Babaev (igorb-seattle) |
Changed in maria: | |
status: | Confirmed → In Progress |
Changed in maria: | |
status: | In Progress → Fix Committed |
Changed in maria: | |
status: | Fix Committed → Fix Released |