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

SHOW EXPLAIN: select_type returned by SHOW EXPLAIN is different from the normal EXPLAIN (SIMPLE vs PRIMARY)

        Details

        • Type: Bug
        • Status: Closed
        • Priority: Major
        • Resolution: Won't Fix
        • Affects Version/s: None
        • Fix Version/s: 10.0.0
        • Component/s: None
        • Labels:
          None

          Description

          EXPLAIN SELECT a FROM t1 WHERE ( SELECT SLEEP(0.5) );
id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
1       PRIMARY t1      ALL     NULL    NULL    NULL    NULL    2       Using where
Warnings:
Note    1249    Select 2 was reduced during optimization
SELECT a FROM t1 WHERE ( SELECT SLEEP(0.5) );

          SHOW EXPLAIN FOR 2;
id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
1       SIMPLE  t1      ALL     NULL    NULL    NULL    NULL    2       Using where
Warnings:
Note    1003    SELECT a FROM t1 WHERE ( SELECT SLEEP(0.5) )

          bzr version-info

          revision-id: psergey@askmonty.org-20120524182239-gq6jtnnovydnthtl
date: 2012-05-24 22:22:39 +0400
revno: 3405

          Reproducible with the default optimizer_switch as well as with all OFF values (except for in_to_exists which is required to execute the query).
          Reproducible with MyISAM, Aria, InnoDB.

          Test case:

          CREATE TABLE t1 (a INT);
INSERT INTO t1 VALUES (1),(2);

--connect (con1,localhost,root,,)
let $con_id = `SELECT CONNECTION_ID()`;

EXPLAIN SELECT a FROM t1 WHERE ( SELECT SLEEP(0.5) );
send SELECT a FROM t1 WHERE ( SELECT SLEEP(0.5) );

--connection default
let $run = 100;
while ($run)
{
  --error 0,ER_ERROR_WHEN_EXECUTING_COMMAND
  --eval SHOW EXPLAIN FOR $con_id
  --dec $run
  if (!$mysql_errno)
  {
    let $run = 0;
  }
}

--connection con1
--reap

            Gliffy Diagrams

              Attachments

                Issue Links

                relates to

                Task - A task that needs to be done. MDEV-165 MWL#182: Explain running statements: finish coding, submit for review

                • Minor - Minor loss of function, or other problem where easy workaround is present.
                • Closed

                  Activity

                  Hide
                  Permalink
                  psergey Sergei Petrunia added a comment -

                  This is a known property, as documented here: http://kb.askmonty.org/en/show-explain/

                  Show
                  psergey Sergei Petrunia added a comment - This is a known property, as documented here: http://kb.askmonty.org/en/show-explain/

                    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: