SET join_cache_level=3;

CREATE TABLE t1 (i1 INT) ENGINE=MyISAM;

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

CREATE TABLE t2 (i2 INT) ENGINE=MyISAM;

INSERT INTO t2 VALUES (7),(5);

CREATE TABLE t3 (i3 INT) ENGINE=MyISAM;

INSERT INTO t3 VALUES (7),(2);

EXPLAIN SELECT * FROM t2 STRAIGHT_JOIN t1 ON (i2 = i1);

SELECT * FROM t1 WHERE i1 IN ( SELECT i3 FROM t2, t3 WHERE i3 = i2 OR 0 );

i1

7

id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra

1	PRIMARY	<subquery2>	ALL	distinct_key	NULL	NULL	NULL	2	100.00	

1	PRIMARY	t1	hash_ALL	NULL	#hash#$hj	5	test.t2.i2	2	100.00	Using where; Using join buffer (flat, BNLH join)

2	MATERIALIZED	t2	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where

2	MATERIALIZED	t3	hash_ALL	NULL	#hash#$hj	5	test.t2.i2	2	100.00	Using where; Using join buffer (flat, BNLH join)

Warnings:

Note	1003	select `test`.`t1`.`i1` AS `i1` from `test`.`t1` semi join (`test`.`t2` join `test`.`t3`) where ((`test`.`t1`.`i1` = `test`.`t3`.`i3`) and (`test`.`t3`.`i3` = `test`.`t2`.`i2`))