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

SHOW EXPLAIN: Plan produced by regular explain says 'Using index condition', but SHOW EXPLAIN does not

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Minor
    • Resolution: Fixed
    • None
    • 10.0.0
    • None
    • None

    Description

      For the following query

      SELECT SLEEP(0.03)
      FROM ( SELECT * FROM t1 ) AS alias
      WHERE alias.a < ALL (
      SELECT b FROM t2, t3 WHERE c = b AND d = b );

      standard EXPLAIN returns the plain with 'Using index condition':

      id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
      1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	20	Using where
      3	DEPENDENT SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	20	Using where
      3	DEPENDENT SUBQUERY	t3	ref	d	d	5	test.t2.b	7	Using index condition; Using where

      while SHOW EXPLAIN does not:

      id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
      1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	20	Using where
      3	SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	20	Using where
      3	SUBQUERY	t3	ref	d	d	5	test.t2.b	7	Using where

      (for the other difference, DEPENDENT SUBQUERY vs SUBQUERY, see bug MDEV-327).

      Reproducible with MyISAM, Aria, InnoDB. The test case contains Aria, because with MyISAM it's less reliable.
      Reproducible with the default optimizer_switch.

      bzr version-info

      revision-id: psergey@askmonty.org-20120607171922-w5d79pe7733jrh2r
      date: 2012-06-07 21:19:22 +0400
      revno: 3412

      Test case:

      CREATE TABLE t1 (a INT NOT NULL) ENGINE=Aria;
      INSERT INTO t1 VALUES
      (0),(8),(1),(8),(9),(24),(6),(1),(6),(2),
      (4),(8),(4),(4),(7),(4),(1),(9),(4),(8);
       
      CREATE TABLE t2 (b INT) ENGINE=Aria;
      INSERT INTO t2 VALUES
      (7),(0),(9),(3),(4),(2),(5),(3),(1),(3),(6),
      (7),(5),(1),(204),(224),(9),(5),(0),(3);
       
       
      CREATE TABLE t3 (c INT, d INT, KEY(d)) ENGINE=Aria;
      INSERT INTO t3 VALUES
      (4,4),(8,5),(0,2),(0,5),(0,1),(7,1),(7,2),(5,2),
      (3,2),(8,1),(4,4),(9,5),(6,3),(1,1),(5,2),(6,7),
      (2,6),(4,1),(1,1),(4,8),(3,7),(3,2),(7,6),(6,2),
      (7,1),(9,0),(4,7),(4,5),(2,7),(1,2),(2,1),(94,80),
      (2,3),(3,8),(8,5),(4,0),(9,9),(4,9),(5,7),(5,0),
      (9,5),(3,6),(8,9),(0,3),(9,9),(3,6),(1,7),(0,3),
      (18,16),(8,7),(3,7),(3,6),(9,5),(6,8),(8,7),(3,2),
      (9,1),(5,3),(9,6),(2,6),(2,5),(5,0),(8,7),(6,7),
      (9,6),(0,0),(3,0),(6,8),(5,0),(8,4),(2,0),(12,13),
      (25,248),(1,1),(3,6),(1,6),(3,3),(15,14),(5,0),
      (9,7),(1,6),(8,6),(7,8),(6,5),(2,8),(4,7),(7,3),
      (3,7),(8,3),(4,8),(6,0),(1,1),(7,3),(1,6),(0,8),
      (2,1),(7,1),(2,9),(1,0),(5,6);
       
      --connect (con1,localhost,root,,)
      let $con_id = `SELECT CONNECTION_ID()`;
       
      EXPLAIN 
      SELECT SLEEP(0.03)
      FROM ( SELECT * FROM t1 ) AS alias
      WHERE alias.a < ALL (
      SELECT b FROM t2, t3 WHERE c = b AND d = b );
       
      --send
      SELECT SLEEP(0.03)
      FROM ( SELECT * FROM t1 ) AS alias
      WHERE alias.a < ALL (
      SELECT b FROM t2, t3 WHERE c = b AND d = b );
       
      --connection default
      let $fail = 100;
      while ($fail)
      {
        --error 0,ER_ERROR_WHEN_EXECUTING_COMMAND
        --eval SHOW EXPLAIN FOR $con_id
        --dec $fail
        if (!$mysql_errno)
        {
          let $fail = 0;
        }
      }
      let $pass = 1;
      while ($pass)
      {
        --error 0,ER_ERROR_WHEN_EXECUTING_COMMAND
        --eval SHOW EXPLAIN FOR $con_id
        if ($mysql_errno)
        {
          let $pass = 0;
        }
      }
       
      --connection con1
      --disable_result_log
      --reap
       

      Attachments

        Issue Links

          Activity

            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.