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_with_cache=ON and join_cache_level>=3 vs two rows with semijoin_with_cache=OFF or join_cache_level<3. The expected result for the test data is two rows.

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_with_cache=on (included into the test case).

Full optimizer_switch: index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=off,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on

EXPLAIN with semijoin_with_cache=ON, join_cache_level=3 (wrong resultset):

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_with_cache=OFF, join_cache_level=3 (correct resultset):

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_with_cache=ON, join_cache_level=1 (correct resultset):

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_switch='semijoin_with_cache=on';

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;