|
==2176== 216 bytes in 1 blocks are definitely lost in loss record 2 of 3
|
==2176== at 0x4C28F9F: malloc (vg_replace_malloc.c:236)
|
==2176== by 0xCDB465: my_malloc (my_malloc.c:41)
|
==2176== by 0x7CAD24: filesort(THD*, TABLE*, st_sort_field*, unsigned int, SQL_SELECT*, unsigned long long, bool, unsigned long long*) (filesort.cc:211)
|
==2176== by 0x66FAD1: create_sort_index(THD*, JOIN*, st_order*, unsigned long long, unsigned long long, bool) (sql_select.cc:19020)
|
==2176== by 0x6485B6: JOIN::exec() (sql_select.cc:2803)
|
==2176== by 0x86BD33: subselect_single_select_engine::exec() (item_subselect.cc:3205)
|
==2176== by 0x8648B5: Item_subselect::exec() (item_subselect.cc:644)
|
==2176== by 0x864DCF: Item_in_subselect::exec() (item_subselect.cc:817)
|
==2176== by 0x8671F6: Item_in_subselect::val_bool() (item_subselect.cc:1638)
|
==2176== by 0x575060: Item::val_bool_result() (item.h:963)
|
==2176== by 0x803D34: Item_in_optimizer::val_int() (item_cmpfunc.cc:1736)
|
==2176== by 0x829A70: eval_const_cond(Item*) (item_func.cc:80)
|
==2176== by 0x661A73: internal_remove_eq_conds(THD*, Item*, Item::cond_result*) (sql_select.cc:13442)
|
==2176== by 0x661DB7: remove_eq_conds(THD*, Item*, Item::cond_result*) (sql_select.cc:13534)
|
==2176== by 0x661440: optimize_cond(JOIN*, Item*, List<TABLE_LIST>*, Item::cond_result*, COND_EQUAL**) (sql_select.cc:13300)
|
==2176== by 0x641C8B: JOIN::optimize() (sql_select.cc:1067)
|
branch: maria/5.5
|
revision-id: sergii@pisem.net-20121127112615-l2mciflv2imoikhy
|
date: 2012-11-27 12:26:15 +0100
|
revno: 3587
|
Also reproducible on all maria/5.5 versions down to 5.5.21 at least.
Could not reproduce on maria/5.3, on current mysql/5.5 or mysql/5.6.
Reproducible with the default optimizer_switch as well as with all OFF values except for in_to_exists or materialization (one of which is required to execute the query).
Default optimizer_switch:
index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=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=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=off
|
EXPLAIN with the default optimizer_switch:
id select_type table type possible_keys key key_len ref rows filtered Extra
|
1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00
|
2 SUBQUERY t2 system NULL NULL NULL NULL 1 100.00 Using filesort
|
2 SUBQUERY t1 ALL NULL NULL NULL NULL 2 100.00
|
Warnings:
|
Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where 1
|
Test case:
SET optimizer_switch = 'in_to_exists=on';
|
|
CREATE TABLE t1 (a INT) ENGINE=MyISAM;
|
INSERT INTO t1 VALUES (1),(9);
|
|
CREATE TABLE t2 (b INT) ENGINE=MyISAM;
|
INSERT INTO t2 VALUES (8);
|
|
SELECT * FROM t1
|
WHERE ( 1, 1 ) IN (
|
SELECT a, SUM( DISTINCT a )
|
FROM t1, t2
|
GROUP BY a
|
);
|
|