set password for 'root'@localhost='';

INSTALL SONAME "ha_spider";

CREATE DATABASE spidertesta;

CREATE TABLE spidertesta.testtable ( testnum bigint(20) NOT NULL, readvalue bigint(20) DEFAULT 0, PRIMARY KEY (testnum)) engine=myisam;

CREATE SERVER localspider FOREIGN DATA WRAPPER mysql OPTIONS( HOST '127.0.0.1', DATABASE 'spidertesta',USER 'root', PASSWORD '',PORT 16000 );

CREATE DATABASE spidertestb;

SET @@spider_same_server_link=1;

CREATE TABLE spidertestb.testtable ( testnum bigint(20) NOT NULL, readvalue bigint(20) DEFAULT 0, PRIMARY KEY (testnum)) ENGINE=SPIDER COMMENT='srv "localspider"';

SET SESSION optimizer_switch='semijoin=off';

SELECT * FROM spidertestb.testtable

WHERE TestNum BETWEEN 0 AND 10 AND ReadValue IN(SELECT ReadValue FROM spidertestb.testtable WHERE TestNum BETWEEN 11 AND 20);

SET SESSION optimizer_switch='semijoin=on';

SELECT * FROM spidertestb.testtable

WHERE TestNum BETWEEN 0 AND 10 AND ReadValue IN(SELECT ReadValue FROM spidertestb.testtable WHERE TestNum BETWEEN 11 AND 20);

query 'SELECT * FROM spidertestb.testtable

WHERE TestNum BETWEEN 0 AND 10 AND ReadValue IN(SELECT ReadValue FROM spidertestb.testtable WHERE TestNum BETWEEN 11 AND 20)' failed: ER_PARSE_ERROR (1064): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '`spidertesta`.`testtable` t1 where ((t1.`readvalue` = t0.`readvalue`) and (t0...' at line 1

--disable_query_log

--disable_result_log

--source ../../t/test_init.inc

--enable_result_log

--enable_query_log

evalp CREATE SERVER srv FOREIGN DATA WRAPPER MYSQL OPTIONS (SOCKET "$MASTER_1_MYSOCK", DATABASE 'test',user 'root');

CREATE TABLE t1 ( a bigint(20) NOT NULL, b bigint(20) DEFAULT 0, PRIMARY KEY (a));

CREATE TABLE t2 ( a bigint(20) NOT NULL, b bigint(20) DEFAULT 0, PRIMARY KEY (a)) ENGINE=SPIDER COMMENT='srv "srv", WRAPPER "mysql", TABLE "t1"';

SET SESSION optimizer_switch='semijoin=off';

SELECT * FROM t2

WHERE A BETWEEN 0 AND 10 AND B IN(SELECT B FROM t2 WHERE A BETWEEN 11 AND 20);

SET SESSION optimizer_switch='semijoin=on';

SELECT * FROM t2

WHERE A BETWEEN 0 AND 10 AND B IN(SELECT B FROM t2 WHERE A BETWEEN 11 AND 20);

drop table t1, t2;

drop server srv;

--disable_query_log

--disable_result_log

--source ../../t/test_deinit.inc

--enable_result_log

--enable_query_log