Wrong result with date/datetime and subquery with GROUP BY and in_to_exists

[Philip Stoev]

The following query:

SELECT * FROM t1 WHERE a IN ( SELECT a AS field1 FROM t1 GROUP BY field1 );

does not return rows that match the IN predicate (that is, all rows in the table). Same query without GROUP BY returns a correct result.

explain:

id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 index NULL a 4 NULL 2 Using where; Using index
2 DEPENDENT SUBQUERY t1 index NULL a 4 NULL 1 Using index

minimal optimizer switch: materialization=off,in_to_exists=on

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=off,derived_merge=off,derived_with_keys=off,firstmatch=off,loosescan=off,materialization=off,in_to_exists=on,semijoin=off,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=off,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

bzr version-info
revision-id: <email address hidden>
date: 2011-08-11 22:34:41 -0700
build-date: 2011-08-12 11:29:02 +0300
revno: 3151
branch-nick: maria-5.3

test case:

CREATE TABLE t1 (a date, KEY (a)) ;
INSERT INTO t1 VALUES ('2009-01-01'),('2009-02-02');

SET SESSION optimizer_switch='materialization=off,in_to_exists=on';

SELECT * FROM t1 WHERE a IN ( SELECT a AS field1 FROM t1 GROUP BY field1 );

Reproducible in maria-5.3. Not reproducible in maria-5.2, mysql-5.5 . Not reproducible with integers. Does not involve NULLSs or constant optimizations.