|
Warning: 1104 bytes lost, allocated at mysys/my_alloc.c:217, sql/sql_list.h:44, heap/ha_heap.cc:65, sql/handler.cc:255, sql/sql_select.cc:14414, sql/opt_subselect.cc:5010, sql/opt_subselect.cc:5183, sql/sql_select.cc:1059
|
Warning: 8 bytes lost, allocated at sql/sql_plugin.cc:943, sql/sql_plugin.cc:990, sql/handler.cc:190, sql/sql_select.cc:14412, sql/opt_subselect.cc:5010, sql/opt_subselect.cc:5183, sql/sql_select.cc:1059, sql/sql_select.cc:3006
|
Warning: 984 bytes lost, allocated at mysys/my_alloc.c:217, sql/thr_malloc.cc:76, sql/field.h:156, sql/sql_select.cc:13686, sql/sql_select.cc:13947, sql/sql_select.cc:14304, sql/opt_subselect.cc:5010, sql/opt_subselect.cc:5183
|
Warning: 4008 bytes lost, allocated at mysys/my_alloc.c:217, mysys/my_alloc.c:281, sql/sql_select.cc:14116, sql/opt_subselect.cc:5010, sql/opt_subselect.cc:5183, sql/sql_select.cc:1059, sql/sql_select.cc:3006, sql/sql_select.cc:312
|
Memory lost: 6104 bytes in 4 chunks
|
==4850== 108 bytes in 1 blocks are still reachable in loss record 2 of 6
|
==4850== at 0x4C28F9F: malloc (vg_replace_malloc.c:236)
|
==4850== by 0xCBC38E: sf_malloc (safemalloc.c:105)
|
==4850== by 0xCA86EF: my_malloc (my_malloc.c:41)
|
==4850== by 0x61C7CB: intern_plugin_lock(LEX*, st_plugin_int**) (sql_plugin.cc:943)
|
==4850== by 0x61C93F: plugin_lock(THD*, st_plugin_int**) (sql_plugin.cc:990)
|
==4850== by 0x7BD572: ha_lock_engine(THD*, handlerton const*) (handler.cc:190)
|
==4850== by 0x65D912: create_tmp_table(THD*, TMP_TABLE_PARAM*, List<Item>&, st_order*, bool, bool, unsigned long long, unsigned long long, char const*, bool) (sql_select.cc:14412)
|
==4850== by 0x74FF19: create_dummy_tmp_table(THD*) (opt_subselect.cc:5010)
|
==4850== by 0x75065D: setup_jtbm_semi_joins(JOIN*, List<TABLE_LIST>*, Item**) (opt_subselect.cc:5183)
|
==4850== by 0x63B715: JOIN::optimize() (sql_select.cc:1059)
|
==4850== by 0x642824: mysql_select(THD*, Item***, TABLE_LIST*, unsigned int, List<Item>&, Item*, unsigned int, st_order*, st_order*, Item*, st_order*, unsigned long long, select_result*, st_select_lex_unit*, st_select_lex*) (sql_select.cc:3006)
|
==4850== by 0x639568: handle_select(THD*, LEX*, select_result*, unsigned long) (sql_select.cc:312)
|
==4850== by 0x612867: execute_sqlcom_select(THD*, TABLE_LIST*) (sql_parse.cc:4616)
|
==4850== by 0x60B3AC: mysql_execute_command(THD*) (sql_parse.cc:2184)
|
==4850== by 0x6150CE: mysql_parse(THD*, char*, unsigned int, Parser_state*) (sql_parse.cc:5731)
|
==4850== by 0x608972: dispatch_command(enum_server_command, THD*, char*, unsigned int) (sql_parse.cc:1055)
|
==4850==
|
==4850== 1,084 bytes in 1 blocks are still reachable in loss record 4 of 6
|
==4850== at 0x4C28F9F: malloc (vg_replace_malloc.c:236)
|
==4850== by 0xCBC38E: sf_malloc (safemalloc.c:105)
|
==4850== by 0xCA86EF: my_malloc (my_malloc.c:41)
|
==4850== by 0xC9C46D: alloc_root (my_alloc.c:217)
|
==4850== by 0x6DBCB0: sql_alloc(unsigned long) (thr_malloc.cc:75)
|
==4850== by 0x5BD9C9: Field::operator new(unsigned long) (field.h:156)
|
==4850== by 0x65B6D5: create_tmp_field_from_item(THD*, Item*, TABLE*, Item***, bool, unsigned int) (sql_select.cc:13686)
|
==4850== by 0x65C05D: create_tmp_field(THD*, TABLE*, Item*, Item::Type, Item***, Field**, Field**, bool, bool, bool, bool, unsigned int) (sql_select.cc:13947)
|
==4850== by 0x65D3CB: create_tmp_table(THD*, TMP_TABLE_PARAM*, List<Item>&, st_order*, bool, bool, unsigned long long, unsigned long long, char const*, bool) (sql_select.cc:14304)
|
==4850== by 0x74FF19: create_dummy_tmp_table(THD*) (opt_subselect.cc:5010)
|
==4850== by 0x75065D: setup_jtbm_semi_joins(JOIN*, List<TABLE_LIST>*, Item**) (opt_subselect.cc:5183)
|
==4850== by 0x63B715: JOIN::optimize() (sql_select.cc:1059)
|
==4850== by 0x642824: mysql_select(THD*, Item***, TABLE_LIST*, unsigned int, List<Item>&, Item*, unsigned int, st_order*, st_order*, Item*, st_order*, unsigned long long, select_result*, st_select_lex_unit*, st_select_lex*) (sql_select.cc:3006)
|
==4850== by 0x639568: handle_select(THD*, LEX*, select_result*, unsigned long) (sql_select.cc:312)
|
==4850== by 0x612867: execute_sqlcom_select(THD*, TABLE_LIST*) (sql_parse.cc:4616)
|
==4850== by 0x60B3AC: mysql_execute_command(THD*) (sql_parse.cc:2184)
|
==4850==
|
==4850== 1,204 bytes in 1 blocks are still reachable in loss record 5 of 6
|
==4850== at 0x4C28F9F: malloc (vg_replace_malloc.c:236)
|
==4850== by 0xCBC38E: sf_malloc (safemalloc.c:105)
|
==4850== by 0xCA86EF: my_malloc (my_malloc.c:41)
|
==4850== by 0xC9C46D: alloc_root (my_alloc.c:217)
|
==4850== by 0x566AC2: Sql_alloc::operator new(unsigned long, st_mem_root*) (sql_list.h:44)
|
==4850== by 0xBDCB23: heap_create_handler(handlerton*, TABLE_SHARE*, st_mem_root*) (ha_heap.cc:65)
|
==4850== by 0x7BD78E: get_new_handler(TABLE_SHARE*, st_mem_root*, handlerton*) (handler.cc:255)
|
==4850== by 0x65D952: create_tmp_table(THD*, TMP_TABLE_PARAM*, List<Item>&, st_order*, bool, bool, unsigned long long, unsigned long long, char const*, bool) (sql_select.cc:14414)
|
==4850== by 0x74FF19: create_dummy_tmp_table(THD*) (opt_subselect.cc:5010)
|
==4850== by 0x75065D: setup_jtbm_semi_joins(JOIN*, List<TABLE_LIST>*, Item**) (opt_subselect.cc:5183)
|
==4850== by 0x63B715: JOIN::optimize() (sql_select.cc:1059)
|
==4850== by 0x642824: mysql_select(THD*, Item***, TABLE_LIST*, unsigned int, List<Item>&, Item*, unsigned int, st_order*, st_order*, Item*, st_order*, unsigned long long, select_result*, st_select_lex_unit*, st_select_lex*) (sql_select.cc:3006)
|
==4850== by 0x639568: handle_select(THD*, LEX*, select_result*, unsigned long) (sql_select.cc:312)
|
==4850== by 0x612867: execute_sqlcom_select(THD*, TABLE_LIST*) (sql_parse.cc:4616)
|
==4850== by 0x60B3AC: mysql_execute_command(THD*) (sql_parse.cc:2184)
|
==4850== by 0x6150CE: mysql_parse(THD*, char*, unsigned int, Parser_state*) (sql_parse.cc:5731)
|
==4850==
|
==4850== 4,108 bytes in 1 blocks are still reachable in loss record 6 of 6
|
==4850== at 0x4C28F9F: malloc (vg_replace_malloc.c:236)
|
==4850== by 0xCBC38E: sf_malloc (safemalloc.c:105)
|
==4850== by 0xCA86EF: my_malloc (my_malloc.c:41)
|
==4850== by 0xC9C46D: alloc_root (my_alloc.c:217)
|
==4850== by 0xC9C71A: multi_alloc_root (my_alloc.c:281)
|
==4850== by 0x65C847: create_tmp_table(THD*, TMP_TABLE_PARAM*, List<Item>&, st_order*, bool, bool, unsigned long long, unsigned long long, char const*, bool) (sql_select.cc:14133)
|
==4850== by 0x74FF19: create_dummy_tmp_table(THD*) (opt_subselect.cc:5010)
|
==4850== by 0x75065D: setup_jtbm_semi_joins(JOIN*, List<TABLE_LIST>*, Item**) (opt_subselect.cc:5183)
|
==4850== by 0x63B715: JOIN::optimize() (sql_select.cc:1059)
|
==4850== by 0x642824: mysql_select(THD*, Item***, TABLE_LIST*, unsigned int, List<Item>&, Item*, unsigned int, st_order*, st_order*, Item*, st_order*, unsigned long long, select_result*, st_select_lex_unit*, st_select_lex*) (sql_select.cc:3006)
|
==4850== by 0x639568: handle_select(THD*, LEX*, select_result*, unsigned long) (sql_select.cc:312)
|
==4850== by 0x612867: execute_sqlcom_select(THD*, TABLE_LIST*) (sql_parse.cc:4616)
|
==4850== by 0x60B3AC: mysql_execute_command(THD*) (sql_parse.cc:2184)
|
==4850== by 0x6150CE: mysql_parse(THD*, char*, unsigned int, Parser_state*) (sql_parse.cc:5731)
|
==4850== by 0x608972: dispatch_command(enum_server_command, THD*, char*, unsigned int) (sql_parse.cc:1055)
|
==4850== by 0x607C29: do_command(THD*) (sql_parse.cc:794)
|
==4850==
|
bzr version-info
revision-id: timour@askmonty.org-20120518115201-s6byggvesqxcntkd
|
date: 2012-05-18 14:52:01 +0300
|
revno: 3404
|
Reproducible with default optimizer_switch as well as with all OFF values except for materialization and semijoin.
Reproducible with MyISAM, Aria, InnoDB.
Minimal optimizer_switch:
materialization=on,semijoin=on
|
Full optimizer_switch (default):
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 NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE
|
2 MATERIALIZED NULL NULL NULL NULL NULL NULL NULL NULL Select tables optimized away
|
3 SUBQUERY t1 ALL NULL NULL NULL NULL 2 100.00
|
Warnings:
|
Note 1003 select `test`.`t1`.`a` AS `a` from <materialize> (select max(`test`.`t2`.`b`) from `test`.`t2` where 1) join `test`.`t1` where 0
|
Test case:
SET optimizer_switch='materialization=on,semijoin=on';
|
|
CREATE TABLE t1 (a INT);
|
INSERT INTO t1 VALUES (0),(8);
|
|
CREATE TABLE t2 (b INT PRIMARY KEY);
|
INSERT INTO t2 VALUES (1),(2);
|
|
SELECT * FROM t1
|
WHERE 4 IN (
|
SELECT MAX(b) FROM t2
|
WHERE EXISTS ( SELECT * FROM t1 )
|
);
|
|
If you run the test without MTR, run valgrind as
valgrind --tool=memcheck --leak-check=yes --show-reachable=yes <mysqld command line>
|
|