INSTALL SONAME 'ha_federatedx'; 

eval create server s foreign data wrapper mysql options (host "127.0.0.1", database "test", user "root", port $MASTER_MYPORT);

set optimizer_switch="materialization=off";

CREATE TABLE t1 (a INT);

INSERT INTO t1 VALUES (3),(2),(3);

CREATE TABLE t2 (pk INT PRIMARY KEY);

INSERT INTO t2 VALUES (1),(2),(3),(4);

CREATE TABLE t2_fed ENGINE=FEDERATED CONNECTION='s/t2';

SELECT * FROM t2_fed WHERE pk IN ( SELECT a FROM t1 );

SET optimizer_switch='semijoin=off';

SELECT * FROM t2_fed WHERE pk IN ( SELECT a FROM t1 );

# Cleanup

DROP TABLE t2_fed, t1, t2;

DROP SERVER s;

UNINSTALL SONAME 'ha_federatedx';

SELECT * FROM t2_fed WHERE pk IN ( SELECT a FROM t1 );

pk

3

2

3

SET optimizer_switch='semijoin=off';

SELECT * FROM t2_fed WHERE pk IN ( SELECT a FROM t1 );

pk

2

3

EXPLAIN EXTENDED SELECT * FROM t2_fed WHERE pk IN ( SELECT a FROM t1 );

id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra

1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where; Start temporary

1	PRIMARY	t2_fed	eq_ref	PRIMARY	PRIMARY	4	test.t1.a	1	33.33	End temporary

Warnings:

Note	1003	select `test`.`t2_fed`.`pk` AS `pk` from `test`.`t2_fed` semi join (`test`.`t1`) where `test`.`t2_fed`.`pk` = `test`.`t1`.`a`

EXPLAIN EXTENDED SELECT * FROM t2_fed WHERE pk IN ( SELECT a FROM t1 );

id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra

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

1	PRIMARY	t2_fed	eq_ref	PRIMARY	PRIMARY	4	test.t1.a	1	100.00	

2	MATERIALIZED	t1	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where

Warnings:

Note	1003	select `test`.`t2_fed`.`pk` AS `pk` from `test`.`t2_fed` semi join (`test`.`t1`) where `test`.`t2_fed`.`pk` = `test`.`t1`.`a`