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

SHOW EXPLAIN: Plan produced by SHOW EXPLAIN for a query with TEMPTABLE view loses 'DERIVED' line on the way without saying that the plan was already deleted

    XMLWordPrintable

    Details

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

      Description

      For the following query, and many others

      SELECT a + SLEEP(0.5) FROM v1

      standard EXPLAIN with the default optimizer_switch produces the following plan:

      id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
      1	PRIMARY	<derived2>	ALL	NULL	NULL	NULL	NULL	2	
      2	DERIVED	t1	ALL	NULL	NULL	NULL	NULL	2	

      SHOW EXPLAIN sometimes returns the same briefly at the beginning, but quickly loses the 'DERIVED' line:

      id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
      1	PRIMARY	<derived2>	ALL	NULL	NULL	NULL	NULL	2	

      DERIVED line might be at the end of the output or in the middle, it happens anyway.

      Reproducible with MyISAM, Aria, InnoDB.

      bzr version-info

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

      Test case:

      CREATE TABLE t1 (a INT);
      INSERT INTO t1 VALUES (1),(2);
      CREATE ALGORITHM=TEMPTABLE VIEW v1 AS SELECT * FROM t1;
       
      --connect (con1,localhost,root,,)
      let $con_id = `SELECT CONNECTION_ID()`;
       
      EXPLAIN SELECT a + SLEEP(0.5) FROM v1;
       
      --send
      SELECT a + SLEEP(0.5) FROM v1;
       
      --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 = 5;
      while ($pass)
      {
        --error 0,ER_ERROR_WHEN_EXECUTING_COMMAND
        --eval SHOW EXPLAIN FOR $con_id
        --dec $pass
        if ($mysql_errno)
        {
          let $pass = 0;
        }
      }
       
      --connection con1
      --disable_result_log
      --reap
       

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              psergei Sergei Petrunia
              Reporter:
              elenst Elena Stepanova
              Votes:
              0 Vote for this issue
              Watchers:
              1 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.