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

Extra field 'Using filesort' in EXPLAIN vs 'Using where; Using filesort' in SHOW EXPLAIN on a query with HAVING and ORDER BY

Details

    • Bug
    • Status: Closed (View Workflow)
    • Minor
    • Resolution: Won't Fix
    • None
    • 10.0.0
    • None
    • None

    Description

      EXPLAIN SELECT alias1.b FROM t1 AS alias1 LEFT JOIN t1 AS alias2 ON (alias2.b = alias1.a)  
      WHERE EXISTS ( SELECT * FROM t1 WHERE a <= alias2.a )  
      HAVING alias1.b > 10 ORDER BY alias1.b;
      id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
      1	PRIMARY	alias1	ALL	NULL	NULL	NULL	NULL	9	Using filesort
      1	PRIMARY	alias2	ref	b	b	5	test.alias1.a	2	Using where
      2	DEPENDENT SUBQUERY	t1	ALL	NULL	NULL	NULL	NULL	9	Using where
      #---------------
      # SHOW EXPLAIN output:
      id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
      1	PRIMARY	alias1	ALL	NULL	NULL	NULL	NULL	9	Using where; Using filesort
      1	PRIMARY	alias2	ref	b	b	5	test.alias1.a	2	Using where
      2	DEPENDENT SUBQUERY	t1	ALL	NULL	NULL	NULL	NULL	9	Using where

      bzr version-info

      revision-id: psergey@askmonty.org-20120801115152-qgxtqd0a5vlqrrhx
      date: 2012-08-01 15:51:52 +0400
      build-date: 2012-08-02 16:12:26 +0300
      revno: 3461

      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

      Test case:

      CREATE TABLE t1 (a INT, b INT, KEY(b)) ENGINE=MyISAM;
      INSERT INTO t1 VALUES 
      (0,4),(8,6),(1,3),(8,5),(9,3),(2,4),(6,2),(1,9),(6,3);
       
      	
      let $query =
      SELECT alias1.b FROM t1 AS alias1 LEFT JOIN t1 AS alias2 ON (alias2.b = alias1.a)  
      WHERE EXISTS ( SELECT * FROM t1 WHERE a <= alias2.a )  
      HAVING alias1.b > 10 ORDER BY alias1.b;
       
       
      eval EXPLAIN $query;
      --echo #---------------
      --echo # SHOW EXPLAIN output:
       
      --connect (con1,localhost,root,,)
      --let $con_id = `SELECT CONNECTION_ID()`
       
      --let $trials = 50
       
      --disable_query_log
       
      while ($trials)
      {
        --dec $trials
        --let $run = 1000
       
        --send_eval $query
       
        --connection default
        while ($run)
        {
          --error 0,1932
          eval SHOW EXPLAIN FOR $con_id;
          --dec $run
          if (!$mysql_errno)
          {
            --let $run = 0
            --let $trials = 0
            --let $found = 1
          }
        }
       
        --disable_result_log
        --connection con1
        --reap
        --enable_result_log
       
      }
       
      if (!$found)
      {
        --echo ########### Could not catch the query by SHOW EXPLAIN, try again  #############
      }
       
      DROP TABLE t1;
       

      Attachments

        Issue Links

          Activity

            Repeatable:

            set @show_explain_probe_select_id=1;
            set debug_dbug='d,show_explain_probe_do_select';

            Not repeatable with

            set @show_explain_probe_select_id=1;
            set debug_dbug='d,show_explain_probe_join_exec_start';

            psergei Sergei Petrunia added a comment - Repeatable: set @show_explain_probe_select_id=1; set debug_dbug='d,show_explain_probe_do_select'; Not repeatable with set @show_explain_probe_select_id=1; set debug_dbug='d,show_explain_probe_join_exec_start';

            === Debugging EXPLAIN

            • join->join_tab[0].select_cond == NULL,
            • (gdb) p dbug_print_item(join->join_tab[1].select_cond)
              $32 = 0x8d910e0 "(trigcond(<expr_cache><alias2.a>(exists(select 1 from t1 where (t1.a <= alias2.a)))) and trigcond(trigcond((alias1.a is not null))))"

            output: "Using filesort; Using where".

            === Debugging SHOW EXPLAIN

            • join->join_tab[0].select_cond == NULL
            • (gdb) p dbug_print_item(join->join_tab[1].select_cond)
              $35 = 0x8d910e0 "(trigcond(<expr_cache><alias2.a>(exists(select 1 from t1 where (t1.a <= alias2.a)))) and trigcond(trigcond((alias1.a is not null))))"
              (same)

            However, there is a saved JOIN::pre_sort_join_tab. From that, we find:
            (gdb) p dbug_print_item(tab->select_cond)
            $37 = 0x8d910e0 "(`j3`.`alias1`.`b` > 10)"
            .. and this is what causes the "Using where" to be printed together with 'using
            filesort'.

            The "Using where" is actually being applied on execution:

            (gdb) wher
            #0 Item_func_gt::val_int (
            #1 0x082e913f in SQL_SELECT::skip_record (
            #2 0x083eac01 in find_all_keys (
            #3 0x083eb70e in filesort (
            #4 0x082931d8 in create_sort_index (
            #5 0x08297e46 in JOIN::exec_inner (
            #6 0x08294ce0 in JOIN::exec (

            psergei Sergei Petrunia added a comment - === Debugging EXPLAIN join->join_tab [0] .select_cond == NULL, (gdb) p dbug_print_item(join->join_tab [1] .select_cond) $32 = 0x8d910e0 "(trigcond(<expr_cache><alias2.a>(exists(select 1 from t1 where (t1.a <= alias2.a)))) and trigcond(trigcond((alias1.a is not null))))" output: "Using filesort; Using where". === Debugging SHOW EXPLAIN join->join_tab [0] .select_cond == NULL (gdb) p dbug_print_item(join->join_tab [1] .select_cond) $35 = 0x8d910e0 "(trigcond(<expr_cache><alias2.a>(exists(select 1 from t1 where (t1.a <= alias2.a)))) and trigcond(trigcond((alias1.a is not null))))" (same) However, there is a saved JOIN::pre_sort_join_tab. From that, we find: (gdb) p dbug_print_item(tab->select_cond) $37 = 0x8d910e0 "(`j3`.`alias1`.`b` > 10)" .. and this is what causes the "Using where" to be printed together with 'using filesort'. The "Using where" is actually being applied on execution: (gdb) wher #0 Item_func_gt::val_int ( #1 0x082e913f in SQL_SELECT::skip_record ( #2 0x083eac01 in find_all_keys ( #3 0x083eb70e in filesort ( #4 0x082931d8 in create_sort_index ( #5 0x08297e46 in JOIN::exec_inner ( #6 0x08294ce0 in JOIN::exec (

            conclusion: this is EXPLAIN's problem, not SHOW EXPLAIN's.

            psergei Sergei Petrunia added a comment - conclusion: this is EXPLAIN's problem, not SHOW EXPLAIN's.

            EXPLAIN's problem.

            psergei Sergei Petrunia added a comment - EXPLAIN's problem.

            People

              psergei Sergei Petrunia
              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.