[#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

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

[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 Created: 2012-06-07 Updated: 2012-06-07 Resolved: 2012-06-07
Status:	Closed
Project:	MariaDB Server
Component/s:	None
Affects Version/s:	None
Fix Version/s:	10.0.0