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

SHOW EXPLAIN: Plan returned by SHOW EXPLAIN only contains 'Using temporary' while the standard EXPLAIN says 'Using temporary; Using filesort'

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Closed (View Workflow)
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: None
    • Fix Version/s: 10.0.0
    • Component/s: None
    • Labels:
      None

      Description

      EXPLAIN SELECT a FROM t1 GROUP BY a;

      id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
      1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	4112	Using temporary; Using filesort
      SELECT a FROM t1 GROUP BY a;

      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	4112	Using temporary
      Warnings:
      Note	1003	SELECT a FROM t1 GROUP BY a

      bzr version-info

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

      Reproducible with the standard optimizer_switch as well as with all OFF values.
      Reproducible with MyISAM, Aria, InnoDB.

      1. MTR test case

      CREATE TABLE t1 (a INT);
      INSERT INTO t1 VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),
        (10),(11),(12),(13),(14),(15),(16);
      INSERT INTO t1 SELECT t11.a FROM t1 t11, t1 t12, t1 t13;
       
      --connect (con1,localhost,root,,)
      let $con_id = `SELECT CONNECTION_ID()`;
       
      EXPLAIN SELECT a FROM t1 GROUP BY a;
      send SELECT a FROM t1 GROUP BY a;
       
      --connection default
      let $run = 20;
      while ($run)
      {
        --error 0,ER_ERROR_WHEN_EXECUTING_COMMAND
        eval SHOW EXPLAIN FOR $con_id;
        --dec $run
      }
       
      --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.