mirror of
https://github.com/MariaDB/server.git
synced 2025-11-18 03:36: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)
443 lines
11 KiB
Text
443 lines
11 KiB
Text
--source have_federatedx.inc
|
|
--source include/federated.inc
|
|
--source include/no_valgrind_without_big.inc
|
|
--source include/have_partition.inc
|
|
|
|
connection default;
|
|
|
|
set global federated_pushdown=1;
|
|
|
|
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');
|
|
|
|
DROP TABLE IF EXISTS 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;
|
|
|
|
--replace_result $SLAVE_MYPORT SLAVE_PORT
|
|
eval
|
|
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_MYPORT/federated/t1';
|
|
|
|
DROP TABLE IF EXISTS federated.t2;
|
|
|
|
--replace_result $SLAVE_MYPORT SLAVE_PORT
|
|
eval
|
|
CREATE TABLE federated.t2 (
|
|
name varchar(16) NOT NULL default ''
|
|
)
|
|
ENGINE="FEDERATED" DEFAULT CHARSET=latin1
|
|
CONNECTION='mysql://root@127.0.0.1:$SLAVE_MYPORT/federated/t2';
|
|
|
|
SELECT * FROM federated.t1;
|
|
|
|
SELECT id FROM federated.t1 WHERE id < 5;
|
|
|
|
SELECT count(*), name FROM federated.t1 WHERE id < 5 GROUP BY name;
|
|
|
|
SELECT * FROM federated.t1, federated.t2
|
|
WHERE federated.t1.name = federated.t2.name;
|
|
|
|
SELECT * FROM federated.t1 LEFT JOIN federated.t2
|
|
ON federated.t1.name = federated.t2.name
|
|
WHERE federated.t1.id > 1;
|
|
|
|
SELECT * FROM federated.t1
|
|
WHERE id IN (SELECT count(*) FROM federated.t2 GROUP BY name);
|
|
|
|
EXPLAIN
|
|
SELECT id FROM federated.t1 WHERE id < 5;
|
|
|
|
EXPLAIN EXTENDED
|
|
SELECT id FROM federated.t1 WHERE id < 5;
|
|
|
|
EXPLAIN FORMAT=JSON
|
|
SELECT id FROM federated.t1 WHERE id < 5;
|
|
|
|
ANALYZE
|
|
SELECT id FROM federated.t1 WHERE id < 5;
|
|
|
|
--source include/analyze-format.inc
|
|
ANALYZE FORMAT=JSON
|
|
SELECT id FROM federated.t1 WHERE id < 5;
|
|
|
|
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');
|
|
|
|
--sorted_result
|
|
SELECT *
|
|
FROM federated.t3, (SELECT * FROM federated.t1 WHERE id > 3) t
|
|
WHERE federated.t3.name=t.name;
|
|
|
|
EXPLAIN
|
|
SELECT *
|
|
FROM federated.t3, (SELECT * FROM federated.t1 WHERE id > 3) t
|
|
WHERE federated.t3.name=t.name;
|
|
|
|
EXPLAIN FORMAT=JSON
|
|
SELECT *
|
|
FROM federated.t3, (SELECT * FROM federated.t1 WHERE id > 3) t
|
|
WHERE federated.t3.name=t.name;
|
|
|
|
ANALYZE
|
|
SELECT *
|
|
FROM federated.t3, (SELECT * FROM federated.t1 WHERE id > 3) t
|
|
WHERE federated.t3.name=t.name;
|
|
|
|
#Check after fix MDEV-31361
|
|
--disable_ps2_protocol
|
|
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;
|
|
--enable_ps2_protocol
|
|
|
|
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;
|
|
|
|
--source include/analyze-format.inc
|
|
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;
|
|
|
|
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;
|
|
|
|
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;
|
|
|
|
--echo #
|
|
--echo # MDEV-21887: federatedx crashes on SELECT ... INTO query in select_handler code
|
|
--echo #
|
|
|
|
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;
|
|
|
|
--sorted_result
|
|
select * from federated.t4;
|
|
|
|
--disable_cursor_protocol
|
|
select name into @var from federated.t1 where id=3 limit 1 ;
|
|
select @var;
|
|
--disable_ps2_protocol
|
|
select name into outfile 'tmp.txt' from federated.t1;
|
|
--enable_ps2_protocol
|
|
--enable_cursor_protocol
|
|
|
|
let $path=`select concat(@@datadir, 'test/tmp.txt')`;
|
|
remove_file $path;
|
|
|
|
--echo #
|
|
--echo # MDEV-22993: Crash on EXPLAIN with PUSHED DOWN SELECT and subquery
|
|
--echo #
|
|
|
|
explain
|
|
select * from federated.t1
|
|
where name in (select name from federated.t2);
|
|
|
|
explain format=json
|
|
select * from federated.t1
|
|
where name in (select name from federated.t2);
|
|
|
|
--echo #
|
|
--echo # MDEV-22993, testcase #2: EXPLAIN output doesn't make sense when
|
|
--echo # derived table pushdown is used.
|
|
--echo #
|
|
|
|
create table t5 (a int) engine=myisam;
|
|
insert into t5 values (1),(2);
|
|
|
|
--echo # 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;
|
|
|
|
--echo # 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;
|
|
|
|
drop table t5;
|
|
|
|
DROP TABLE federated.t1, federated.t2, federated.t3, federated.t4;
|
|
|
|
connection slave;
|
|
DROP TABLE federated.t1, federated.t2;
|
|
|
|
connection default;
|
|
|
|
--echo #
|
|
--echo # MDEV-23778: Derived handler used for big derived tables
|
|
--echo #
|
|
|
|
connection slave;
|
|
|
|
CREATE TABLE federated.t1 (
|
|
a varchar(100) NOT NULL default '123'
|
|
)
|
|
DEFAULT CHARSET=latin1;
|
|
|
|
CREATE TABLE federated.t2 LIKE federated.t1;
|
|
|
|
DELIMITER $$;
|
|
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
|
|
$$
|
|
|
|
DELIMITER ;$$
|
|
|
|
connection master;
|
|
|
|
--replace_result $SLAVE_MYPORT SLAVE_PORT
|
|
eval
|
|
CREATE TABLE federated.t1 (
|
|
a varchar(100) NOT NULL default '123'
|
|
)
|
|
ENGINE="FEDERATED" DEFAULT CHARSET=latin1
|
|
CONNECTION='mysql://root@127.0.0.1:$SLAVE_MYPORT/federated/t1';
|
|
|
|
|
|
--replace_result $SLAVE_MYPORT SLAVE_PORT
|
|
eval
|
|
CREATE TABLE federated.t2 (
|
|
a varchar(100) NOT NULL default '123'
|
|
)
|
|
ENGINE="FEDERATED" DEFAULT CHARSET=latin1
|
|
CONNECTION='mysql://root@127.0.0.1:$SLAVE_MYPORT/federated/t2';
|
|
|
|
SELECT COUNT(DISTINCT a) FROM federated.t1;
|
|
|
|
INSERT INTO federated.t2
|
|
SELECT * FROM (SELECT * FROM federated.t1 LIMIT 100) dt;
|
|
SELECT COUNT(DISTINCT a) FROM federated.t2;
|
|
|
|
TRUNCATE TABLE federated.t2;
|
|
INSERT INTO federated.t2
|
|
SELECT * FROM (SELECT * FROM federated.t1 LIMIT 70000) dt;
|
|
SELECT COUNT(DISTINCT a) FROM federated.t2;
|
|
|
|
--echo #
|
|
--echo # MDEV-29640 FederatedX does not properly handle pushdown
|
|
--echo # in case of difference in local and remote table names
|
|
--echo #
|
|
connection master;
|
|
--echo # Use tables from the previous test. Make sure pushdown works:
|
|
EXPLAIN SELECT COUNT(DISTINCT a) FROM federated.t2;
|
|
SELECT COUNT(DISTINCT a) FROM federated.t2;
|
|
|
|
--echo # Link remote table `federated.t1` with the local table named `t1_local`
|
|
--replace_result $SLAVE_MYPORT SLAVE_PORT
|
|
eval
|
|
CREATE TABLE federated.t1_local ENGINE="FEDERATED"
|
|
CONNECTION='mysql://root@127.0.0.1:$SLAVE_MYPORT/federated/t1';
|
|
|
|
--echo # No pushdown here due to table names mismatch, retrieve data as usual:
|
|
EXPLAIN SELECT COUNT(DISTINCT a) FROM federated.t1_local;
|
|
SELECT COUNT(DISTINCT a) FROM federated.t1_local;
|
|
|
|
|
|
--echo #
|
|
--echo # MDEV-29863 Server crashes in federatedx_txn::acquire after select from
|
|
--echo # the Federated table with partitions and federated_pushdown=1
|
|
--echo # in case of difference in local and remote table names
|
|
--echo #
|
|
connection slave;
|
|
CREATE TABLE federated.t3 (a INT);
|
|
INSERT INTO federated.t3 VALUES (1),(2),(3);
|
|
CREATE TABLE federated.t4 (a INT);
|
|
|
|
connection master;
|
|
--replace_result $SLAVE_MYPORT SLAVE_PORT
|
|
eval CREATE SERVER fedlink FOREIGN DATA WRAPPER mariadb
|
|
OPTIONS (USER 'root', HOST '127.0.0.1', DATABASE 'federated',
|
|
PORT $SLAVE_MYPORT);
|
|
|
|
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;
|
|
SELECT * FROM federated.t3;
|
|
|
|
--echo #
|
|
--echo # MDEV-29655: ASAN heap-use-after-free in
|
|
--echo # Pushdown_derived::Pushdown_derived
|
|
--echo #
|
|
|
|
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;
|
|
|
|
--replace_result $SLAVE_MYPORT SLAVE_PORT
|
|
eval
|
|
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_MYPORT/federated/t1';
|
|
|
|
use federated;
|
|
SELECT * FROM (SELECT * FROM (SELECT * FROM (SELECT * FROM t1 where id=3) dt3
|
|
WHERE id=2) dt2) dt;
|
|
|
|
PREPARE stmt FROM "
|
|
SELECT * FROM (SELECT * FROM (SELECT * FROM (SELECT * FROM t1 where id=3) dt3
|
|
WHERE id=3) dt2) dt;
|
|
";
|
|
EXECUTE stmt;
|
|
EXECUTE stmt;
|
|
DEALLOCATE PREPARE stmt;
|
|
|
|
EXPLAIN
|
|
SELECT * FROM (SELECT * FROM (SELECT * FROM (SELECT * FROM t1 where id=3) dt3
|
|
WHERE id=3) dt2) dt;
|
|
|
|
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;
|
|
--replace_result $SLAVE_MYPORT SLAVE_PORT
|
|
eval
|
|
CREATE TABLE federated.t10
|
|
ENGINE="FEDERATED" DEFAULT CHARSET=latin1
|
|
CONNECTION='mysql://root@127.0.0.1:$SLAVE_MYPORT/federated/t10';
|
|
|
|
--replace_result $SLAVE_MYPORT SLAVE_PORT
|
|
eval
|
|
CREATE TABLE federated.t11
|
|
ENGINE="FEDERATED" DEFAULT CHARSET=latin1
|
|
CONNECTION='mysql://root@127.0.0.1:$SLAVE_MYPORT/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;
|
|
|
|
--echo #
|
|
--echo # MDEV-31361: Second execution of PS for query with derived table
|
|
--echo #
|
|
|
|
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;
|
|
|
|
--replace_result $SLAVE_MYPORT SLAVE_PORT
|
|
eval
|
|
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_MYPORT/federated/t1';
|
|
|
|
use federated;
|
|
|
|
let $q=
|
|
SELECT * FROM
|
|
(SELECT * FROM
|
|
(SELECT * FROM
|
|
(SELECT * FROM t1 where id>3) dt3
|
|
WHERE id>3) dt2
|
|
) dt;
|
|
|
|
eval $q;
|
|
|
|
eval PREPARE stmt FROM "$q";
|
|
EXECUTE stmt;
|
|
EXECUTE stmt;
|
|
DEALLOCATE PREPARE stmt;
|
|
|
|
|
|
set global federated_pushdown=0;
|
|
|
|
source include/federated_cleanup.inc;
|
|
|