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

SHOW EXPLAIN: Plan produced by SHOW EXPLAIN loses 'UNION RESULT' 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 FROM t1      
      WHERE a IN ( SELECT 1+SLEEP(0.5) UNION SELECT 2 );

      standard EXPLAIN produces the following plan (with the default optimizer_switch):

      id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
      1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	Using where
      2	DEPENDENT SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
      3	DEPENDENT UNION	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
      NULL	UNION RESULT	<union2,3>	ALL	NULL	NULL	NULL	NULL	NULL	

      SHOW EXPLAIN sometimes returns the same at the beginning, but quickly loses the 'UNION RESULT' line:

      id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
      1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	Using where
      2	DEPENDENT SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
      3	DEPENDENT UNION	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used

      UNION RESULT string 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 (4),(6);
       
      --connect (con1,localhost,root,,)
      let $con_id = `SELECT CONNECTION_ID()`;
       
      EXPLAIN SELECT a FROM t1
      WHERE a IN ( SELECT 1+SLEEP(0.5) UNION SELECT 2 );
       
      --send
      SELECT a FROM t1
      WHERE a IN ( SELECT 1+SLEEP(0.5) UNION SELECT 2 );
       
      --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
      --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.