Activity log for bug #900375

Date Who What changed Old value New value Message
2011-12-05 16:51:45 Elena Stepanova bug added bug
2011-12-05 16:51:56 Elena Stepanova maria: milestone 5.3
2011-12-05 16:52:55 Elena Stepanova maria: assignee Igor Babaev (igorb-seattle)
2011-12-05 18:35:15 Elena Stepanova description 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. 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 ) ; 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: igor@askmonty.org-20111130182253-ukyklybbf4yno2as 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 ) ;
2011-12-06 22:33:32 Igor Babaev maria: milestone 5.3 5.2
2011-12-06 22:33:39 Igor Babaev maria: importance Undecided Critical
2011-12-06 22:33:45 Igor Babaev maria: status New Confirmed
2011-12-06 22:34:18 Igor Babaev maria: assignee Igor Babaev (igorb-seattle) Timour Katchaounov (timour)
2011-12-07 21:10:31 Timour Katchaounov maria: status Confirmed In Progress
2011-12-12 10:42:25 Launchpad Janitor branch linked lp:maria/5.2
2011-12-12 10:52:53 Timour Katchaounov maria: status In Progress Fix Released