1 files changed, 385 insertions, 0 deletions
diff --git a/mysql-test/suite/federated/federatedx_create_handlers.test b/mysql-test/suite/federated/federatedx_create_handlers.test
new file mode 100644
index 00000000..61dc32a2
--- /dev/null
+++ b/mysql-test/suite/federated/federatedx_create_handlers.test
@@ -0,0 +1,385 @@
+--source have_federatedx.inc
+--source include/federated.inc
+--source include/no_valgrind_without_big.inc
+--source include/have_partition.inc
+
+connection default;
+
+set global federated_pushdown=1;
+
+connection slave;
+
+DROP TABLE IF EXISTS federated.t1;
+
+CREATE TABLE federated.t1 (
+  id int(20) NOT NULL,
+  name varchar(16) NOT NULL default ''
+)
+DEFAULT CHARSET=latin1;
+
+INSERT INTO federated.t1 VALUES
+  (3,'xxx'), (7,'yyy'), (4,'xxx'), (1,'zzz'), (5,'yyy');
+
+DROP TABLE IF EXISTS federated.t2;
+
+CREATE TABLE federated.t2 (
+  name varchar(16) NOT NULL default ''
+)
+DEFAULT CHARSET=latin1;
+
+INSERT INTO federated.t2 VALUES
+  ('yyy'), ('www'), ('yyy'), ('xxx'), ('www'), ('yyy'), ('www');
+
+
+connection master;
+
+DROP TABLE IF EXISTS federated.t1;
+
+--replace_result $SLAVE_MYPORT SLAVE_PORT
+eval
+CREATE TABLE federated.t1 (
+  id int(20) NOT NULL,
+  name varchar(16) NOT NULL default ''
+)
+ENGINE="FEDERATED" DEFAULT CHARSET=latin1
+CONNECTION='mysql://root@127.0.0.1:$SLAVE_MYPORT/federated/t1';
+
+DROP TABLE IF EXISTS federated.t2;
+
+--replace_result $SLAVE_MYPORT SLAVE_PORT
+eval
+CREATE TABLE federated.t2 (
+  name varchar(16) NOT NULL default ''
+)
+ENGINE="FEDERATED" DEFAULT CHARSET=latin1
+CONNECTION='mysql://root@127.0.0.1:$SLAVE_MYPORT/federated/t2';
+
+SELECT * FROM federated.t1;
+
+SELECT id FROM federated.t1 WHERE id < 5;
+
+SELECT count(*), name FROM federated.t1 WHERE id < 5 GROUP BY name;
+
+SELECT * FROM federated.t1, federated.t2
+  WHERE federated.t1.name = federated.t2.name;
+
+SELECT * FROM federated.t1 LEFT JOIN federated.t2
+              ON federated.t1.name = federated.t2.name
+  WHERE federated.t1.id > 1;
+
+SELECT * FROM federated.t1
+  WHERE id IN (SELECT count(*) FROM federated.t2 GROUP BY name);
+
+EXPLAIN
+SELECT id FROM federated.t1 WHERE id < 5;
+ 
+EXPLAIN EXTENDED
+SELECT id FROM federated.t1 WHERE id < 5;
+
+EXPLAIN FORMAT=JSON
+SELECT id FROM federated.t1 WHERE id < 5;
+
+ANALYZE
+SELECT id FROM federated.t1 WHERE id < 5;
+
+--source include/analyze-format.inc
+ANALYZE FORMAT=JSON
+SELECT id FROM federated.t1 WHERE id < 5;
+
+CREATE TABLE federated.t3 (
+  name varchar(16) NOT NULL default ''
+)
+DEFAULT CHARSET=latin1;
+
+INSERT INTO federated.t3 VALUES
+  ('yyy'), ('www'), ('yyy'), ('xxx'), ('www'), ('yyy'), ('www');
+
+#Enable after fix MDEV-31361
+--disable_ps2_protocol
+SELECT *
+FROM federated.t3, (SELECT * FROM federated.t1 WHERE id > 3) t
+WHERE federated.t3.name=t.name;
+--enable_ps2_protocol
+
+EXPLAIN
+SELECT *
+FROM federated.t3, (SELECT * FROM federated.t1 WHERE id > 3) t
+WHERE federated.t3.name=t.name;
+
+EXPLAIN FORMAT=JSON
+SELECT *
+FROM federated.t3, (SELECT * FROM federated.t1 WHERE id > 3) t
+WHERE federated.t3.name=t.name;
+
+ANALYZE
+SELECT *
+FROM federated.t3, (SELECT * FROM federated.t1 WHERE id > 3) t
+WHERE federated.t3.name=t.name;
+
+#Check after fix MDEV-31361
+--disable_ps2_protocol
+SELECT *
+FROM federated.t3, (SELECT t1.name FROM federated.t1
+                    WHERE id IN (SELECT count(*)
+                                 FROM federated.t2 GROUP BY name)) t
+WHERE federated.t3.name=t.name;
+--enable_ps2_protocol
+
+EXPLAIN
+SELECT *
+FROM federated.t3, (SELECT t1.name FROM federated.t1
+                    WHERE id IN (SELECT count(*)
+                                 FROM federated.t2 GROUP BY name)) t
+WHERE federated.t3.name=t.name;
+
+--source include/analyze-format.inc
+ANALYZE FORMAT=JSON
+SELECT *
+FROM federated.t3, (SELECT t1.name FROM federated.t1
+                    WHERE id IN (SELECT count(*)
+                                 FROM federated.t2 GROUP BY name)) t
+WHERE federated.t3.name=t.name;
+
+SELECT t.id, federated.t3.name
+FROM federated.t3,
+     ( SELECT * FROM federated.t1 WHERE id < 3
+       UNION
+       SELECT * FROM federated.t1 WHERE id >= 5) t
+WHERE federated.t3.name=t.name;
+
+EXPLAIN
+SELECT t.id, federated.t3.name
+FROM federated.t3,
+     ( SELECT * FROM federated.t1 WHERE id < 3
+       UNION
+       SELECT * FROM federated.t1 WHERE id >= 5) t
+WHERE federated.t3.name=t.name;
+
+--echo #
+--echo # MDEV-21887: federatedx crashes on SELECT ... INTO query in select_handler code
+--echo #
+
+CREATE TABLE federated.t4 (
+  id int(20) NOT NULL,
+  name varchar(16) NOT NULL default ''
+) engine=myisam;
+insert into federated.t4 select * from federated.t1;
+
+--sorted_result
+select * from federated.t4;
+
+select name into @var from federated.t1 where id=3 limit 1 ;
+select @var;
+--disable_ps2_protocol
+select name into outfile 'tmp.txt' from federated.t1;
+--enable_ps2_protocol
+
+let $path=`select concat(@@datadir, 'test/tmp.txt')`;
+remove_file $path;
+
+--echo #
+--echo # MDEV-22993: Crash on EXPLAIN with PUSHED DOWN SELECT and subquery
+--echo #
+
+explain
+select * from federated.t1
+where name in (select name from federated.t2);
+
+explain format=json
+select * from federated.t1
+where name in (select name from federated.t2);
+
+--echo #
+--echo # MDEV-22993, testcase #2: EXPLAIN output doesn't make sense when
+--echo #   derived table pushdown is used.
+--echo #
+
+create table t5 (a int) engine=myisam;
+insert into t5 values (1),(2);
+
+--echo # Must not show lines with id=3
+explain 
+select * from t5, 
+(select id from federated.t1
+ where name in (select name from federated.t2) or name like 'foo%') as TQ;
+
+--echo # Must not show elements with select_id=3
+explain format=json
+select * from t5, 
+(select id from federated.t1
+ where name in (select name from federated.t2) or name like 'foo%') as TQ;
+
+drop table t5;
+
+DROP TABLE federated.t1, federated.t2, federated.t3, federated.t4;
+
+connection slave;
+DROP TABLE federated.t1, federated.t2;
+
+connection default;
+
+--echo #
+--echo # MDEV-23778: Derived handler used for big derived tables
+--echo #
+
+connection slave;
+
+CREATE TABLE federated.t1 (
+  a varchar(100) NOT NULL default '123'
+)
+DEFAULT CHARSET=latin1;
+
+CREATE TABLE federated.t2 LIKE federated.t1;
+
+DELIMITER $$;
+BEGIN NOT ATOMIC
+  DECLARE i INT DEFAULT 0;
+  START TRANSACTION;
+  WHILE i < 70000 DO
+    INSERT INTO federated.t1 VALUES (i);
+    SET i = i + 1;
+  END WHILE;
+  COMMIT;
+END
+$$
+
+DELIMITER ;$$
+
+connection master;
+
+--replace_result $SLAVE_MYPORT SLAVE_PORT
+eval
+CREATE TABLE federated.t1 (
+  a varchar(100) NOT NULL default '123'
+)
+ENGINE="FEDERATED" DEFAULT CHARSET=latin1
+CONNECTION='mysql://root@127.0.0.1:$SLAVE_MYPORT/federated/t1';
+
+
+--replace_result $SLAVE_MYPORT SLAVE_PORT
+eval
+CREATE TABLE federated.t2 (
+  a varchar(100) NOT NULL default '123'
+)
+ENGINE="FEDERATED" DEFAULT CHARSET=latin1
+CONNECTION='mysql://root@127.0.0.1:$SLAVE_MYPORT/federated/t2';
+
+SELECT COUNT(DISTINCT a) FROM federated.t1;
+
+INSERT INTO federated.t2
+  SELECT * FROM (SELECT * FROM federated.t1 LIMIT 100) dt;
+SELECT COUNT(DISTINCT a) FROM federated.t2;
+
+TRUNCATE TABLE federated.t2;
+INSERT INTO federated.t2
+  SELECT * FROM (SELECT * FROM federated.t1 LIMIT 70000) dt;
+SELECT COUNT(DISTINCT a) FROM federated.t2;
+
+--echo #
+--echo # MDEV-29640 FederatedX does not properly handle pushdown
+--echo #             in case of difference in local and remote table names
+--echo #
+connection master;
+--echo # Use tables from the previous test. Make sure pushdown works:
+EXPLAIN SELECT COUNT(DISTINCT a) FROM federated.t2;
+SELECT COUNT(DISTINCT a) FROM federated.t2;
+
+--echo # Link remote table `federated.t1` with the local table named `t1_local`
+--replace_result $SLAVE_MYPORT SLAVE_PORT
+eval
+CREATE TABLE federated.t1_local ENGINE="FEDERATED"
+CONNECTION='mysql://root@127.0.0.1:$SLAVE_MYPORT/federated/t1';
+
+--echo # No pushdown here due to table names mismatch, retrieve data as usual:
+EXPLAIN SELECT COUNT(DISTINCT a) FROM federated.t1_local;
+SELECT COUNT(DISTINCT a) FROM federated.t1_local;
+
+
+--echo #
+--echo # MDEV-29863 Server crashes in federatedx_txn::acquire after select from
+--echo #            the Federated table with partitions and federated_pushdown=1
+--echo #            in case of difference in local and remote table names
+--echo #
+connection slave;
+CREATE TABLE federated.t3 (a INT);
+INSERT INTO federated.t3 VALUES (1),(2),(3);
+CREATE TABLE federated.t4 (a INT);
+
+connection master;
+--replace_result $SLAVE_MYPORT SLAVE_PORT
+eval CREATE SERVER fedlink FOREIGN DATA WRAPPER mysql 
+  OPTIONS (USER 'root', HOST '127.0.0.1', DATABASE 'federated',
+  PORT $SLAVE_MYPORT);
+
+CREATE TABLE federated.t3 (a INT)
+  ENGINE=FEDERATED
+  CONNECTION='mysql://root@127.0.0.1:$SLAVE_MYPORT/federated/t3'
+  PARTITION BY list (a)
+  (PARTITION p1 VALUES IN (1) CONNECTION='fedlink/t3',
+   PARTITION p2 VALUES IN (2) CONNECTION='fedlink/t4');
+
+EXPLAIN SELECT * FROM federated.t3;
+SELECT * FROM federated.t3;
+
+--echo #
+--echo # MDEV-29655: ASAN heap-use-after-free in
+--echo #             Pushdown_derived::Pushdown_derived
+--echo #
+
+connection slave;
+DROP TABLE IF EXISTS federated.t1;
+
+CREATE TABLE federated.t1 (
+  id int(20) NOT NULL,
+  name varchar(16) NOT NULL default ''
+)
+DEFAULT CHARSET=latin1;
+
+INSERT INTO federated.t1 VALUES
+  (3,'xxx'), (7,'yyy'), (4,'xxx'), (1,'zzz'), (5,'yyy');
+
+connection master;
+DROP TABLE IF EXISTS federated.t1;
+
+--replace_result $SLAVE_MYPORT SLAVE_PORT
+eval
+CREATE TABLE federated.t1 (
+  id int(20) NOT NULL,
+  name varchar(16) NOT NULL default ''
+)
+ENGINE="FEDERATED" DEFAULT CHARSET=latin1
+CONNECTION='mysql://root@127.0.0.1:$SLAVE_MYPORT/federated/t1';
+
+use federated;
+SELECT * FROM (SELECT * FROM (SELECT * FROM (SELECT * FROM t1 where id=3) dt3
+  WHERE id=2) dt2) dt;
+
+connection slave;
+CREATE TABLE federated.t10 (a INT,b INT);
+CREATE TABLE federated.t11 (a INT, b INT);
+INSERT INTO federated.t10 VALUES (1,1),(2,2);
+INSERT INTO federated.t11 VALUES (1,1),(2,2);
+
+connection master;
+--replace_result $SLAVE_MYPORT SLAVE_PORT
+eval
+CREATE TABLE federated.t10
+ENGINE="FEDERATED" DEFAULT CHARSET=latin1
+CONNECTION='mysql://root@127.0.0.1:$SLAVE_MYPORT/federated/t10';
+
+--replace_result $SLAVE_MYPORT SLAVE_PORT
+eval
+CREATE TABLE federated.t11
+ENGINE="FEDERATED" DEFAULT CHARSET=latin1
+CONNECTION='mysql://root@127.0.0.1:$SLAVE_MYPORT/federated/t11';
+
+use federated;
+SELECT * FROM t10 LEFT JOIN 
+  (t11, (SELECT * FROM (SELECT * FROM (SELECT * FROM t1 where id=3) dt3
+    WHERE id=2) dt2) dt
+  ) ON t10.a=t11.a;
+
+set global federated_pushdown=0;
+
+source include/federated_cleanup.inc;
+