SHOW EXPLAIN: For dependent subquery EXPLAIN produces type=index, key, 'Using where; Using index', while SHOW EXPLAIN says type=ALL, no key, 'Range checked for each record'

let $query =

SELECT SUM(b) FROM ( SELECT * FROM t1 ) AS alias1, t2 

WHERE b <= ANY ( 

  SELECT a FROM t1 

  WHERE a = b + SLEEP(0.2) OR a >= ( SELECT SUM(b) FROM t2 ) 

)

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra

1	PRIMARY	t1	index	NULL	a	4	NULL	2	Using index

1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	Using where; Using join buffer (flat, BNL join)

3	DEPENDENT SUBQUERY	t1	index	a	a	4	NULL	2	Using where; Using index

4	SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	2	

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra

1	PRIMARY	t1	index	NULL	a	4	NULL	2	Using index

1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	Using where; Using join buffer (flat, BNL join)

3	DEPENDENT SUBQUERY	t1	ALL	a	NULL	NULL	NULL	2	Range checked for each record (index map: 0x1)

4	SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	2	

revision-id: psergey@askmonty.org-20120719215203-m2p9cbqb37n0th7n

date: 2012-07-20 01:52:03 +0400

build-date: 2012-07-24 04:43:23 +0400

revno: 3456

CREATE TABLE t1 (a INT NOT NULL, KEY(a)) ENGINE=MyISAM;

INSERT INTO t1 VALUES (7),(0);

CREATE TABLE t2 (b INT NOT NULL) ENGINE=MyISAM;

INSERT INTO t2 VALUES (0),(8);

let $query =

SELECT SUM(b) FROM ( SELECT * FROM t1 ) AS alias1, t2 

WHERE b <= ANY ( 

  SELECT a FROM t1 

  WHERE a = b + SLEEP(0.2) OR a >= ( SELECT SUM(b) FROM t2 ) 

);

eval EXPLAIN $query;

--echo #---------------

--echo # SHOW EXPLAIN output:

--connect (con1,localhost,root,,)

--let $con_id = `SELECT CONNECTION_ID()`

--let $trials = 50

--disable_query_log

while ($trials)

{

  --dec $trials

  --let $run = 1000

  --send_eval $query

  --connection default

  while ($run)

  {

    --error 0,1932

    eval SHOW EXPLAIN FOR $con_id;

    --dec $run

    if (!$mysql_errno)

    {

      --let $run = 0

      --let $trials = 0

      --let $found = 1

    }

  }

  --disable_result_log

  --connection con1

  --reap

  --enable_result_log

}

if (!$found)

{

  --echo ########### Could not catch the query by SHOW EXPLAIN, try again  #############

}

DROP TABLE t1, t2;