Wrong result with semijoin + IN + join_cache_level
Affects | Status | Importance | Assigned to | Milestone | |
---|---|---|---|---|---|
MariaDB |
Fix Released
|
Critical
|
Igor Babaev |
Bug Description
The following query:
SELECT * FROM t1, t2
WHERE t2.a IN (
SELECT b
FROM t3
WHERE t3.d <= t1.d
);
returns 1 row less when executed with semijoin, join_cache_level >= 3:
d a b
w 2 18:56:33
w 5 19:11:10
the correct result is:
d a b
w 2 18:56:33
q 2 18:56:33
w 5 19:11:10
EXPLAIN:
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 2
1 PRIMARY t3 ALL d NULL NULL NULL 5 Range checked for each record (index map: 0x2); Start temporary
1 PRIMARY t2 hash_ALL PRIMARY #hash#PRIMARY 4 test.t3.b 2 End temporary; Using join buffer (flat, BNLH join)
but the bug was also observed with this EXPLAIN:
d select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 2
1 PRIMARY t3 ALL col_varchar_key NULL NULL NULL 5 Range checked for each record (index map: 0x2); Start temporary
1 PRIMARY t2 eq_ref PRIMARY PRIMARY 4 test.t3.
The date column does not participate in the query, but its removal causes the bug to disappear. So it may be a record-size issue of some sort.
bzr version-info
revision-id: <email address hidden>
date: 2011-11-17 08:00:22 -0800
build-date: 2011-11-18 12:32:54 +0200
revno: 3291
branch-nick: maria-5.3
test case:
CREATE TABLE t1 ( d varchar(1)) engine=aria;
INSERT INTO t1 VALUES ('w'),('q');
CREATE TABLE t2 ( a int NOT NULL, b time, PRIMARY KEY (a)) engine=aria;
INSERT INTO t2 VALUES (2,'18:
CREATE TABLE t3 ( a int NOT NULL, b int, c int, d varchar(1), PRIMARY KEY (a), KEY (d,c)) engine=aria;
INSERT INTO t3 VALUES (25,158,
SET SESSION optimizer_
SET SESSION join_cache_level=3;
SELECT * FROM t1, t2
WHERE t2.a IN (
SELECT b
FROM t3
WHERE t3.d <= t1.d
);
Changed in maria: | |
assignee: | nobody → Sergey Petrunia (sergefp) |
milestone: | none → 5.3 |
Changed in maria: | |
status: | New → Confirmed |
importance: | Undecided → Critical |
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 |