SET @@session.spider_same_server_link = ON;

GRANT ALL PRIVILEGES ON *.* TO 'spinne'@'127.0.0.1'  IDENTIFIED BY 'Widow2021!';

DROP SERVER data1;

CREATE SERVER IF NOT EXISTS data1

FOREIGN DATA WRAPPER mysql

OPTIONS(

HOST '127.0.0.1',

DATABASE 'test',

USER 'spinne',

PORT 3307,

PASSWORD 'Widow2021!'

);

DROP TABLE IF EXISTS `test`.`spider_data_tab2`;

CREATE TABLE `test`.`spider_data_tab2` (

`col0` varchar(10) NOT NULL,

`col1` varchar(10) NOT NULL,

`col2` varchar(10) NOT NULL,

`col3` datetime NOT NULL DEFAULT '0001-01-01 00:00:00',

`col4` int,

PRIMARY KEY (`col1`,`col2`,`col3`),

KEY `IX_1` (`col3`,`col4`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8

PARTITION BY RANGE COLUMNS(`col3`)

(PARTITION `pmax` VALUES LESS THAN (MAXVALUE) ENGINE = InnoDB);

DROP TABLE IF EXISTS `test`.`spider_tab2`;

CREATE TABLE IF NOT EXISTS `test`.`spider_tab2` (

`col0` varchar(10) NOT NULL,

`col1` varchar(10) NOT NULL,

`col2` varchar(17) NOT NULL,

`col3` datetime NOT NULL DEFAULT '0001-01-01 00:00:00',

`col4` int,

PRIMARY KEY (`col0`,`col1`,`col2`,`col3`)

,KEY `IX_1` (`col3`,`col4`)

) ENGINE=SPIDER DEFAULT CHARSET=utf8 COMMENT='wrapper "mariadb", table "spider_data_tab2"'

PARTITION BY LIST COLUMNS(`col1`)

(PARTITION `ptdef` DEFAULT COMMENT = 'srv "data1"' ENGINE = SPIDER);

DROP TABLE IF EXISTS `test`.`local_tab`;

CREATE TABLE IF NOT EXISTS `test`.`local_tab` (

`col1` varchar(10) NOT NULL,

`col2` varchar(17) NOT NULL,

`col3` varchar(100) NOT NULL,

PRIMARY KEY (`col1`,`col2`,`col3`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8;

INSERT INTO test.spider_data_tab2 VALUES

("8522d5bd69","00174","037ec5b849","2021-03-31 00:00:00",3),

("8522d5bd70","00174","04b11de592","2021-04-01 00:00:00",3),

("8522d5bd71","00174","0f29529250","2021-04-02 00:00:00",1),

("8522d5bd72","00174","1bb3fecc59","2021-04-02 00:00:00",3),

("8522d5bd70","00174","293f5f916a","2021-04-03 00:00:00",3),

("8522d5bd70","00174","3ddba35681","2021-04-04 00:00:00",3),

("8522d5bd70","00166","024009786c","2021-03-31 00:00:00",3),

("8522d5bd69","00166","037ec5b849","2021-04-01 00:00:00",3),

("8522d5bd73","00166","04b11de592","2021-04-02 00:00:00",1),

("8522d5bd72","00166","04f62d1dbf","2021-04-02 00:00:00",3),

("8522d5bd71","00166","0ba4eacc85","2021-04-03 00:00:00",3),

("8522d5bd66","00166","1104ef747c","2021-04-04 00:00:00",3);

INSERT INTO test.local_tab VALUES

("00166", "024009786c", "d1cde678-cd97-11eb-9ff8-020017000a63"),

("00166", "037ec5b849", "d60e4c51-cd97-11eb-9ff8-020017000a63"),

("00166", "04b11de592", "d007ede3-cd97-11eb-9ff8-020017000a63"),

("00166", "04f62d1dbf", "d0fd469c-cd97-11eb-9ff8-020017000a63"),

("00166", "0ba4eacc85", "cfaf324d-cd97-11eb-9ff8-020017000a63"),

("00166", "1104ef747c", "cceafd90-cd97-11eb-9ff8-020017000a63"),

("00174", "037ec5b849", "d60e6dd5-cd97-11eb-9ff8-020017000a63"),

("00174", "04b11de592", "d007f3f3-cd97-11eb-9ff8-020017000a63"),

("00174", "0f29529250", "d0fd4d7e-cd97-11eb-9ff8-020017000a63"),

("00174", "1bb3fecc59", "cceb03e5-cd97-11eb-9ff8-020017000a63"),

("00174", "293f5f916a", "d419c420-cd97-11eb-9ff8-020017000a63");

SELECT DISTINCT A.col1, A.col2, B.col3 FROM test.local_tab A INNER JOIN test.spider_data_tab2 B ON A.col1 = B.col1 AND A.col2 = B.col2 AND A.col1 IN ('00166' , '00174') AND B.col4 <> 1 AND B.col3 >= '2021-04-01 00:00:00' AND B.col3 <= '2021-04-03 00:00:00';

SELECT DISTINCT A.col1, A.col2, B.col3 FROM test.local_tab A INNER JOIN test.spider_tab2 B ON A.col1 = B.col1 AND A.col2 = B.col2 AND A.col1 IN ('00166' , '00174') AND B.col4 <> 1 AND B.col3 >= '2021-04-01 00:00:00' AND B.col3 <= '2021-04-03 00:00:00';