Wrong result with in_to_exists, constant table , semijoin=OFF,materialization=OFF

[Philip Stoev]

The following query:

SELECT *
FROM t1
WHERE t1.a IN (
         SELECT t2.a
         FROM t2
         WHERE t2.a < 'ZZZ'
);

returns rows that do not match the WHERE predicate when executed with in_to_exists. Forcing in_to_exists requires disabling semijoin and materialization.

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
2 DEPENDENT SUBQUERY t2 system NULL NULL NULL NULL 1

minimal switch: semijoin=off,materialization=off,in_to_exists=on
full 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=off,derived_with_keys=off,firstmatch=on,loosescan=on,materialization=off,in_to_exists=on,semijoin=off,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=off,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on

repeatable in maria-5.3. not repeatable in maria-5.2, mysql-5.5

 bzr version-info
revision-id: <email address hidden>
date: 2011-11-24 15:12:10 +0200
build-date: 2011-11-24 16:14:10 +0200
revno: 3310
branch-nick: maria-5.3

test case:

CREATE TABLE t1 (a varchar(3));
INSERT INTO t1 VALUES ('AAA'),('BBB');

CREATE TABLE t2 (a varchar(3));
INSERT INTO t2 VALUES ('CCC');

SELECT *
FROM t1
WHERE t1.a IN (
         SELECT t2.a
         FROM t2
         WHERE t2.a < 'ZZZ'
);