|
# This may not work, e.g. for in-source builds, fix the path
|
--source plugin/spider/spider/include/init_spider.inc
|
#--source ../storage/spider/mysql-test/spider/include/init_spider.inc
|
|
SET spider_same_server_link= on;
|
--eval create server s foreign data wrapper mysql options (host "127.0.0.1", database "test", user "root", port $MASTER_MYPORT);
|
|
create table t (a int);
|
insert into t values (2),(2),(1),(3);
|
create table t_spider (a int) engine=Spider comment='wrapper "mysql", srv "s", table "t"';
|
|
SELECT * FROM t WHERE a NOT IN ( SELECT a FROM t_spider );
|
|
# Cleanup
|
drop table t_spider, t;
|
drop server s;
|
|
# This may not work, e.g. for in-source builds, fix the path
|
--source plugin/spider/spider/include/deinit_spider.inc
|
#--source ../storage/spider/mysql-test/spider/include/deinit_spider.inc
|
|
10.4 40adf52d
|
SELECT * FROM t WHERE a NOT IN ( SELECT a FROM t_spider );
|
a
|
1
|
3
|
The expected result is obviously an empty result set.
Plan:
explain extended SELECT * FROM t WHERE a NOT IN ( SELECT a FROM t_spider );
|
id select_type table type possible_keys key key_len ref rows filtered Extra
|
1 PRIMARY t ALL NULL NULL NULL NULL 4 100.00 Using where
|
2 MATERIALIZED NULL NULL NULL NULL NULL NULL NULL NULL Storage engine handles GROUP BY
|
Warnings:
|
Note 1003 /* select#1 */ select `test`.`t`.`a` AS `a` from `test`.`t` where !<expr_cache><`test`.`t`.`a`>(<in_optimizer>(`test`.`t`.`a`,`test`.`t`.`a` in ( <materialize> (/* select#2 */ select `test`.`t_spider`.`a` from `test`.`t_spider` ), <primary_index_lookup>(`test`.`t`.`a` in <temporary table> on distinct_key where `test`.`t`.`a` = `<subquery2>`.`a`))))
|
|