|
For the following query
SELECT SLEEP(0.03)
|
FROM ( SELECT * FROM t1 ) AS alias
|
WHERE alias.a < ALL (
|
SELECT b FROM t2, t3 WHERE c = b AND d = b );
|
standard EXPLAIN returns the plain with 'Using index condition':
id select_type table type possible_keys key key_len ref rows Extra
|
1 PRIMARY t1 ALL NULL NULL NULL NULL 20 Using where
|
3 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 20 Using where
|
3 DEPENDENT SUBQUERY t3 ref d d 5 test.t2.b 7 Using index condition; Using where
|
while SHOW EXPLAIN does not:
id select_type table type possible_keys key key_len ref rows Extra
|
1 PRIMARY t1 ALL NULL NULL NULL NULL 20 Using where
|
3 SUBQUERY t2 ALL NULL NULL NULL NULL 20 Using where
|
3 SUBQUERY t3 ref d d 5 test.t2.b 7 Using where
|
(for the other difference, DEPENDENT SUBQUERY vs SUBQUERY, see bug MDEV-327).
Reproducible with MyISAM, Aria, InnoDB. The test case contains Aria, because with MyISAM it's less reliable.
Reproducible with the default optimizer_switch.
bzr version-info
revision-id: psergey@askmonty.org-20120607171922-w5d79pe7733jrh2r
|
date: 2012-06-07 21:19:22 +0400
|
revno: 3412
|
Test case:
CREATE TABLE t1 (a INT NOT NULL) ENGINE=Aria;
|
INSERT INTO t1 VALUES
|
(0),(8),(1),(8),(9),(24),(6),(1),(6),(2),
|
(4),(8),(4),(4),(7),(4),(1),(9),(4),(8);
|
|
CREATE TABLE t2 (b INT) ENGINE=Aria;
|
INSERT INTO t2 VALUES
|
(7),(0),(9),(3),(4),(2),(5),(3),(1),(3),(6),
|
(7),(5),(1),(204),(224),(9),(5),(0),(3);
|
|
|
CREATE TABLE t3 (c INT, d INT, KEY(d)) ENGINE=Aria;
|
INSERT INTO t3 VALUES
|
(4,4),(8,5),(0,2),(0,5),(0,1),(7,1),(7,2),(5,2),
|
(3,2),(8,1),(4,4),(9,5),(6,3),(1,1),(5,2),(6,7),
|
(2,6),(4,1),(1,1),(4,8),(3,7),(3,2),(7,6),(6,2),
|
(7,1),(9,0),(4,7),(4,5),(2,7),(1,2),(2,1),(94,80),
|
(2,3),(3,8),(8,5),(4,0),(9,9),(4,9),(5,7),(5,0),
|
(9,5),(3,6),(8,9),(0,3),(9,9),(3,6),(1,7),(0,3),
|
(18,16),(8,7),(3,7),(3,6),(9,5),(6,8),(8,7),(3,2),
|
(9,1),(5,3),(9,6),(2,6),(2,5),(5,0),(8,7),(6,7),
|
(9,6),(0,0),(3,0),(6,8),(5,0),(8,4),(2,0),(12,13),
|
(25,248),(1,1),(3,6),(1,6),(3,3),(15,14),(5,0),
|
(9,7),(1,6),(8,6),(7,8),(6,5),(2,8),(4,7),(7,3),
|
(3,7),(8,3),(4,8),(6,0),(1,1),(7,3),(1,6),(0,8),
|
(2,1),(7,1),(2,9),(1,0),(5,6);
|
|
--connect (con1,localhost,root,,)
|
let $con_id = `SELECT CONNECTION_ID()`;
|
|
EXPLAIN
|
SELECT SLEEP(0.03)
|
FROM ( SELECT * FROM t1 ) AS alias
|
WHERE alias.a < ALL (
|
SELECT b FROM t2, t3 WHERE c = b AND d = b );
|
|
--send
|
SELECT SLEEP(0.03)
|
FROM ( SELECT * FROM t1 ) AS alias
|
WHERE alias.a < ALL (
|
SELECT b FROM t2, t3 WHERE c = b AND d = b );
|
|
--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 = 1;
|
while ($pass)
|
{
|
--error 0,ER_ERROR_WHEN_EXECUTING_COMMAND
|
--eval SHOW EXPLAIN FOR $con_id
|
if ($mysql_errno)
|
{
|
let $pass = 0;
|
}
|
}
|
|
--connection con1
|
--disable_result_log
|
--reap
|
|
|