From 0f080dd60a9a4fbfcd9c8a2d0361c9d7f0c080aa Mon Sep 17 00:00:00 2001
From: Oleksandr Byelkin <sanja@mariadb.com>
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/suite/compat/oracle/t/parser.test | 132 +++++++++++++++++++++++++++
 1 file changed, 132 insertions(+)

(limited to 'mysql-test/suite/compat/oracle/t/parser.test')

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