diff options
author | Igor Babaev <igor@askmonty.org> | 2020-04-04 09:24:22 -0700 |
---|---|---|
committer | Igor Babaev <igor@askmonty.org> | 2020-04-04 09:24:22 -0700 |
commit | 21b87437340c403794f100d1af50dcd9c19e123c (patch) | |
tree | 75f2e6d23afc177b2fe222fffb243acc7deef221 /mysql-test/main/cte_nonrecursive.result | |
parent | fbef428645e01113521824c2eb0a42fe4163699c (diff) | |
download | mariadb-git-21b87437340c403794f100d1af50dcd9c19e123c.tar.gz |
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.
Diffstat (limited to 'mysql-test/main/cte_nonrecursive.result')
-rw-r--r-- | mysql-test/main/cte_nonrecursive.result | 110 |
1 files changed, 110 insertions, 0 deletions
diff --git a/mysql-test/main/cte_nonrecursive.result b/mysql-test/main/cte_nonrecursive.result index ff5a6f15597..1bd731c956b 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 |