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

Valgrind warnings (blocks are definitely lost) in filesort on IN subquery with SUM and DISTINCT

    Details

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

      Description

      ==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 
      );

        Attachments

          Issue Links

            Activity

              People

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

                Dates

                • Created:
                  Updated:
                  Resolved: