From 0f080dd60a9a4fbfcd9c8a2d0361c9d7f0c080aa Mon Sep 17 00:00:00 2001 From: Oleksandr Byelkin Date: Tue, 11 Aug 2020 16:37:48 +0200 Subject: MDEV-23094: Multiple calls to a Stored Procedure from another Stored Procedure crashes server Added system-SELECT to IF/WHILE/REPET/FOR for correct subqueries connecting. Added control of system/usual selects for correct error detection. --- mysql-test/main/parser.result | 89 +++++++++++++++++ mysql-test/main/parser.test | 97 ++++++++++++++++++ mysql-test/suite/compat/oracle/r/parser.result | 115 +++++++++++++++++++++ mysql-test/suite/compat/oracle/t/parser.test | 132 +++++++++++++++++++++++++ 4 files changed, 433 insertions(+) (limited to 'mysql-test') diff --git a/mysql-test/main/parser.result b/mysql-test/main/parser.result index 78156c842ea..49ef2073971 100644 --- a/mysql-test/main/parser.result +++ b/mysql-test/main/parser.result @@ -1908,4 +1908,93 @@ KILL ( SELECT 1 ) + LASTVAL(s); ERROR 42000: KILL does not support subqueries or stored functions KILL LASTVAL(s); ERROR 42000: KILL does not support subqueries or stored functions +# +# MDEV-23094: Multiple calls to a Stored Procedure from another +# Stored Procedure crashes server +# +create table t1 (id1 int primary key, data1 int); +create table t2 (id2 int primary key, data2 int); +create procedure p1(IN id int, IN dt int) +begin +if (exists(select * from t1 where id1 = id and data1 = dt) or +not exists (select * from t2 where id2 = id and data2 = dt)) +then +select 1; +end if; +end // +call p1(1,2); +1 +1 +call p1(1,2); +1 +1 +drop procedure p1; +create procedure p1(IN id int, IN dt int) +begin +case (exists(select * from t1 where id1 = id and data1 = dt) or +not exists (select * from t2 where id2 = id and data2 = dt)) +when 1 then +select 1; +else +select 0; +end case; +end // +call p1(1,2); +1 +1 +call p1(1,2); +1 +1 +drop procedure p1; +create procedure p1(IN id int, IN dt int) +begin +declare wcont int default 1; +while (exists(select * from t1 where id1 = id and data1 = dt) or +not exists (select * from t2 where id2 = id and data2 = dt)) and wcont +do +select 1; +set wcont=0; +end while; +end // +call p1(1,2); +1 +1 +call p1(1,2); +1 +1 +drop procedure p1; +create procedure p1(IN id int, IN dt int) +begin +declare count int default 1; +repeat +select 1; +set count=count+1; +until (exists(select * from t1 where id1 = id and data1 = dt) or +not exists (select * from t2 where id2 = id and data2 = dt)) and +count < 3 +end repeat; +end // +call p1(1,2); +1 +1 +call p1(1,2); +1 +1 +drop procedure p1; +create procedure p1(IN id int, IN dt int) +begin +for i in 1..(exists(select * from t1 where id1 = id and data1 = dt) or +not exists (select * from t2 where id2 = id and data2 = dt)) +do +select 1; +end for; +end // +call p1(1,2); +1 +1 +call p1(1,2); +1 +1 +drop procedure p1; +drop table t1,t2; # End of 10.4 tests diff --git a/mysql-test/main/parser.test b/mysql-test/main/parser.test index 608f76557e5..502bbde5ea5 100644 --- a/mysql-test/main/parser.test +++ b/mysql-test/main/parser.test @@ -1696,4 +1696,101 @@ KILL ( SELECT 1 ) + LASTVAL(s); --error ER_SUBQUERIES_NOT_SUPPORTED KILL LASTVAL(s); +--echo # +--echo # MDEV-23094: Multiple calls to a Stored Procedure from another +--echo # Stored Procedure crashes server +--echo # + +create table t1 (id1 int primary key, data1 int); +create table t2 (id2 int primary key, data2 int); + +delimiter //; +create procedure p1(IN id int, IN dt int) +begin + if (exists(select * from t1 where id1 = id and data1 = dt) or + not exists (select * from t2 where id2 = id and data2 = dt)) + then + select 1; + end if; +end // +delimiter ;// + +call p1(1,2); +call p1(1,2); + +drop procedure p1; + +delimiter //; +create procedure p1(IN id int, IN dt int) +begin +case (exists(select * from t1 where id1 = id and data1 = dt) or + not exists (select * from t2 where id2 = id and data2 = dt)) +when 1 then + select 1; +else + select 0; +end case; +end // +delimiter ;// + +call p1(1,2); +call p1(1,2); + +drop procedure p1; + +delimiter //; +create procedure p1(IN id int, IN dt int) +begin +declare wcont int default 1; +while (exists(select * from t1 where id1 = id and data1 = dt) or + not exists (select * from t2 where id2 = id and data2 = dt)) and wcont +do + select 1; + set wcont=0; +end while; +end // +delimiter ;// + +call p1(1,2); +call p1(1,2); + +drop procedure p1; + +delimiter //; +create procedure p1(IN id int, IN dt int) +begin +declare count int default 1; +repeat + select 1; + set count=count+1; +until (exists(select * from t1 where id1 = id and data1 = dt) or + not exists (select * from t2 where id2 = id and data2 = dt)) and + count < 3 +end repeat; +end // +delimiter ;// + +call p1(1,2); +call p1(1,2); + +drop procedure p1; + +delimiter //; +create procedure p1(IN id int, IN dt int) +begin +for i in 1..(exists(select * from t1 where id1 = id and data1 = dt) or + not exists (select * from t2 where id2 = id and data2 = dt)) +do + select 1; +end for; +end // +delimiter ;// + +call p1(1,2); +call p1(1,2); + +drop procedure p1; + +drop table t1,t2; + --echo # End of 10.4 tests diff --git a/mysql-test/suite/compat/oracle/r/parser.result b/mysql-test/suite/compat/oracle/r/parser.result index 0b573027c6d..2d9416d59c1 100644 --- a/mysql-test/suite/compat/oracle/r/parser.result +++ b/mysql-test/suite/compat/oracle/r/parser.result @@ -643,3 +643,118 @@ END; # # End of 10.3 tests # +# +# MDEV-21998: Server crashes in st_select_lex::add_table_to_list +# upon mix of KILL and sequences +# +KILL ( SELECT 1 ) + LASTVAL(s); +ERROR 42000: KILL does not support subqueries or stored functions +KILL LASTVAL(s); +ERROR 42000: KILL does not support subqueries or stored functions +# +# MDEV-23094: Multiple calls to a Stored Procedure from another +# Stored Procedure crashes server +# +create table t1 (id1 int primary key, data1 int); +create table t2 (id2 int primary key, data2 int); +create procedure p1(id int,dt int) as +begin +if (exists(select * from t1 where id1 = id and data1 = dt) or +not exists (select * from t2 where id2 = id and data2 = dt)) +then +select 1; +end if; +end // +call p1(1,2); +1 +1 +call p1(1,2); +1 +1 +drop procedure p1; +create procedure p1(id int, dt int) as +begin +case (exists(select * from t1 where id1 = id and data1 = dt) or +not exists (select * from t2 where id2 = id and data2 = dt)) +when 1 then +select 1; +else +select 0; +end case; +end // +call p1(1,2); +1 +1 +call p1(1,2); +1 +1 +drop procedure p1; +create procedure p1(id int, dt int) as +begin +declare wcont int default 1; +begin +while (exists(select * from t1 where id1 = id and data1 = dt) or +not exists (select * from t2 where id2 = id and data2 = dt)) and wcont +loop +select 1; +set wcont=0; +end loop; +end; +end // +call p1(1,2); +1 +1 +call p1(1,2); +1 +1 +drop procedure p1; +create procedure p1(id int, dt int) as +begin +declare count int default 1; +begin +repeat +select 1; +set count=count+1; +until (exists(select * from t1 where id1 = id and data1 = dt) or +not exists (select * from t2 where id2 = id and data2 = dt)) and +count < 3 +end repeat; +end; +end // +call p1(1,2); +1 +1 +call p1(1,2); +1 +1 +drop procedure p1; +create procedure p1(id int, dt int) as +begin +for i in 1..(exists(select * from t1 where id1 = id and data1 = dt) or +not exists (select * from t2 where id2 = id and data2 = dt)) +loop +select 1; +end loop; +end // +call p1(1,2); +1 +1 +call p1(1,2); +1 +1 +drop procedure p1; +set sql_mode=ORACLE; +create or replace procedure p1(id int, dt int) as +begin +while (1) +loop +exit when (exists(select * from t1 where id1 = id and data1 = dt) or +not exists (select * from t2 where id2 = id and data2 = dt)); +end loop; +end; +// +call p1(1,2); +call p1(1,2); +drop procedure p1; +drop table t1,t2; +# End of 10.4 tests diff --git a/mysql-test/suite/compat/oracle/t/parser.test b/mysql-test/suite/compat/oracle/t/parser.test index 4d558c5d153..8dabdc1b3bd 100644 --- a/mysql-test/suite/compat/oracle/t/parser.test +++ b/mysql-test/suite/compat/oracle/t/parser.test @@ -459,3 +459,135 @@ DELIMITER ;// --echo # --echo # End of 10.3 tests --echo # + + +--echo # +--echo # MDEV-21998: Server crashes in st_select_lex::add_table_to_list +--echo # upon mix of KILL and sequences +--echo # + +--error ER_SUBQUERIES_NOT_SUPPORTED +KILL ( SELECT 1 ) + LASTVAL(s); +--error ER_SUBQUERIES_NOT_SUPPORTED +KILL LASTVAL(s); + +--echo # +--echo # MDEV-23094: Multiple calls to a Stored Procedure from another +--echo # Stored Procedure crashes server +--echo # + +create table t1 (id1 int primary key, data1 int); +create table t2 (id2 int primary key, data2 int); + +delimiter //; +create procedure p1(id int,dt int) as +begin + if (exists(select * from t1 where id1 = id and data1 = dt) or + not exists (select * from t2 where id2 = id and data2 = dt)) + then + select 1; + end if; +end // +delimiter ;// + +call p1(1,2); +call p1(1,2); + +drop procedure p1; + +delimiter //; +create procedure p1(id int, dt int) as +begin +case (exists(select * from t1 where id1 = id and data1 = dt) or + not exists (select * from t2 where id2 = id and data2 = dt)) +when 1 then + select 1; +else + select 0; +end case; +end // +delimiter ;// + +call p1(1,2); +call p1(1,2); + +drop procedure p1; + +delimiter //; +create procedure p1(id int, dt int) as +begin +declare wcont int default 1; +begin + while (exists(select * from t1 where id1 = id and data1 = dt) or + not exists (select * from t2 where id2 = id and data2 = dt)) and wcont + loop + select 1; + set wcont=0; + end loop; +end; +end // +delimiter ;// + +call p1(1,2); +call p1(1,2); + +drop procedure p1; + +delimiter //; +create procedure p1(id int, dt int) as +begin +declare count int default 1; +begin + repeat + select 1; + set count=count+1; + until (exists(select * from t1 where id1 = id and data1 = dt) or + not exists (select * from t2 where id2 = id and data2 = dt)) and + count < 3 + end repeat; +end; +end // +delimiter ;// + +call p1(1,2); +call p1(1,2); + +drop procedure p1; + +delimiter //; +create procedure p1(id int, dt int) as +begin +for i in 1..(exists(select * from t1 where id1 = id and data1 = dt) or + not exists (select * from t2 where id2 = id and data2 = dt)) +loop + select 1; +end loop; +end // +delimiter ;// + +call p1(1,2); +call p1(1,2); + +drop procedure p1; + +delimiter //; +set sql_mode=ORACLE; +create or replace procedure p1(id int, dt int) as +begin + while (1) + loop + exit when (exists(select * from t1 where id1 = id and data1 = dt) or + not exists (select * from t2 where id2 = id and data2 = dt)); + end loop; +end; +// +delimiter ;// + +call p1(1,2); +call p1(1,2); + +drop procedure p1; + +drop table t1,t2; + +--echo # End of 10.4 tests -- cgit v1.2.1