Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-288

CHEAP SQ: Valgrind warnings "Memory lost" with IN and EXISTS nested subquery, materialization+semijoin

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Fixed
    • None
    • 10.0.0
    • None
    • 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

          Activity

            People

              timour Timour Katchaounov (Inactive)
              elenst Elena Stepanova
              Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

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