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

EXPLAIN shows type=range, while SHOW EXPLAIN and userstat show full table scan is used

    Details

    • Type: Bug
    • Status: Closed
    • Priority: Major
    • Resolution: Fixed
    • Affects versions: None
    • Fix versions: 10.0.0
    • Components: None
    • Labels:
      None
    • Sprint:

      Description

      For the following query

      SELECT MIN(b) FROM ( SELECT * FROM t1, t2, t3 WHERE d = b ) AS alias1 
      WHERE SLEEP(0.2) OR c < 'p' OR b = ( SELECT MIN(b) FROM t2 )
      

      usual EXPLAIN produces

      id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
      1	PRIMARY	t1	system	NULL	NULL	NULL	NULL	1	
      1	PRIMARY	t2	range	b	b	5	NULL	2	Using index condition; Using where
      1	PRIMARY	t3	ref	d	d	5	test.t2.b	2	Using where; Using index
      3	SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Select tables optimized away
      

      while SHOW EXPLAIN says

      id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
      1	PRIMARY	t1	system	NULL	NULL	NULL	NULL	1	
      1	PRIMARY	t2	ALL	b	NULL	NULL	NULL	2	Using where
      1	PRIMARY	t3	ref	d	d	5	test.t2.b	2	Using where; Using index
      3	SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Select tables optimized away
      

      As always, SLEEP in the query is not essential, it just makes the query a bit longer and allows to catch it by SHOW EXPLAIN.

      bzr version-info (5.5-show-explain-test1)

      revision-id: psergey@askmonty.org-20120719215203-m2p9cbqb37n0th7n
      date: 2012-07-20 01:52:03 +0400
      build-date: 2012-07-24 04:43:23 +0400
      revno: 3456
      

      Test case:

      CREATE TABLE t1 (a VARCHAR(3) PRIMARY KEY) ENGINE=MyISAM;
      INSERT INTO t1 VALUES ('USA');
      
      CREATE TABLE t2 (b INT, c VARCHAR(52), KEY(b)) ENGINE=MyISAM;
      INSERT INTO t2 VALUES (3813,'United States'),(3940,'Russia');
      
      CREATE TABLE t3 (d INT, KEY(d)) ENGINE=MyISAM;
      INSERT INTO t3 VALUES (12),(22),(9),(45);
      
      EXPLAIN
      SELECT MIN(b) FROM ( SELECT * FROM t1, t2, t3 WHERE d = b ) AS alias1 
      WHERE SLEEP(0.2) OR c < 'p' OR b = ( SELECT MIN(b) FROM t2 );
      --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
        SELECT MIN(b) FROM ( SELECT * FROM t1, t2, t3 WHERE d = b ) AS alias1 
        WHERE SLEEP(0.2) OR c < 'p' OR b = ( SELECT MIN(b) FROM t2 );
      
      
        --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:
                4 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 - 6 hours
                  6h