mirror of
https://github.com/MariaDB/server.git
synced 2025-04-13 02:35:32 +02:00
6fadbf8ebf
This bug led to wrong result sets returned by the second execution of prepared statements from selects using mergeable derived tables pushed into external engine. Such derived tables are always materialized. The decision that they have to be materialized is taken late in the function mysql_derived_optimized(). For regular derived tables this decision is usually taken at the prepare phase. However in some cases for some derived tables this decision is made in mysql_derived_optimized() too. It can be seen in the code of mysql_derived_fill() that for such a derived table it's critical to change its translation table to tune it to the fields of the temporary table used for materialization of the derived table and this must be done after each refill of the derived table. The same actions are needed for derived tables pushed into external engines. Approved by Oleksandr Byelkin <sanja@mariadb.com>
590 lines
15 KiB
Text
590 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",
|
|
"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;
|