Wrong result with semijoin + IN + join_cache_level

[Philip Stoev]

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.col_int_nokey 1 End temporary; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan

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:56:33'),(5,'19:11:10');

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,10,'f'),(26,5,2,'v'),(27,163,103,'f'),(28,2,3,'q'),(29,8,6,'y');

SET SESSION optimizer_switch='semijoin=on,join_cache_hashed=on';
SET SESSION join_cache_level=3;

SELECT * FROM t1, t2
WHERE t2.a IN (
        SELECT b
        FROM t3
        WHERE t3.d <= t1.d
);