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

SHOW EXPLAIN: Different select_type in plans produced by SHOW EXPLAIN and standard EXPLAIN: 'SUBQUERY' vs 'DEPENDENT SUBQUERY'

    XMLWordPrintable

Details

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

    Description

      For the following query

      SELECT * FROM t1, ( SELECT * FROM t2 ) AS alias
      WHERE a < ALL ( SELECT b FROM t1, t2 WHERE a = b )

      with the default optimizer_switch the usual EXPLAIN returns

      id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
      1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	20	Using where
      1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	20	Using join buffer (flat, BNL join)
      3	DEPENDENT SUBQUERY	t1	ALL	NULL	NULL	NULL	NULL	20	
      3	DEPENDENT SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	20	Using where; Using join buffer (flat, BNL join)

      while SHOW EXPLAIN produces

      id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
      1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	20	Using where
      1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	20	Using join buffer (flat, BNL join)
      3	SUBQUERY	t1	ALL	NULL	NULL	NULL	NULL	20	
      3	SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	20	Using where; Using join buffer (flat, BNL join)

      bzr version-info

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

      Reproducible with MyISAM, Aria, InnoDB. The test case contains Aria because with MyISAM it is less reliable.

      Test case:

      CREATE TABLE t1 (a INT) ENGINE=Aria;
      INSERT INTO t1 VALUES
      (4),(6),(3),(5),(3),(246),(2),(9),(3),(8),
      (1),(8),(8),(5),(7),(5),(1),(6),(2),(9);
       
      CREATE TABLE t2 (b INT) ENGINE=Aria;
      INSERT INTO t2 VALUES
      (1),(7),(4),(7),(0),(2),(9),(4),(0),(9),
      (1),(3),(8),(8),(18),(84),(6),(3),(6),(6);
       
      --connect (con1,localhost,root,,)
      let $con_id = `SELECT CONNECTION_ID()`;
       
      EXPLAIN 
      SELECT * FROM t1, ( SELECT * FROM t2 ) AS alias
      WHERE a < ALL ( SELECT b FROM t1, t2 WHERE a = b );
       
      --send
      SELECT * FROM t1, ( SELECT * FROM t2 ) AS alias
      WHERE a < ALL ( SELECT b FROM t1, t2 WHERE a = 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.