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