[MDEV-288] CHEAP SQ: Valgrind warnings "Memory lost" with IN and EXISTS nested subquery, materialization+semijoin - Jira

XML

Word

Printable

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Major
Resolution: Fixed
Affects Version/s: None
Fix Version/s: 10.0.0
Component/s: None
Labels:
None

Description

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>

Attachments

Issue Links

relates to

MDEV-193 LP:944706 - Query with impossible or constant subquery in WHERE or HAVING is not precomputed and thus not part of optimization

Closed

Activity

People

Assignee:: Timour Katchaounov (Inactive)

Reporter:: Elena Stepanova

Votes:: 0 Vote for this issue

Watchers:: 2 Start watching this issue

Dates

Created:: 2012-05-21 16:01

Updated:: 2012-06-01 14:49

Resolved:: 2012-06-01 14:49

Git Integration

Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.