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

Select type DEPENDENT SUBQUERY in SHOW EXPLAIN vs SUBQUERY in EXPLAIN

    Details

    • Type: Bug
    • Status: Closed
    • Priority: Minor
    • Resolution: Won't Fix
    • Affects versions: None
    • Fix versions: 10.0.0
    • Components: None
    • Labels:
      None
    • Sprint:

      Description

      EXPLAIN SELECT * FROM t1 
      WHERE a IN ( SELECT b FROM t2 WHERE b <= a OR b IN ( SELECT a FROM t1, t3 WHERE a != b ) );
      id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
      1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	43	
      1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	12	Using where; FirstMatch(t1); Using join buffer (flat, BNL join)
      3	SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
      #---------------
      # SHOW EXPLAIN output:
      id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
      1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	43	
      1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	12	Using where; FirstMatch(t1); Using join buffer (flat, BNL join)
      3	DEPENDENT SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	no matching row in const table
      

      bzr version-info

      revision-id: psergey@askmonty.org-20120801115152-qgxtqd0a5vlqrrhx
      date: 2012-08-01 15:51:52 +0400
      build-date: 2012-08-02 13:13:01 +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);
      INSERT INTO t1 VALUES 
      (12),(22),(9),(45),(13),(11),(17),(33),(26),(11),(9),(33),(17),(10),(10),
      (42),(20),(28),(14),(66),(25),(65),(23),(11),(11),(10),(9),(24),(19),(59),
      (9),(14),(9),(14),(29),(10),(10),(10),(11),(9),(9),(12),(18);
      
      CREATE TABLE t2 (b INT);
      INSERT INTO t2 VALUES (1),(86),(1),(1),(1),(0),(0),(0),(0),(8),(0),(0);
      
      CREATE TABLE t3 (c INT);
      
      
      let $query =
      SELECT * FROM t1 
      WHERE a IN ( SELECT b FROM t2 WHERE b <= a OR b IN ( SELECT a FROM t1, t3 WHERE a != 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, t2, t3;
      

        Attachments

          Issue links

            Activity

              People

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

                Dates

                • Created:
                  Updated:
                  Resolved:

                  Time Tracking

                  Estimated:
                  Original Estimate - Not Specified
                  Not Specified
                  Remaining:
                  Remaining Estimate - 0 minutes
                  0m
                  Logged:
                  Time Spent - 1 hour, 30 minutes
                  1h 30m