path: root/mysql-test/suite/federated/federatedx_create_handlers.result

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	1	
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,
    "cost": "COST_REPLACED",
    "nested_loop": [
      {
        "table": {
          "table_name": "t3",
          "access_type": "ALL",
          "loops": 1,
          "rows": 7,
          "cost": "COST_REPLACED",
          "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"],
          "loops": 7,
          "rows": 1,
          "cost": "COST_REPLACED",
          "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	1	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	1	
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,
    "cost": "REPLACED",
    "r_loops": 1,
    "r_total_time_ms": "REPLACED",
    "nested_loop": [
      {
        "table": {
          "table_name": "t3",
          "access_type": "ALL",
          "loops": 1,
          "r_loops": 1,
          "rows": 7,
          "r_rows": 7,
          "cost": "REPLACED",
          "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"],
          "loops": 7,
          "r_loops": 7,
          "rows": 1,
          "r_rows": 0.142857143,
          "cost": "REPLACED",
          "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	key1,distinct_key	key1	18	federated.t3.name	1	
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,
    "cost": "COST_REPLACED",
    "nested_loop": [
      {
        "table": {
          "table_name": "t5",
          "access_type": "ALL",
          "loops": 1,
          "rows": 2,
          "cost": "COST_REPLACED",
          "filtered": 100
        }
      },
      {
        "block-nl-join": {
          "table": {
            "table_name": "<derived2>",
            "access_type": "ALL",
            "loops": 2,
            "rows": 5,
            "cost": "COST_REPLACED",
            "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 mysql 
OPTIONS (USER 'root', HOST '127.0.0.1', DATABASE 'federated',
PORT SLAVE_PORT);
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;
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	PUSHED SELECT	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;
DROP TABLES federated.t1, federated.t2, federated.t3, federated.t10,
federated.t11;
connection slave;
DROP TABLES federated.t1, federated.t2, federated.t3, federated.t10,
federated.t11;
# MDEV-25080: Allow pushdown of queries involving UNIONs
#   in outer select to foreign engines
#
connection slave;
CREATE TABLE federated.t1 (
a varchar(10)
)
DEFAULT CHARSET=latin1;
CREATE TABLE federated.t2 (
a varchar(16) NOT NULL default ''
)
DEFAULT CHARSET=latin1;
INSERT INTO federated.t1 VALUES ('bcd'), ('abc'), ('cde');
INSERT INTO federated.t2 VALUES ('cde'), ('efg'), ('abc'), ('bcd'), ('def');
connection master;
CREATE TABLE federated.t1 (
a varchar(10)
)
ENGINE="FEDERATED" DEFAULT CHARSET=latin1
CONNECTION='mysql://root@127.0.0.1:SLAVE_PORT/federated/t1';
CREATE TABLE federated.t2 (
a varchar(16) NOT NULL default ''
)
ENGINE="FEDERATED" DEFAULT CHARSET=latin1
CONNECTION='mysql://root@127.0.0.1:SLAVE_PORT/federated/t2';
CREATE TABLE t3 (a varchar(30)) ENGINE=MyISAM;
CREATE TABLE t4 (a varchar(30)) ENGINE=MyISAM;
INSERT INTO t3 VALUES ('t3_myisam1'), ('t3_myisam2'), ('t3_myisam3');
INSERT INTO t4 VALUES ('t4_myisam1'), ('t4_myisam2'), ('t4_myisam3');
# Pushdown of the whole UNION
SELECT * from federated.t1 UNION SELECT * from federated.t2;
a
abc
bcd
cde
def
efg
EXPLAIN SELECT * from federated.t1 UNION SELECT * from federated.t2;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
NULL	PUSHED UNION	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
# Pushdown of a part of the UNION
SELECT * from federated.t1 UNION SELECT * from t3;
a
abc
bcd
cde
t3_myisam1
t3_myisam2
t3_myisam3
EXPLAIN SELECT * from federated.t1 UNION SELECT * from t3;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PUSHED SELECT	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
2	UNION	t3	ALL	NULL	NULL	NULL	NULL	3	
NULL	UNION RESULT	<union1,2>	ALL	NULL	NULL	NULL	NULL	NULL	
SELECT * from federated.t1 UNION ALL SELECT * from federated.t2;
a
abc
abc
bcd
bcd
cde
cde
def
efg
EXPLAIN SELECT * from federated.t1 UNION ALL SELECT * from federated.t2;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
NULL	PUSHED UNION	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
EXPLAIN FORMAT=JSON SELECT * from federated.t1 UNION ALL
SELECT * from federated.t2;
EXPLAIN
{
  "query_block": {
    "union_result": {
      "message": "PUSHED UNION"
    }
  }
}
ANALYZE SELECT * from federated.t1 UNION ALL SELECT * from federated.t2;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	r_rows	filtered	r_filtered	Extra
NULL	PUSHED UNION	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
ANALYZE FORMAT=JSON SELECT * from federated.t1 UNION ALL
SELECT * from federated.t2;
ANALYZE
{
  "query_optimization": {
    "r_total_time_ms": "REPLACED"
  },
  "query_block": {
    "union_result": {
      "message": "PUSHED UNION"
    }
  }
}
SELECT * from federated.t1 EXCEPT SELECT * from federated.t2;
a
EXPLAIN EXTENDED SELECT * from federated.t1 EXCEPT
SELECT * from federated.t2;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
NULL	PUSHED EXCEPT	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
Warnings:
Note	1003	/* select#1 */ select `federated`.`t1`.`a` AS `a` from `federated`.`t1` except /* select#2 */ select `federated`.`t2`.`a` AS `a` from `federated`.`t2`
EXPLAIN FORMAT=JSON SELECT * from federated.t1 EXCEPT
SELECT * from federated.t2;
EXPLAIN
{
  "query_block": {
    "union_result": {
      "message": "PUSHED EXCEPT"
    }
  }
}
SELECT * from federated.t1 INTERSECT SELECT * from federated.t2;
a
abc
bcd
cde
EXPLAIN PARTITIONS SELECT * from federated.t1 INTERSECT
SELECT * from federated.t2;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
NULL	PUSHED INTERSECT	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
EXPLAIN FORMAT=JSON SELECT * from federated.t1 INTERSECT
SELECT * from federated.t2;
EXPLAIN
{
  "query_block": {
    "union_result": {
      "message": "PUSHED INTERSECT"
    }
  }
}
# More than two SELECTs in a UNIT:
SELECT * from federated.t1 INTERSECT
SELECT * from federated.t2 UNION ALL
SELECT * from federated.t2 EXCEPT
SELECT * from federated.t1;
a
def
efg
EXPLAIN
SELECT count(*) from federated.t1 INTERSECT
SELECT count(*) from federated.t2 UNION ALL
SELECT count(*)+20 from federated.t2 EXCEPT
SELECT count(*)+5 from federated.t1;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
NULL	PUSHED UNIT	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
EXPLAIN FORMAT=JSON
SELECT count(*) from federated.t1 INTERSECT
SELECT count(*) from federated.t2 UNION ALL
SELECT count(*)+20 from federated.t2 EXCEPT
SELECT count(*)+5 from federated.t1;
EXPLAIN
{
  "query_block": {
    "union_result": {
      "message": "PUSHED UNIT"
    }
  }
}
ANALYZE
SELECT count(*) from federated.t1 INTERSECT
SELECT count(*) from federated.t2 UNION
SELECT count(*)+20 from federated.t2 EXCEPT
SELECT count(*)+5 from federated.t1;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	r_rows	filtered	r_filtered	Extra
NULL	PUSHED UNIT	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
# UNION inside a derived table: the whole derived table must be pushed
SELECT * FROM 
(SELECT * FROM federated.t1 UNION ALL SELECT * FROM federated.t2) q;
a
abc
abc
bcd
bcd
cde
cde
def
efg
EXPLAIN 
SELECT * FROM 
(SELECT a FROM federated.t1 UNION ALL SELECT * FROM federated.t2) q;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PUSHED SELECT	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
# There is an uncacheable side effect due to fetch into @var,
# so the UNION cannot be pushed down as a whole.
# But separate SELECTs can be pushed, and the results are combined
# at the server side
SELECT count(*) FROM federated.t1 UNION
SELECT count(*) FROM federated.t1 EXCEPT
SELECT count(*)+1 FROM federated.t1
INTO @var;
EXPLAIN SELECT count(*) FROM federated.t1 UNION
SELECT count(*) FROM federated.t2 EXCEPT
SELECT count(*)+1 FROM federated.t1
INTO @var;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PUSHED SELECT	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
2	PUSHED SELECT	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
3	PUSHED SELECT	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
NULL	UNIT RESULT	<unit1,2,3>	ALL	NULL	NULL	NULL	NULL	NULL	
EXPLAIN FORMAT=JSON SELECT count(*) FROM federated.t1 UNION
SELECT count(*) FROM federated.t2 EXCEPT
SELECT count(*)+2 FROM federated.t2
INTO @var;
EXPLAIN
{
  "query_block": {
    "union_result": {
      "table_name": "<unit1,2,3>",
      "access_type": "ALL",
      "query_specifications": [
        {
          "query_block": {
            "select_id": 1,
            "table": {
              "message": "Pushed select"
            }
          }
        },
        {
          "query_block": {
            "select_id": 2,
            "operation": "UNION",
            "table": {
              "message": "Pushed select"
            }
          }
        },
        {
          "query_block": {
            "select_id": 3,
            "operation": "EXCEPT",
            "table": {
              "message": "Pushed select"
            }
          }
        }
      ]
    }
  }
}
# Prepared statements
PREPARE stmt FROM "SELECT * from federated.t1 INTERSECT
  SELECT * from federated.t2 UNION ALL
  SELECT * from federated.t2 EXCEPT
  SELECT * from federated.t1";
EXECUTE stmt;
a
def
efg
EXECUTE stmt;
a
def
efg
EXECUTE stmt;
a
def
efg
PREPARE stmt FROM "EXPLAIN SELECT * from federated.t1 INTERSECT
  SELECT * from federated.t2 UNION ALL
  SELECT * from federated.t2 EXCEPT
  SELECT * from federated.t1";
EXECUTE stmt;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
NULL	PUSHED UNIT	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
EXECUTE stmt;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
NULL	PUSHED UNIT	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
# UNIONs of mixed Federated/MyISAM tables, pushing parts of UNIONs
SELECT * FROM federated.t1 UNION SELECT * FROM t3;
a
abc
bcd
cde
t3_myisam1
t3_myisam2
t3_myisam3
EXPLAIN SELECT * FROM federated.t1 UNION SELECT * FROM t3;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PUSHED SELECT	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
2	UNION	t3	ALL	NULL	NULL	NULL	NULL	3	
NULL	UNION RESULT	<union1,2>	ALL	NULL	NULL	NULL	NULL	NULL	
SELECT * FROM federated.t1 UNION ALL
SELECT * FROM t3 EXCEPT
SELECT * FROM federated.t2;
a
t3_myisam1
t3_myisam2
t3_myisam3
EXPLAIN SELECT * FROM federated.t1 UNION ALL
SELECT * FROM t3 EXCEPT
SELECT * 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
2	UNION	t3	ALL	NULL	NULL	NULL	NULL	3	
3	PUSHED SELECT	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
NULL	UNIT RESULT	<unit1,2,3>	ALL	NULL	NULL	NULL	NULL	NULL	
SELECT * FROM t3 UNION ALL
SELECT * FROM federated.t1 EXCEPT
SELECT * FROM t4 INTERSECT
SELECT * FROM federated.t2;
a
abc
bcd
cde
t3_myisam1
t3_myisam2
t3_myisam3
EXPLAIN SELECT * FROM t3 UNION ALL
SELECT * FROM federated.t1 EXCEPT
SELECT * FROM t4 INTERSECT
SELECT * FROM federated.t2;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	3	
2	PUSHED SELECT	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
5	EXCEPT	<derived3>	ALL	NULL	NULL	NULL	NULL	3	
3	DERIVED	t4	ALL	NULL	NULL	NULL	NULL	3	
4	INTERSECT	t2	ALL	NULL	NULL	NULL	NULL	5	
NULL	INTERSECT RESULT	<intersect3,4>	ALL	NULL	NULL	NULL	NULL	NULL	
NULL	UNIT RESULT	<unit1,2,5>	ALL	NULL	NULL	NULL	NULL	NULL	
SELECT * FROM federated.t2 UNION ALL
SELECT * FROM t3 EXCEPT
SELECT * FROM t4 INTERSECT
SELECT * FROM federated.t1;
a
abc
bcd
cde
def
efg
t3_myisam1
t3_myisam2
t3_myisam3
EXPLAIN SELECT * FROM federated.t2 UNION ALL
SELECT * FROM t3 EXCEPT
SELECT * FROM t4 INTERSECT
SELECT * FROM federated.t1;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PUSHED SELECT	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
2	UNION	t3	ALL	NULL	NULL	NULL	NULL	3	
5	EXCEPT	<derived3>	ALL	NULL	NULL	NULL	NULL	3	
3	DERIVED	t4	ALL	NULL	NULL	NULL	NULL	3	
4	INTERSECT	t1	ALL	NULL	NULL	NULL	NULL	3	
NULL	INTERSECT RESULT	<intersect3,4>	ALL	NULL	NULL	NULL	NULL	NULL	
NULL	UNIT RESULT	<unit1,2,5>	ALL	NULL	NULL	NULL	NULL	NULL	
# Parenthesis must not prevent the whole UNIONs pushdown
EXPLAIN (SELECT * FROM federated.t1 UNION
SELECT * FROM federated.t2) UNION ALL
SELECT * FROM federated.t1;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
NULL	PUSHED UNION	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
(SELECT * FROM federated.t1 UNION
SELECT * FROM federated.t2) UNION ALL
SELECT * FROM federated.t1;
a
abc
abc
bcd
bcd
cde
cde
def
efg
EXPLAIN (SELECT * FROM federated.t1 UNION SELECT * FROM federated.t2)
UNION ALL (SELECT * FROM federated.t1 UNION SELECT * FROM federated.t2);
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
NULL	PUSHED UNION	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
(SELECT * FROM federated.t1 UNION SELECT * FROM federated.t2) UNION ALL
(SELECT * FROM federated.t1 UNION SELECT * FROM federated.t2);
a
abc
abc
bcd
bcd
cde
cde
def
def
efg
efg
# Union of tables containing different INT data types
connection slave;
CREATE TABLE federated.t11 (a smallint(6) NOT NULL);
INSERT INTO federated.t11 VALUES (-32678), (-1), (0);
CREATE TABLE federated.t12 (a int(10) UNSIGNED NOT NULL);
INSERT INTO federated.t12 VALUES (0), (1), (32767);
connection master;
CREATE TABLE federated.t11 (a smallint(6) NOT NULL)
ENGINE="FEDERATED"
CONNECTION='mysql://root@127.0.0.1:SLAVE_PORT/federated/t11';
CREATE TABLE federated.t12 (a int(10) UNSIGNED NOT NULL)
ENGINE="FEDERATED"
CONNECTION='mysql://root@127.0.0.1:SLAVE_PORT/federated/t12';
# Entire UNION pushdown
SELECT a FROM federated.t12 UNION ALL SELECT a FROM federated.t11;
a
-1
-32678
0
0
1
32767
EXPLAIN SELECT a FROM federated.t12 UNION ALL SELECT a FROM federated.t11;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
NULL	PUSHED UNION	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
SELECT a FROM federated.t11 UNION SELECT a FROM federated.t12;
a
-1
-32678
0
1
32767
# Partial pushdown of SELECTs composing the UNION
SELECT a FROM federated.t12 UNION SELECT a FROM federated.t11 UNION SELECT 123;
a
-1
-32678
0
1
123
32767
EXPLAIN
SELECT a FROM federated.t12 UNION SELECT a FROM federated.t11
UNION SELECT 123;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PUSHED SELECT	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
2	PUSHED SELECT	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
3	UNION	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
NULL	UNION RESULT	<union1,2,3>	ALL	NULL	NULL	NULL	NULL	NULL	
SELECT a FROM federated.t12 EXCEPT
SELECT 1 UNION ALL
SELECT a FROM federated.t11 EXCEPT
SELECT 0;
a
-1
-32678
32767
# Union of tables containing different string data types
connection slave;
CREATE TABLE federated.t13 (a CHAR(6));
INSERT INTO federated.t13 VALUES ('t13abc'), ('t13xx'), ('common');
CREATE TABLE federated.t14 (a VARCHAR(8));
INSERT INTO federated.t14 VALUES ('t14abcde'), ('t14xyzzz'), ('common');
connection master;
CREATE TABLE federated.t13 (a CHAR(6))
ENGINE="FEDERATED"
CONNECTION='mysql://root@127.0.0.1:SLAVE_PORT/federated/t13';
CREATE TABLE federated.t14 (a VARCHAR(8))
ENGINE="FEDERATED"
CONNECTION='mysql://root@127.0.0.1:SLAVE_PORT/federated/t14';
SELECT * FROM federated.t13 UNION SELECT * FROM federated.t14;
a
common
t13abc
t13xx
t14abcde
t14xyzzz
EXPLAIN SELECT * FROM federated.t13 UNION SELECT * FROM federated.t14;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
NULL	PUSHED UNION	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
SELECT * FROM federated.t14 UNION ALL SELECT * FROM federated.t13;
a
common
common
t13abc
t13xx
t14abcde
t14xyzzz
SELECT * FROM federated.t14 UNION
SELECT * FROM federated.t13 UNION
SELECT '123456789000';
a
t14abcde
t14xyzzz
common
t13abc
t13xx
123456789000
EXPLAIN SELECT * FROM federated.t14 UNION
SELECT * FROM federated.t13 UNION
SELECT '123456789000';
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PUSHED SELECT	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
2	PUSHED SELECT	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
3	UNION	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
NULL	UNION RESULT	<union1,2,3>	ALL	NULL	NULL	NULL	NULL	NULL	
SELECT * FROM federated.t13 UNION
SELECT '123456789000' UNION
SELECT * FROM federated.t14;
a
123456789000
common
t13abc
t13xx
t14abcde
t14xyzzz
# CREATE TABLE .. AS from a pushed UNION
CREATE TABLE t5 AS SELECT * FROM federated.t13 UNION
SELECT * FROM federated.t14;
SHOW CREATE TABLE t5;
Table	Create Table
t5	CREATE TABLE `t5` (
  `a` varchar(8) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
SELECT * FROM t5;
a
common
t13abc
t13xx
t14abcde
t14xyzzz
CREATE TABLE t6 AS SELECT a FROM federated.t12 EXCEPT
SELECT 1 UNION ALL
SELECT a FROM federated.t11 EXCEPT
SELECT 0;
SHOW CREATE TABLE t6;
Table	Create Table
t6	CREATE TABLE `t6` (
  `a` decimal(10,0) NOT NULL DEFAULT 0
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
SELECT * FROM t6;
a
-1
-32678
32767
#
# MDEV-30828 ORDER BY clause using an integer (positional argument)
#
SELECT a FROM federated.t1 UNION SELECT a FROM federated.t2 ORDER BY 1;
a
abc
bcd
cde
def
efg
SELECT a FROM federated.t1 UNION ALL SELECT a FROM federated.t2 ORDER BY a DESC;
a
efg
def
cde
cde
bcd
bcd
abc
abc
# Check handling of incorrect ORDER BY clause
SELECT a FROM federated.t1 UNION SELECT a FROM federated.t2 ORDER BY 2;
ERROR 42S22: Unknown column '2' in 'order clause'
PREPARE stmt FROM
"SELECT a FROM federated.t1 UNION ALL SELECT a FROM federated.t2 ORDER BY 2";
ERROR 42S22: Unknown column '2' in 'order clause'
SELECT a FROM federated.t1 UNION ALL SELECT a FROM federated.t2 ORDER BY 2,1,3;
ERROR 42S22: Unknown column '2' in 'order clause'
SELECT a FROM federated.t1 UNION ALL SELECT a FROM federated.t2 ORDER BY t1.a;
ERROR 42000: Table 't1' from one of the SELECTs cannot be used in ORDER clause
SELECT * from federated.t1 INTERSECT
SELECT * from federated.t2 UNION ALL
SELECT * from federated.t2 EXCEPT
SELECT * from federated.t1
ORDER BY 1;
a
def
efg
SELECT * from federated.t1 INTERSECT
SELECT * from federated.t2 UNION ALL
SELECT * from federated.t2 EXCEPT
SELECT * from federated.t1
ORDER BY 3;
ERROR 42S22: Unknown column '3' in 'order clause'
# UNION of mixed Federated/MyISAM tables, pushing parts of UNIONs
SELECT * FROM federated.t1 UNION SELECT * FROM t3 ORDER BY a;
a
abc
bcd
cde
t3_myisam1
t3_myisam2
t3_myisam3
SELECT * FROM federated.t1 UNION SELECT * FROM t3 ORDER BY 2;
ERROR 42S22: Unknown column '2' in 'order clause'
#
# MDEV-32382 FederatedX error on pushdown of statement having CTE
#
# Single SELECT with CTE
WITH cte AS (SELECT * FROM federated.t1)
SELECT * FROM cte;
a
bcd
abc
cde
explain extended WITH cte AS (SELECT * FROM federated.t1)
SELECT * FROM cte;
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	with cte as (/* select#2 */ select `federated`.`t1`.`a` AS `a` from `federated`.`t1`)/* select#1 */ select `cte`.`a` AS `a` from `cte`
# Pushdown of a UNION having CTE's
WITH cte AS (SELECT * FROM federated.t1),
cte2 AS (SELECT * FROM federated.t2)
SELECT * FROM cte
UNION
SELECT * FROM cte2;
a
abc
bcd
cde
def
efg
explain extended WITH cte AS (SELECT * FROM federated.t1),
cte2 AS (SELECT * FROM federated.t2)
SELECT * FROM cte
UNION
SELECT * FROM cte2;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
NULL	PUSHED UNION	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
Warnings:
Note	1003	with cte as (/* select#2 */ select `federated`.`t1`.`a` AS `a` from `federated`.`t1`), cte2 as (/* select#3 */ select `federated`.`t2`.`a` AS `a` from `federated`.`t2`)/* select#1 */ select `cte`.`a` AS `a` from `cte` union /* select#4 */ select `cte2`.`a` AS `a` from `cte2`
# Partial pushdown is not allowed for unions with CTE's, however a CTE
# may be pushed down a derived table
WITH cte AS (SELECT * FROM federated.t1)
SELECT * FROM cte
UNION ALL
SELECT * FROM t3;
a
abc
bcd
cde
t3_myisam1
t3_myisam2
t3_myisam3
explain extended WITH cte AS (SELECT * FROM federated.t1)
SELECT * FROM cte
UNION ALL
SELECT * FROM t3;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	PRIMARY	<derived2>	ALL	NULL	NULL	NULL	NULL	3	100.00	
2	PUSHED DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
3	UNION	t3	ALL	NULL	NULL	NULL	NULL	3	100.00	
Warnings:
Note	1003	with cte as (/* select#2 */ select `federated`.`t1`.`a` AS `a` from `federated`.`t1`)/* select#1 */ select `cte`.`a` AS `a` from `cte` union all /* select#3 */ select `federated`.`t3`.`a` AS `a` from `federated`.`t3`
WITH cte AS (SELECT * FROM federated.t1 UNION SELECT * FROM t3)
SELECT * FROM cte;
a
abc
bcd
cde
t3_myisam1
t3_myisam2
t3_myisam3
explain extended WITH cte AS (SELECT * FROM federated.t1 UNION SELECT * FROM t3)
SELECT * FROM cte;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	PRIMARY	<derived2>	ALL	NULL	NULL	NULL	NULL	6	100.00	
2	DERIVED	t1	ALL	NULL	NULL	NULL	NULL	3	100.00	
3	UNION	t3	ALL	NULL	NULL	NULL	NULL	3	100.00	
NULL	UNION RESULT	<union2,3>	ALL	NULL	NULL	NULL	NULL	NULL	NULL	
Warnings:
Note	1003	with cte as (/* select#2 */ select `federated`.`t1`.`a` AS `a` from `federated`.`t1` union /* select#3 */ select `federated`.`t3`.`a` AS `a` from `federated`.`t3`)/* select#1 */ select `cte`.`a` AS `a` from `cte`
# Two CTE's where one CTE refers to another
WITH cte AS (SELECT * FROM federated.t1),
cte2 AS (SELECT * FROM t3
WHERE t3.a NOT IN (SELECT * FROM cte))
SELECT * FROM cte JOIN cte2;
a	a
abc	t3_myisam1
abc	t3_myisam2
abc	t3_myisam3
bcd	t3_myisam1
bcd	t3_myisam2
bcd	t3_myisam3
cde	t3_myisam1
cde	t3_myisam2
cde	t3_myisam3
explain extended WITH cte AS (SELECT * FROM federated.t1),
cte2 AS (SELECT * FROM t3
WHERE t3.a NOT IN (SELECT * FROM cte))
SELECT * FROM cte JOIN cte2;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	PRIMARY	<derived5>	ALL	NULL	NULL	NULL	NULL	3	100.00	
1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where; Using join buffer (flat, BNL join)
5	PUSHED DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
4	MATERIALIZED	<derived2>	ALL	NULL	NULL	NULL	NULL	3	100.00	
2	PUSHED DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
Warnings:
Note	1003	with cte as (/* select#2 */ select `federated`.`t1`.`a` AS `a` from `federated`.`t1`), cte2 as (/* select#3 */ select `federated`.`t3`.`a` AS `a` from `federated`.`t3` where !<expr_cache><`federated`.`t3`.`a`>(<in_optimizer>(`federated`.`t3`.`a`,`federated`.`t3`.`a` in ( <materialize> (/* select#4 */ select `cte`.`a` from `cte` ), <primary_index_lookup>(`federated`.`t3`.`a` in <temporary table> on distinct_key where `federated`.`t3`.`a` = `<subquery4>`.`a`)))))/* select#1 */ select `cte`.`a` AS `a`,`federated`.`t3`.`a` AS `a` from `cte` join `federated`.`t3` where !<expr_cache><`federated`.`t3`.`a`>(<in_optimizer>(`federated`.`t3`.`a`,`federated`.`t3`.`a` in ( <materialize> (/* select#4 */ select `cte`.`a` from `cte` ), <primary_index_lookup>(`federated`.`t3`.`a` in <temporary table> on distinct_key where `federated`.`t3`.`a` = `<subquery4>`.`a`))))
connection master;
DROP TABLES federated.t1, federated.t2, t3, t4, t5, t6, federated.t11,
federated.t12, federated.t13, federated.t14;
connection slave;
DROP TABLES federated.t1, federated.t2, federated.t11, federated.t12,
federated.t13, federated.t14;
connection default;
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;