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