mirror of
https://github.com/MariaDB/server.git
synced 2025-10-31 10:56:12 +01:00
591 lines
15 KiB
Text
591 lines
15 KiB
Text
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_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,
|
|
"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_block": {
|
|
"select_id": 1,
|
|
"r_loops": 1,
|
|
"r_total_time_ms": "REPLACED",
|
|
"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,
|
|
"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 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 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;
|