select nextID from tab1 LEFT join tab2 on id = currentID where id in ( 1 );

ERROR 1064 (42000): 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 'join `forensic`.`tab2` t0 on (t0.`currentID` = 1 where 1' at line 1

log_warnings=1

default_storage_engine=MyISAM

innodb=OFF

general_log=ON

expire_logs_days=2

create database mydb default character set latin1;

use mydb

create user spidy@`%`;

set password for spidy@`%` = password('password123');

grant select on *.* to spidy@`%`;

-- grant all on mydb.tab1 to spidy@`%`;

-- grant all on mydb.tab2 to spidy@`%`;

create table tab1 (

  id int(4) not null auto_increment,

  status varchar(25) default null,

  primary key (id)

) engine=MYISAM default charset=latin1;

create table tab2 (

  currentID int(4) not null default 0,

  nextID int(4) not null default 0,

  primary key (currentID, nextID)

) engine=MYISAM default charset=latin1;

insert into tab1 values 

(null, 'WORKING'),

(null, 'READY FOR QC'),

(null, 'READY TO CREATE'),

(null, 'CREATED'),

(null, 'APPROVAL ERROR'),

(null, 'CREATION ERROR'),

(null, 'AWAITING'),

(null, 'NEEDS RTL'),

(null, 'RTL SUBMITTED'),

(null, 'ARCHIVED');

insert into tab2 values 

(1,2),

(1,7),

(1,8),

(1,10),

(2,6),

(2,10),

(3,4),

(3,6),

(3,10),

(5,1),

(5,10),

(6,1),

(6,10),

(7,2),

(7,10),

(8,5),

(8,9),

(8,10),

(9,2),

(9,5),

(9,7),

(9,10),

(10,1),

(10,2),

(10,3),

(10,5),

(10,6),

(10,7),

(10,8),

(10,9);

create or replace server dataNode1 foreign data wrapper mysql options (PORT 3306, HOST 'hostname', USER 'spidy', PASSWORD 'password123');

select * from mysql.servers;

select db_name, table_name, server from mysql.spider_tables;

create table mydb.tab1 engine=SPIDER comment='wrapper "mysql", srv "dataNode1", table "tab1"';

create table mydb.tab2 engine=SPIDER comment='wrapper "mysql", srv "dataNode1", table "tab2"';

-- Test cases:

-- produces ERROR

select nextID 

from tab1

LEFT join tab2 on id = currentID

where id in ( 1 );

-- produces ERROR

select nextID

from tab2

RIGHT join tab1 on currentID = id

where id in ( 1 );

-- produces ERROR

select nextID

from tab2

RIGHT join tab1 on currentID = id

where id in ( 1,2 );

ERROR 1064 (42000): 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 'join `forensic`.`tab2` t0 on (t0.`currentID` = 1 where 1' at line 1

-- OK - no error

select nextID 

from tab1

LEFT join tab2 on id = currentID

where id in ( 1,2 );

-- OK - no error

select nextID 

from tab2

LEFT join tab1 on id = currentID

where id in ( 1 );

-- OK - no error

select nextID

from tab1

RIGHT join tab2 on currentID = id

where id in ( 1 );