The following query:

SELECT * FROM t2 WHERE (a, b) IN (
SELECT a , b FROM t1
WHERE a IS NOT NULL
GROUP BY a , b
) ;

returns only 1 row whereas it has to return the full set of 20 rows. Repeatable in maria-5.3 with both in_to_exists and materialization. Repeatable in mysql-5.5. See also previous GROUP BY bugs filed on LP.

Explain:

MariaDB [test]> explain SELECT * FROM t2 WHERE (a, b) IN ( SELECT a , b FROM t1 WHERE a IS NOT NULL GROUP BY a , b );
-------------------------------------------------------------------------------------------------------------------------+

-------------------------------------------------------------------------------------------------------------------------+

-------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

test case:

DROP TABLE IF EXISTS t1;
CREATE TABLE t1 ( a integer NOT NULL, PRIMARY KEY (a)) ENGINE=MyISAM;
INSERT INTO t1 VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13),(14),(15),(16),(17),(18),(19),(20);

DROP TABLE IF EXISTS t2;
CREATE TABLE t2 ( a integer, b integer );
INSERT INTO t2 VALUES (1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8),(9,9),(10,10),(11,11),(12,12),(13,13),(14,14),(15,15),(16,16),(17,17),(18,18),(19,19),(20,20);
SELECT * FROM t2 WHERE (a, b) IN (
SELECT a , b FROM t1
WHERE a IS NOT NULL
GROUP BY a , b
) ;

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=on,in_to_exists=off,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-09-10 18:01:27 +0300
build-date: 2011-09-14 10:06:03 +0300
revno: 3183
branch-nick: maria-5.3