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

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

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

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;