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)

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Closed (View Workflow)
    • 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

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              psergei Sergei Petrunia
              Reporter:
              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.