CREATE TABLE t1 (i INT, c1 CHAR(5), c2 CHAR(5), t1_field VARCHAR(1)) ENGINE=MyISAM;

INSERT INTO t1 VALUES (1,'test1','test2','f'), (2,'test3','test4','d');

CREATE TABLE t2 (t2_field VARCHAR(1)) ENGINE=MyISAM;

INSERT INTO t2 VALUES ('m'), ('b');

CREATE TABLE t3 (t3_field VARCHAR(1)) ENGINE=MyISAM;

INSERT INTO t3 VALUES ('b'),('c');

SELECT * FROM t1, t2 outer_t2 

WHERE EXISTS ( SELECT 1 FROM t2, t3 WHERE t3_field = outer_t2.t2_field AND t2_field <= t1_field );

i	c1	c2	t1_field	t2_field

--------------------------------------------------

1	test1	test2	f		b

2	test3	test4	d		b

EXPLAIN EXTENDED SELECT * FROM t1, t2 outer_t2 WHERE EXISTS ( SELECT 1 FROM t2, t3 WHERE t3_field = outer_t2.t2_field AND t2_field <= t1_field ) ;

id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra

1	PRIMARY	outer_t2	ALL	NULL	NULL	NULL	NULL	2	100.00	

1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	100.00	Start temporary; Using join buffer (flat, BNL join)

1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where; End temporary; Using join buffer (incremental, BNL join)

1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where; Using join buffer (incremental, BNL join)

Warnings:

Note	1276	Field or reference 'test.outer_t2.t2_field' of SELECT #2 was resolved in SELECT #1

Note	1276	Field or reference 'test.t1.t1_field' of SELECT #2 was resolved in SELECT #1

Note	1003	select `test`.`t1`.`i` AS `i`,`test`.`t1`.`c1` AS `c1`,`test`.`t1`.`c2` AS `c2`,`test`.`t1`.`t1_field` AS `t1_field`,`test`.`outer_t2`.`t2_field` AS `t2_field` from `test`.`t1` semi join (`test`.`t2` join `test`.`t3`) join `test`.`t2` `outer_t2` where ((`test`.`t3`.`t3_field` = `test`.`outer_t2`.`t2_field`) and (`test`.`t2`.`t2_field` <= `test`.`t1`.`t1_field`))

revision-id: sanja@askmonty.org-20130207125211-tdiknbz6344njon4

revno: 3488

branch-nick: 10.0-base-exists2in