SHOW EXPLAIN: 'Using where' for a subquery is shown in EXPLAIN, but not in SHOW EXPLAIN output

Description

1 2 3 4 5 6 7 8 9 10 11 12 13 14 EXPLAIN SELECT * FROM t1 AS alias1, ( SELECT * FROM t2 ) AS alias WHERE EXISTS ( SELECT * FROM t2 WHERE b = c ) OR a <= 10; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY alias1 ALL NULL NULL NULL NULL 14 1 PRIMARY t2 ALL NULL NULL NULL NULL 20 Using join buffer (flat, BNL join) 3 SUBQUERY t2 ALL NULL NULL NULL NULL 20 Using where #--------------- # SHOW EXPLAIN output: id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY alias1 ALL NULL NULL NULL NULL 14 1 PRIMARY t2 ALL NULL NULL NULL NULL 20 Using join buffer (flat, BNL join) 3 SUBQUERY t2 ALL NULL NULL NULL NULL 20

bzr version-info

1 2 3 4 revision-id: psergey@askmonty.org-20120727121752-5dmlp1nkhrn01ab5 date: 2012-07-27 16:17:52 +0400 build-date: 2012-08-01 17:33:14 +0300 revno: 3460

Default optimizer_switch:

1 index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=off

Test case:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 CREATE TABLE t1 (a INT) ENGINE=MyISAM; INSERT INTO t1 VALUES (7),(0),(9),(3),(4),(2),(5),(7),(0),(9),(3),(4),(2),(5); CREATE TABLE t2 (b INT, c INT) ENGINE=MyISAM; INSERT INTO t2 VALUES (0,4),(8,6),(1,3),(8,5),(9,3),(24,246),(6,2),(1,9),(6,3),(2,8), (4,1),(8,8),(4,8),(4,5),(7,7),(4,5),(1,1),(9,6),(4,2),(8,9); let $query = SELECT * FROM t1 AS alias1, ( SELECT * FROM t2 ) AS alias WHERE EXISTS ( SELECT * FROM t2 WHERE b = c ) OR a <= 10; 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;

Environment

None

Status

Assignee

Sergei Petrunia

Reporter

Elena Stepanova

Labels

None

External issue ID

None

External issue ID

None

Fix versions

Priority

Minor
Configure