Wrong result with derived_merge=ON, DISTINCT or GROUP BY, EXISTS

[Elena Stepanova]

The following query

SELECT alias1.*
FROM t1, ( SELECT * FROM t1 ) AS alias1
WHERE EXISTS ( SELECT DISTINCT b FROM t1 WHERE b <= alias1.a )

with 10 rows in the table, each of which meets the WHERE condition, with derived_merge=ON returns 9 rows instead of 100.
If I remove at least one row from the table, the query starts producing the full resultset.
With derived_merge=OFF the query returns 100 rows as expected.

Same happens with GROUP BY instead of DISTINCT.

revno: 3324
revision-id: <email address hidden>

Reproducible on mariadb 5.2.9 (with default optimizer_switch) and on mysql 5.5.18.

EXPLAIN with derived_merge=on:

EXPLAIN SELECT alias1.* FROM t1, ( SELECT * FROM t1 ) AS alias1 WHERE EXISTS ( SELECT DISTINCT b FROM t1 WHERE b <= alias1.a ) ;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 index NULL b 5 NULL 10 Using index
1 PRIMARY t1 ALL NULL NULL NULL NULL 10 Using where; Using join buffer (flat, BNL join)
3 DEPENDENT SUBQUERY t1 range b b 5 NULL 6Using where; Using index for group-by; Using temporary

EXPLAIN with derived_merge=off:

EXPLAIN SELECT alias1.* FROM t1, ( SELECT * FROM t1 ) AS alias1 WHERE EXISTS ( SELECT DISTINCT b FROM t1 WHERE b <= alias1.a ) ;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 index NULL b 5 NULL 10 Using index
1 PRIMARY <derived2> ALL NULL NULL NULL NULL 10 Using where; Using join buffer (flat, BNL join)
3 DEPENDENT SUBQUERY t1 range b b 5 NULL 6Range checked for each record (index map: 0x1); Using temporary
2 DERIVED t1 ALL NULL NULL NULL NULL 10

Minimal optimizer_switch: none (default)
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=off,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on

Test case:

CREATE TABLE t1 ( a INT, b INT, KEY (b) );

INSERT INTO t1 VALUES
(100,10),(101,11),(102,12),(103,13),(104,14),
(105,15),(106,16),(107,17),(108,18),(109,19);

SELECT alias1.*
FROM t1, ( SELECT * FROM t1 ) AS alias1
WHERE EXISTS ( SELECT DISTINCT b FROM t1 WHERE b <= alias1.a ) ;

[Elena Stepanova (elenst)]

While saying that the issue is reprodubile on mariadb 5.2 and mysql 5.5.18, I just mean that they produce the same wrong resultset.

mariadb 5.2.9:

EXPLAIN SELECT alias1.*
FROM t1, ( SELECT * FROM t1 ) AS alias1
WHERE EXISTS ( SELECT DISTINCT b FROM t1 WHERE b <= alias1.a ) ;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 index NULL b 5 NULL 10 Using index
1 PRIMARY <derived2> ALL NULL NULL NULL NULL 10 Using where; Using join buffer
3 DEPENDENT SUBQUERY t1 range b b 5 NULL 6Using where; Using index for group-by; Using temporary
2 DERIVED t1 ALL NULL NULL NULL NULL 10

mysql 5.5.18:

EXPLAIN SELECT alias1.*
FROM t1, ( SELECT * FROM t1 ) AS alias1
WHERE EXISTS ( SELECT DISTINCT b FROM t1 WHERE b <= alias1.a ) ;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 index NULL b 5 NULL 10 Using index
1 PRIMARY <derived2> ALL NULL NULL NULL NULL 10 Using where; Using join buffer
3 DEPENDENT SUBQUERY t1 range b b 5 NULL 6Using where; Using index for group-by; Using temporary
2 DERIVED t1 ALL NULL NULL NULL NULL 10

[Timour Katchaounov (timour)]

Pushed into 5.2.