SET optimizer_switch = 'semijoin=on';

CREATE TABLE t1 (pk INT PRIMARY KEY, c1 VARCHAR(1)) ENGINE=MyISAM;

INSERT INTO t1 VALUES (1,'v'),(2,'v'),(3,'c'),(4,NULL),(5,'x');

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

INSERT INTO t2 VALUES ('x');

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

INSERT INTO t3 VALUES ('x'),('d');

SELECT * FROM t1, t2 WHERE pk IN ( SELECT pk FROM t1 LEFT JOIN t3 ON (c1 = c3 ) ) ORDER BY c2, c1;

pk	c1	c2

4	NULL	x

pk	c1	c2

4	NULL	x

3	c	x

1	v	x

2	v	x

5	x	x

revision-id: bar@mnogosearch.org-20131016141313-63jt6geakz1e6dg2

revno: 3708

branch-nick: 5.3

EXPLAIN EXTENDED

SELECT * FROM t1, t2 WHERE pk IN ( SELECT pk FROM t1 LEFT JOIN t3 ON (c1 = c3 ) ) ORDER BY c2, c1;

id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra

1	PRIMARY	t2	system	NULL	NULL	NULL	NULL	1	100.00	Using filesort

1	PRIMARY	t1	ALL	PRIMARY	NULL	NULL	NULL	5	100.00	

1	PRIMARY	t1	eq_ref	PRIMARY	PRIMARY	4	test.t1.pk	1	100.00	

1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where; Start temporary; End temporary

Warnings:

Note	1003	select `test`.`t1`.`pk` AS `pk`,`test`.`t1`.`c1` AS `c1`,'x' AS `c2` from `test`.`t1` semi join (`test`.`t3`) join `test`.`t1` join `test`.`t2` where (`test`.`t1`.`pk` = `test`.`t1`.`pk`) order by 'x',`test`.`t1`.`c1`