We're updating the issue view to help you get more done. 

Select type DEPENDENT SUBQUERY in SHOW EXPLAIN vs SUBQUERY in EXPLAIN

Description

1 2 3 4 5 6 7 8 9 10 11 12 EXPLAIN SELECT * FROM t1 WHERE a IN ( SELECT b FROM t2 WHERE b <= a OR b IN ( SELECT a FROM t1, t3 WHERE a != b ) ); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 43 1 PRIMARY t2 ALL NULL NULL NULL NULL 12 Using where; FirstMatch(t1); Using join buffer (flat, BNL join) 3 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables #--------------- # SHOW EXPLAIN output: id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 43 1 PRIMARY t2 ALL NULL NULL NULL NULL 12 Using where; FirstMatch(t1); Using join buffer (flat, BNL join) 3 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL no matching row in const table

bzr version-info

1 2 3 4 revision-id: psergey@askmonty.org-20120801115152-qgxtqd0a5vlqrrhx date: 2012-08-01 15:51:52 +0400 build-date: 2012-08-02 13:13:01 +0300 revno: 3461

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 62 CREATE TABLE t1 (a INT); INSERT INTO t1 VALUES (12),(22),(9),(45),(13),(11),(17),(33),(26),(11),(9),(33),(17),(10),(10), (42),(20),(28),(14),(66),(25),(65),(23),(11),(11),(10),(9),(24),(19),(59), (9),(14),(9),(14),(29),(10),(10),(10),(11),(9),(9),(12),(18); CREATE TABLE t2 (b INT); INSERT INTO t2 VALUES (1),(86),(1),(1),(1),(0),(0),(0),(0),(8),(0),(0); CREATE TABLE t3 (c INT); let $query = SELECT * FROM t1 WHERE a IN ( SELECT b FROM t2 WHERE b <= a OR b IN ( SELECT a FROM t1, t3 WHERE a != b ) ); 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, t3;

Environment

None

Status

Assignee

Sergei Petrunia

Reporter

Elena Stepanova

Fix versions

Priority

Minor