connect  master,127.0.0.1,root,,test,$MASTER_MYPORT,;
connect  slave,127.0.0.1,root,,test,$SLAVE_MYPORT,;
connection master;
CREATE DATABASE federated;
connection slave;
CREATE DATABASE federated;
connection default;
set global federated_pushdown=1;
connection slave;
DROP TABLE IF EXISTS federated.t1;
Warnings:
Note	1051	Unknown table '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;
Warnings:
Note	1051	Unknown table '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;
Warnings:
Note	1051	Unknown table 'federated.t1'
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_PORT/federated/t1';
DROP TABLE IF EXISTS federated.t2;
Warnings:
Note	1051	Unknown table 'federated.t2'
CREATE TABLE federated.t2 (
name varchar(16) NOT NULL default ''
)
ENGINE="FEDERATED" DEFAULT CHARSET=latin1
CONNECTION='mysql://root@127.0.0.1:SLAVE_PORT/federated/t2';
SELECT * FROM federated.t1;
id	name
3	xxx
7	yyy
4	xxx
1	zzz
5	yyy
SELECT id FROM federated.t1 WHERE id < 5;
id
3
4
1
SELECT count(*), name FROM federated.t1 WHERE id < 5 GROUP BY name;
count(*)	name
2	xxx
1	zzz
SELECT * FROM federated.t1, federated.t2
WHERE federated.t1.name = federated.t2.name;
id	name	name
7	yyy	yyy
5	yyy	yyy
7	yyy	yyy
5	yyy	yyy
3	xxx	xxx
4	xxx	xxx
7	yyy	yyy
5	yyy	yyy
SELECT * FROM federated.t1 LEFT JOIN federated.t2
ON federated.t1.name = federated.t2.name
WHERE federated.t1.id > 1;
id	name	name
7	yyy	yyy
5	yyy	yyy
7	yyy	yyy
5	yyy	yyy
3	xxx	xxx
4	xxx	xxx
7	yyy	yyy
5	yyy	yyy
SELECT * FROM federated.t1
WHERE id IN (SELECT count(*) FROM federated.t2 GROUP BY name);
id	name
3	xxx
1	zzz
EXPLAIN
SELECT id FROM federated.t1 WHERE id < 5;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PUSHED SELECT	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
EXPLAIN EXTENDED
SELECT id FROM federated.t1 WHERE id < 5;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	PUSHED SELECT	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
Warnings:
Note	1003	select `federated`.`t1`.`id` AS `id` from `federated`.`t1` where `federated`.`t1`.`id` < 5
EXPLAIN FORMAT=JSON
SELECT id FROM federated.t1 WHERE id < 5;
EXPLAIN
{
  "query_block": {
    "select_id": 1,
    "table": {
      "message": "Pushed select"
    }
  }
}
ANALYZE
SELECT id FROM federated.t1 WHERE id < 5;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	r_rows	filtered	r_filtered	Extra
1	PUSHED SELECT	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
ANALYZE FORMAT=JSON
SELECT id FROM federated.t1 WHERE id < 5;
ANALYZE
{
  "query_optimization": {
    "r_total_time_ms": "REPLACED"
  },
  "query_block": {
    "select_id": 1,
    "table": {
      "message": "Pushed select"
    }
  }
}
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');
SELECT *
FROM federated.t3, (SELECT * FROM federated.t1 WHERE id > 3) t
WHERE federated.t3.name=t.name;
name	id	name
yyy	5	yyy
yyy	7	yyy
yyy	5	yyy
yyy	7	yyy
xxx	4	xxx
yyy	5	yyy
yyy	7	yyy
EXPLAIN
SELECT *
FROM federated.t3, (SELECT * FROM federated.t1 WHERE id > 3) t
WHERE federated.t3.name=t.name;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	7	
1	PRIMARY	<derived2>	ref	key0	key0	18	federated.t3.name	2	
2	PUSHED DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
EXPLAIN FORMAT=JSON
SELECT *
FROM federated.t3, (SELECT * FROM federated.t1 WHERE id > 3) t
WHERE federated.t3.name=t.name;
EXPLAIN
{
  "query_block": {
    "select_id": 1,
    "nested_loop": [
      {
        "table": {
          "table_name": "t3",
          "access_type": "ALL",
          "rows": 7,
          "filtered": 100
        }
      },
      {
        "table": {
          "table_name": "<derived2>",
          "access_type": "ref",
          "possible_keys": ["key0"],
          "key": "key0",
          "key_length": "18",
          "used_key_parts": ["name"],
          "ref": ["federated.t3.name"],
          "rows": 2,
          "filtered": 100,
          "materialized": {
            "query_block": {
              "select_id": 2,
              "table": {
                "message": "Pushed derived"
              }
            }
          }
        }
      }
    ]
  }
}
ANALYZE
SELECT *
FROM federated.t3, (SELECT * FROM federated.t1 WHERE id > 3) t
WHERE federated.t3.name=t.name;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	r_rows	filtered	r_filtered	Extra
1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	7	7.00	100.00	100.00	
1	PRIMARY	<derived2>	ref	key0	key0	18	federated.t3.name	2	1.00	100.00	100.00	
2	PUSHED DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
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;
name	name
xxx	xxx
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;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	7	
1	PRIMARY	<derived2>	ref	key0	key0	18	federated.t3.name	2	
2	PUSHED DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
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;
ANALYZE
{
  "query_optimization": {
    "r_total_time_ms": "REPLACED"
  },
  "query_block": {
    "select_id": 1,
    "r_loops": 1,
    "r_total_time_ms": "REPLACED",
    "nested_loop": [
      {
        "table": {
          "table_name": "t3",
          "access_type": "ALL",
          "r_loops": 1,
          "rows": 7,
          "r_rows": 7,
          "r_table_time_ms": "REPLACED",
          "r_other_time_ms": "REPLACED",
          "r_engine_stats": REPLACED,
          "filtered": 100,
          "r_filtered": 100
        }
      },
      {
        "table": {
          "table_name": "<derived2>",
          "access_type": "ref",
          "possible_keys": ["key0"],
          "key": "key0",
          "key_length": "18",
          "used_key_parts": ["name"],
          "ref": ["federated.t3.name"],
          "r_loops": 7,
          "rows": 2,
          "r_rows": 0.142857143,
          "r_table_time_ms": "REPLACED",
          "r_other_time_ms": "REPLACED",
          "filtered": 100,
          "r_filtered": 100,
          "materialized": {
            "query_block": {
              "select_id": 2,
              "table": {
                "message": "Pushed derived"
              }
            }
          }
        }
      }
    ]
  }
}
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;
id	name
5	yyy
7	yyy
5	yyy
7	yyy
5	yyy
7	yyy
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;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	7	
1	PRIMARY	<derived2>	ref	key0	key0	18	federated.t3.name	2	
2	PUSHED DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
#
# MDEV-21887: federatedx crashes on SELECT ... INTO query in select_handler code
#
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;
select * from federated.t4;
id	name
1	zzz
3	xxx
4	xxx
5	yyy
7	yyy
select name into @var from federated.t1 where id=3 limit 1 ;
select @var;
@var
xxx
select name into outfile 'tmp.txt' from federated.t1;
#
# MDEV-22993: Crash on EXPLAIN with PUSHED DOWN SELECT and subquery
#
explain
select * from federated.t1
where name in (select name from federated.t2);
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PUSHED SELECT	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
explain format=json
select * from federated.t1
where name in (select name from federated.t2);
EXPLAIN
{
  "query_block": {
    "select_id": 1,
    "table": {
      "message": "Pushed select"
    }
  }
}
#
# MDEV-22993, testcase #2: EXPLAIN output doesn't make sense when
#   derived table pushdown is used.
#
create table t5 (a int) engine=myisam;
insert into t5 values (1),(2);
# 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;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	t5	ALL	NULL	NULL	NULL	NULL	2	
1	PRIMARY	<derived2>	ALL	NULL	NULL	NULL	NULL	5	Using join buffer (flat, BNL join)
2	PUSHED DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
# 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;
EXPLAIN
{
  "query_block": {
    "select_id": 1,
    "nested_loop": [
      {
        "table": {
          "table_name": "t5",
          "access_type": "ALL",
          "rows": 2,
          "filtered": 100
        }
      },
      {
        "block-nl-join": {
          "table": {
            "table_name": "<derived2>",
            "access_type": "ALL",
            "rows": 5,
            "filtered": 100
          },
          "buffer_type": "flat",
          "buffer_size": "65",
          "join_type": "BNL",
          "materialized": {
            "query_block": {
              "select_id": 2,
              "table": {
                "message": "Pushed derived"
              }
            }
          }
        }
      }
    ]
  }
}
drop table t5;
DROP TABLE federated.t1, federated.t2, federated.t3, federated.t4;
connection slave;
DROP TABLE federated.t1, federated.t2;
connection default;
#
# MDEV-23778: Derived handler used for big derived tables
#
connection slave;
CREATE TABLE federated.t1 (
a varchar(100) NOT NULL default '123'
)
DEFAULT CHARSET=latin1;
CREATE TABLE federated.t2 LIKE federated.t1;
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
$$
connection master;
CREATE TABLE federated.t1 (
a varchar(100) NOT NULL default '123'
)
ENGINE="FEDERATED" DEFAULT CHARSET=latin1
CONNECTION='mysql://root@127.0.0.1:SLAVE_PORT/federated/t1';
CREATE TABLE federated.t2 (
a varchar(100) NOT NULL default '123'
)
ENGINE="FEDERATED" DEFAULT CHARSET=latin1
CONNECTION='mysql://root@127.0.0.1:SLAVE_PORT/federated/t2';
SELECT COUNT(DISTINCT a) FROM federated.t1;
COUNT(DISTINCT a)
70000
INSERT INTO federated.t2
SELECT * FROM (SELECT * FROM federated.t1 LIMIT 100) dt;
SELECT COUNT(DISTINCT a) FROM federated.t2;
COUNT(DISTINCT a)
100
TRUNCATE TABLE federated.t2;
INSERT INTO federated.t2
SELECT * FROM (SELECT * FROM federated.t1 LIMIT 70000) dt;
SELECT COUNT(DISTINCT a) FROM federated.t2;
COUNT(DISTINCT a)
70000
#
# MDEV-29640 FederatedX does not properly handle pushdown
#             in case of difference in local and remote table names
#
connection master;
# Use tables from the previous test. Make sure pushdown works:
EXPLAIN SELECT COUNT(DISTINCT a) FROM federated.t2;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PUSHED SELECT	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
SELECT COUNT(DISTINCT a) FROM federated.t2;
COUNT(DISTINCT a)
70000
# Link remote table `federated.t1` with the local table named `t1_local`
CREATE TABLE federated.t1_local ENGINE="FEDERATED"
CONNECTION='mysql://root@127.0.0.1:SLAVE_PORT/federated/t1';
# No pushdown here due to table names mismatch, retrieve data as usual:
EXPLAIN SELECT COUNT(DISTINCT a) FROM federated.t1_local;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1_local	ALL	NULL	NULL	NULL	NULL	70000	
SELECT COUNT(DISTINCT a) FROM federated.t1_local;
COUNT(DISTINCT a)
70000
#
# MDEV-29863 Server crashes in federatedx_txn::acquire after select from
#            the Federated table with partitions and federated_pushdown=1
#            in case of difference in local and remote table names
#
connection slave;
CREATE TABLE federated.t3 (a INT);
INSERT INTO federated.t3 VALUES (1),(2),(3);
CREATE TABLE federated.t4 (a INT);
connection master;
CREATE SERVER fedlink FOREIGN DATA WRAPPER mariadb
OPTIONS (USER 'root', HOST '127.0.0.1', DATABASE 'federated',
PORT SLAVE_PORT);
CREATE TABLE federated.t3 (a INT)
ENGINE=FEDERATED
CONNECTION='mariadb://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;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t3	ALL	NULL	NULL	NULL	NULL	3	
SELECT * FROM federated.t3;
a
1
2
3
#
# MDEV-29655: ASAN heap-use-after-free in
#             Pushdown_derived::Pushdown_derived
#
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;
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_PORT/federated/t1';
use federated;
SELECT * FROM (SELECT * FROM (SELECT * FROM (SELECT * FROM t1 where id=3) dt3
WHERE id=2) dt2) dt;
id	name
PREPARE stmt FROM "
SELECT * FROM (SELECT * FROM (SELECT * FROM (SELECT * FROM t1 where id=3) dt3
  WHERE id=3) dt2) dt;
";
EXECUTE stmt;
id	name
3	xxx
EXECUTE stmt;
id	name
3	xxx
DEALLOCATE PREPARE stmt;
EXPLAIN
SELECT * FROM (SELECT * FROM (SELECT * FROM (SELECT * FROM t1 where id=3) dt3
WHERE id=3) dt2) dt;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	<derived4>	ALL	NULL	NULL	NULL	NULL	5	Using where
4	PUSHED DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
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;
CREATE TABLE federated.t10
ENGINE="FEDERATED" DEFAULT CHARSET=latin1
CONNECTION='mysql://root@127.0.0.1:SLAVE_PORT/federated/t10';
CREATE TABLE federated.t11
ENGINE="FEDERATED" DEFAULT CHARSET=latin1
CONNECTION='mysql://root@127.0.0.1:SLAVE_PORT/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;
a	b	a	b	id	name
1	1	NULL	NULL	NULL	NULL
2	2	NULL	NULL	NULL	NULL
#
# MDEV-31361: Second execution of PS for query with derived table
#
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;
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_PORT/federated/t1';
use federated;
SELECT * FROM
(SELECT * FROM
(SELECT * FROM
(SELECT * FROM t1 where id>3) dt3
WHERE id>3) dt2
) dt;
id	name
7	yyy
4	xxx
5	yyy
PREPARE stmt FROM "SELECT * FROM
(SELECT * FROM
(SELECT * FROM
(SELECT * FROM t1 where id>3) dt3
WHERE id>3) dt2
) dt";
EXECUTE stmt;
id	name
7	yyy
4	xxx
5	yyy
EXECUTE stmt;
id	name
7	yyy
4	xxx
5	yyy
DEALLOCATE PREPARE stmt;
set global federated_pushdown=0;
connection master;
DROP TABLE IF EXISTS federated.t1;
DROP DATABASE IF EXISTS federated;
connection slave;
DROP TABLE IF EXISTS federated.t1;
DROP DATABASE IF EXISTS federated;