set optimizer_switch='exists_to_in=on';

CREATE TABLE t1 (i1 INT, c1 VARCHAR(1)) ENGINE=MyISAM;

INSERT t1 VALUES (7,'v'),(3,'y');

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

INSERT INTO t2 VALUES ('y'),('y');

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

INSERT INTO t3 VALUES 

  ('j'),('v'),('c'),('m'),('d'),

  ('d'),('y'),('t'),('d'),('s');

SELECT * FROM t1 outer_t1 LEFT JOIN t2 ON (c2 = c1 OR c1 > 'z') 

WHERE EXISTS ( SELECT 1 FROM t1, t3 WHERE c3 = c1 AND c1 = outer_t1.c1 );

i1	c1	c2

7	v	NULL

3	y	NULL

i1	c1	c2

3	y	y

3	y	y

7	v	NULL

id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra

1	PRIMARY	outer_t1	ALL	NULL	NULL	NULL	NULL	2	100.00	

1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	4	func	1	100.00	

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

2	MATERIALIZED	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	

2	MATERIALIZED	t3	ALL	NULL	NULL	NULL	NULL	10	100.00	Using where; Using join buffer (flat, BNL join)

Warnings:

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

Note	1003	select `test`.`outer_t1`.`i1` AS `i1`,`test`.`outer_t1`.`c1` AS `c1`,`test`.`t2`.`c2` AS `c2` from `test`.`t1` `outer_t1` semi join (`test`.`t1` join `test`.`t3`) left join `test`.`t2` on((((`test`.`t3`.`c3` = `test`.`t1`.`c1`) and (`test`.`t2`.`c2` = `test`.`outer_t1`.`c1`)) or (`test`.`outer_t1`.`c1` > 'z'))) where (`test`.`t3`.`c3` = `test`.`t1`.`c1`)

revision-id: sanja@askmonty.org-20130213131832-b1sk3puofj29jubr

revno: 3502

branch-nick: 10.0-base-exists2in