revision-id: 21b87437340c403794f100d1af50dcd9c19e123c (mariadb-10.3.18-203-g21b8743) parent(s): fbef428645e01113521824c2eb0a42fe4163699c author: Igor Babaev committer: Igor Babaev timestamp: 2020-04-04 09:24:22 -0700 message: MDEV-21673 Calling stored procedure twice in the same session causes MariaDB to crash This bug could happen only with a stored procedure containing queries with more than one reference to a CTE that used local variables / parameters. This bug was the result of an incomplete merge of the fix for the bug MDEV-17154. The merge covered usage of parameter markers occurred in a CTE that was referenced more than once, but missed coverage of local variables. --- mysql-test/main/cte_nonrecursive.result | 110 ++++++++++++++++++++++++++++++++ mysql-test/main/cte_nonrecursive.test | 103 ++++++++++++++++++++++++++++++ sql/sql_lex.cc | 10 +-- 3 files changed, 219 insertions(+), 4 deletions(-) diff --git a/mysql-test/main/cte_nonrecursive.result b/mysql-test/main/cte_nonrecursive.result index ff5a6f1..1bd731c 100644 --- a/mysql-test/main/cte_nonrecursive.result +++ b/mysql-test/main/cte_nonrecursive.result @@ -1691,3 +1691,113 @@ DROP TABLE test.t; connection default; disconnect con1; # End of 10.2 tests +# +# MDEV-21673: several references to CTE that uses +# local variables / parameters of SP +# +CREATE TABLE t1 (col1 int); +CREATE TABLE t2 (col1 int, col2 date, col3 varchar(16), col4 int); +CREATE TABLE t3 (col1 int, col2 date); +CREATE TABLE t4 (col1 int, col2 date); +INSERT INTO t1 VALUES (3), (7), (9), (1); +INSERT INTO t2 VALUES +(3,'2019-09-01','AAA',2), (7,'2019-10-01','AAA',4), (3,'2019-10-01','AAA',8), +(1,'2019-10-01','BBB',9), (1,'2019-10-01','AAA',4), (1,'2019-10-01','AAA',6); +INSERT INTO t3 VALUES +(4,'2018-10-01'), (6,'2018-10-01'), (4,'2017-10-01'), (7,'2017-10-01'); +INSERT INTO t4 VALUES +(5,'2018-10-01'), (8,'2017-10-01'), (4,'2017-10-01'); +CREATE OR REPLACE PROCEDURE SP1() +BEGIN +DECLARE p_date date; +DECLARE p_var2 varchar(16); +SET p_date='2019-10-01'; +SET p_var2='AAA'; +WITH cte_first(col) AS +( +SELECT DISTINCT col4 +FROM t1, t2 +WHERE t2.col1 = t1.col1 AND t2.col2 = p_date AND t2.col3 = p_var2 +), +cte2 AS +( +SELECT DISTINCT col2 +FROM t3 +WHERE col1 IN ( SELECT col FROM cte_first ) +), +cte3 AS ( +SELECT distinct t4.col1 +FROM cte2, t4 +WHERE t4.col2 = cte2.col2 AND t4.col1 IN ( SELECT col FROM cte_first ) +) +SELECT * FROM cte3; +END| +CREATE PROCEDURE SP2(IN d date) +BEGIN +DECLARE p_var2 varchar(16); +SET p_var2='AAA'; +WITH cte_first(col) AS +( +SELECT DISTINCT col4 +FROM t1, t2 +WHERE t2.col1 = t1.col1 AND t2.col2 = d AND t2.col3 = p_var2 +), +cte2 AS +( +SELECT DISTINCT col2 +FROM t3 +WHERE col1 IN ( SELECT col FROM cte_first ) +), +cte3 AS ( +SELECT distinct t4.col1 +FROM cte2, t4 +WHERE t4.col2 = cte2.col2 AND t4.col1 IN ( SELECT col FROM cte_first ) +) +SELECT * FROM cte3; +END| +CREATE TABLE t AS +SELECT col4 AS col +FROM t1, t2 +WHERE t2.col1 = t1.col1 AND t2.col2 ='2019-10-01' AND t2.col3 = 'AAA'; +SELECT * FROM t; +col +4 +8 +4 +6 +CREATE TABLE tt AS +SELECT col2 +FROM t3 +WHERE col1 IN ( SELECT col FROM t ); +SELECT * FROM tt; +col2 +2018-10-01 +2018-10-01 +2017-10-01 +SELECT t4.col1 +FROM tt, t4 +WHERE t4.col2 = tt.col2 AND t4.col1 IN ( SELECT col FROM t ); +col1 +8 +4 +DROP TABLE t,tt; +CALL SP1(); +col1 +8 +4 +CALL SP1(); +col1 +8 +4 +CALL SP2('2019-10-01'); +col1 +8 +4 +CALL SP2('2019-10-01'); +col1 +8 +4 +DROP PROCEDURE SP1; +DROP PROCEDURE SP2; +DROP TABLE t1,t2,t3,t4; +# End of 10.3 tests diff --git a/mysql-test/main/cte_nonrecursive.test b/mysql-test/main/cte_nonrecursive.test index 41a5b81..00242e3 100644 --- a/mysql-test/main/cte_nonrecursive.test +++ b/mysql-test/main/cte_nonrecursive.test @@ -1202,3 +1202,106 @@ DROP TABLE test.t; --disconnect con1 --echo # End of 10.2 tests + +--echo # +--echo # MDEV-21673: several references to CTE that uses +--echo # local variables / parameters of SP +--echo # + +CREATE TABLE t1 (col1 int); +CREATE TABLE t2 (col1 int, col2 date, col3 varchar(16), col4 int); +CREATE TABLE t3 (col1 int, col2 date); +CREATE TABLE t4 (col1 int, col2 date); +INSERT INTO t1 VALUES (3), (7), (9), (1); +INSERT INTO t2 VALUES + (3,'2019-09-01','AAA',2), (7,'2019-10-01','AAA',4), (3,'2019-10-01','AAA',8), + (1,'2019-10-01','BBB',9), (1,'2019-10-01','AAA',4), (1,'2019-10-01','AAA',6); +INSERT INTO t3 VALUES + (4,'2018-10-01'), (6,'2018-10-01'), (4,'2017-10-01'), (7,'2017-10-01'); +INSERT INTO t4 VALUES + (5,'2018-10-01'), (8,'2017-10-01'), (4,'2017-10-01'); + +DELIMITER |; + +CREATE OR REPLACE PROCEDURE SP1() +BEGIN +DECLARE p_date date; +DECLARE p_var2 varchar(16); +SET p_date='2019-10-01'; +SET p_var2='AAA'; +WITH cte_first(col) AS +( + SELECT DISTINCT col4 + FROM t1, t2 + WHERE t2.col1 = t1.col1 AND t2.col2 = p_date AND t2.col3 = p_var2 +), +cte2 AS +( + SELECT DISTINCT col2 + FROM t3 + WHERE col1 IN ( SELECT col FROM cte_first ) +), +cte3 AS ( + SELECT distinct t4.col1 + FROM cte2, t4 + WHERE t4.col2 = cte2.col2 AND t4.col1 IN ( SELECT col FROM cte_first ) +) +SELECT * FROM cte3; +END| + +CREATE PROCEDURE SP2(IN d date) +BEGIN +DECLARE p_var2 varchar(16); +SET p_var2='AAA'; +WITH cte_first(col) AS +( + SELECT DISTINCT col4 + FROM t1, t2 + WHERE t2.col1 = t1.col1 AND t2.col2 = d AND t2.col3 = p_var2 +), +cte2 AS +( + SELECT DISTINCT col2 + FROM t3 + WHERE col1 IN ( SELECT col FROM cte_first ) +), +cte3 AS ( + SELECT distinct t4.col1 + FROM cte2, t4 + WHERE t4.col2 = cte2.col2 AND t4.col1 IN ( SELECT col FROM cte_first ) +) +SELECT * FROM cte3; +END| + +DELIMITER ;| + + +CREATE TABLE t AS +SELECT col4 AS col +FROM t1, t2 +WHERE t2.col1 = t1.col1 AND t2.col2 ='2019-10-01' AND t2.col3 = 'AAA'; +SELECT * FROM t; + +CREATE TABLE tt AS +SELECT col2 +FROM t3 +WHERE col1 IN ( SELECT col FROM t ); +SELECT * FROM tt; + +SELECT t4.col1 +FROM tt, t4 +WHERE t4.col2 = tt.col2 AND t4.col1 IN ( SELECT col FROM t ); + +DROP TABLE t,tt; + +CALL SP1(); +CALL SP1(); + +CALL SP2('2019-10-01'); +CALL SP2('2019-10-01'); + +DROP PROCEDURE SP1; +DROP PROCEDURE SP2; +DROP TABLE t1,t2,t3,t4; + +--echo # End of 10.3 tests diff --git a/sql/sql_lex.cc b/sql/sql_lex.cc index 58c91ab..00fca6c 100644 --- a/sql/sql_lex.cc +++ b/sql/sql_lex.cc @@ -7066,7 +7066,8 @@ Item *LEX::create_item_limit(THD *thd, const Lex_ident_cli_st *ca) if (unlikely(!(item= new (thd->mem_root) Item_splocal(thd, rh, &sa, spv->offset, spv->type_handler(), - pos.pos(), pos.length())))) + clone_spec_offset ? 0 : pos.pos(), + clone_spec_offset ? 0 : pos.length())))) return NULL; #ifdef DBUG_ASSERT_EXISTS item->m_sp= sphead; @@ -7165,14 +7166,15 @@ Item *LEX::create_item_ident_sp(THD *thd, Lex_ident_sys_st *name, } Query_fragment pos(thd, sphead, start, end); + uint f_pos= clone_spec_offset ? 0 : pos.pos(); + uint f_length= clone_spec_offset ? 0 : pos.length(); Item_splocal *splocal= spv->field_def.is_column_type_ref() ? new (thd->mem_root) Item_splocal_with_delayed_data_type(thd, rh, name, spv->offset, - pos.pos(), - pos.length()) : + f_pos, f_length) : new (thd->mem_root) Item_splocal(thd, rh, name, spv->offset, spv->type_handler(), - pos.pos(), pos.length()); + f_pos, f_length); if (unlikely(splocal == NULL)) return NULL; #ifdef DBUG_ASSERT_EXISTS