mirror/mariadb
1
0
Fork 0
mirror of https://github.com/MariaDB/server.git synced 2025-11-10 15:56:13 +01:00
Code Issues Projects Releases Packages Wiki Activity
mariadb/mysql-test/suite/federated/federatedx_create_handlers.result
Rex 9c01f28c41 MDEV-30073 Wrong result on 2nd execution of PS for query with NOT EXISTS 
This bug is consequence of a serious architectural flaw of the query
processing at the prepare stage. Context analysis of processed queries has
to be done at this stage. The important part of this analysis is resolution
of column references also known as name resolution. The items for column
references used in a query are created by the parser. They are allocated
in the statement memory because their life span is the same as the life
span of the executed query. During name resolution some of the items are
wrapped into objects belonging to classes derived from the Item_ref class.
In many cases we can't do without such wrappers. For example, the parser
does not differentiate between references to columns of base tables and
references to columns of views. However for a reference to a view column
we need a pointer to the translation item for this column. This pointer
is stored in a Item_direct_view_ref object that wraps the item for the
column reference. Before this patch the wrappers were allocated in
execution memory good only for one execution. That wasn't a problem when
the query was executed only once. Yet for the queries executed as prepared
statements or within a stored procedure it could lead to crashes or wrong
result sets at the second execution of the query. It could happen when
the wrapped item was substituted for something else by permanent
transformations during the optimization phase.

This patch allocates all the wrappers around items created for column
references in the statement memory at the first execution of PS or at the
first call of SP/SF.
2025-04-14 09:21:31 +11:00

1325 lines
37 KiB
Text
Raw Permalink Blame History

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
xxx 4 xxx
yyy 5 yyy
yyy 5 yyy
yyy 5 yyy
yyy 7 yyy
yyy 7 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 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 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 BY'
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 BY'
SELECT a FROM federated.t1 UNION ALL SELECT a FROM federated.t2 ORDER BY 2,1,3;
ERROR 42S22: Unknown column '2' in 'ORDER BY'
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 BY
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 BY'
# 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 BY'
#
# 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;
Reference in a new issue View git blame Copy permalink