Alice Sherepa added a comment - 2023-07-19 15:11 Thank you for the report! I repeated on 10.4-11.0: 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); second select fails: 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

Yuchen Pei added a comment - 2023-09-22 13:05 mtr case --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

Yuchen Pei added a comment - 2023-11-17 00:43 fixed by MDEV-26247