Details
-
Type:
Bug
-
Status: Closed
-
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
Gliffy Diagrams
Attachments
Issue Links
- relates to
-
MDEV-165 MWL#182: Explain running statements: finish coding, submit for review
-
- Closed
-
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
