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

SHOW EXPLAIN: 'Using where' for a subquery is shown in EXPLAIN, but not in SHOW EXPLAIN output

    XMLWordPrintable

    Details

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

      Description

      EXPLAIN SELECT * 
      FROM t1 AS alias1, ( SELECT * FROM t2 ) AS alias
      WHERE EXISTS ( SELECT * FROM t2 WHERE b = c ) 
      OR a <= 10;
      id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
      1	PRIMARY	alias1	ALL	NULL	NULL	NULL	NULL	14	
      1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	20	Using join buffer (flat, BNL join)
      3	SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	20	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	14	
      1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	20	Using join buffer (flat, BNL join)
      3	SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	20	

      bzr version-info

      revision-id: psergey@askmonty.org-20120727121752-5dmlp1nkhrn01ab5
      date: 2012-07-27 16:17:52 +0400
      build-date: 2012-08-01 17:33:14 +0300
      revno: 3460

      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) ENGINE=MyISAM;
      INSERT INTO t1 VALUES (7),(0),(9),(3),(4),(2),(5),(7),(0),(9),(3),(4),(2),(5);		
       
      CREATE TABLE t2 (b INT, c INT) ENGINE=MyISAM;
      INSERT INTO t2 VALUES 
      (0,4),(8,6),(1,3),(8,5),(9,3),(24,246),(6,2),(1,9),(6,3),(2,8),
      (4,1),(8,8),(4,8),(4,5),(7,7),(4,5),(1,1),(9,6),(4,2),(8,9);
       
       
      let $query =
      SELECT * 
      FROM t1 AS alias1, ( SELECT * FROM t2 ) AS alias
      WHERE EXISTS ( SELECT * FROM t2 WHERE b = c ) 
      OR a <= 10;
       
       
      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, t2;

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              psergei Sergei Petrunia
              Reporter:
              elenst Elena Stepanova
              Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved:

                  Git Integration