MDEV-10411 Providing compatibility for basic PL/SQL constructs

Part 17: RETURN in stored procedures

2 files changed, 100 insertions, 9 deletions

diff --git a/mysql-test/suite/compat/oracle/r/sp-code.result b/mysql-test/suite/compat/oracle/r/sp-code.result
index 9ac733274e1..4f2dc58d6c8 100644
--- a/mysql-test/suite/compat/oracle/r/sp-code.result
+++ b/mysql-test/suite/compat/oracle/r/sp-code.result
@@ -415,8 +415,8 @@ END;
 SHOW FUNCTION CODE f1;
 Pos	Instruction
 0	set i@0 0
-1	set i@0 (i@0 + 1)
-2	jump_if_not 1(1) (i@0 >= 5)
+1	set i@0 i@0 + 1
+2	jump_if_not 1(1) i@0 >= 5
 3	jump 4
 4	freturn 3 i@0
 SELECT f1() FROM DUAL;
@@ -437,8 +437,8 @@ END;
 SHOW FUNCTION CODE f1;
 Pos	Instruction
 0	set i@0 0
-1	set i@0 (i@0 + 1)
-2	jump_if_not 1(0) (i@0 >= 5)
+1	set i@0 i@0 + 1
+2	jump_if_not 1(0) i@0 >= 5
 3	jump 4
 4	freturn 3 i@0
 SELECT f1() FROM DUAL;
@@ -466,8 +466,8 @@ SHOW FUNCTION CODE f1;
 Pos	Instruction
 0	set i@0 0
 1	jump 5
-2	set i@0 (i@0 + 1)
-3	jump_if_not 8(8) (i@0 >= 5)
+2	set i@0 i@0 + 1
+3	jump_if_not 8(8) i@0 >= 5
 4	jump 10
 5	hpush_jump 2 1 EXIT
 6	set i@0 1000
@@ -506,15 +506,15 @@ SHOW PROCEDURE CODE p1;
 Pos	Instruction
 0	set i@1 0
 1	jump 14
-2	set i@1 (i@1 + 1)
-3	jump_if_not 8(8) (i@1 >= 5)
+2	set i@1 i@1 + 1
+3	jump_if_not 8(8) i@1 >= 5
 4	jump 10
 5	hpush_jump 2 2 EXIT
 6	set a@0 1000
 7	hreturn 0 8
 8	hpop 1
 9	jump 5
-10	set i@1 (i@1 + 100)
+10	set i@1 i@1 + 100
 11	jump 12
 12	set a@0 i@1
 13	jump 17
@@ -528,3 +528,35 @@ SELECT @v;
 @v
 105
 DROP PROCEDURE p1;
+# Testing RETURN in procedures
+CREATE PROCEDURE p1 (a IN OUT INT)
+AS
+BEGIN
+IF a < 10 THEN
+BEGIN
+a:= a + 1;
+RETURN;
+END;
+END IF;
+a:= 200;
+EXCEPTION
+WHEN OTHERS THEN
+BEGIN
+a:= 100;
+RETURN;
+END;
+END;
+/
+SHOW PROCEDURE CODE p1;
+Pos	Instruction
+0	jump 6
+1	jump_if_not 4(4) a@0 < 10
+2	set a@0 a@0 + 1
+3	preturn
+4	set a@0 200
+5	jump 9
+6	hpush_jump 1 1 EXIT
+7	set a@0 100
+8	preturn
+9	hpop 1
+DROP PROCEDURE p1;
diff --git a/mysql-test/suite/compat/oracle/r/sp.result b/mysql-test/suite/compat/oracle/r/sp.result
index 3f5c7fa2eb3..ec68f45b5cd 100644
--- a/mysql-test/suite/compat/oracle/r/sp.result
+++ b/mysql-test/suite/compat/oracle/r/sp.result
@@ -677,3 +677,62 @@ f1()
 1
 DROP FUNCTION f1;
 DROP TABLE t1;
+# Testing RETURN in procedures
+CREATE PROCEDURE p1 (a IN OUT INT)
+AS
+BEGIN
+RETURN 10;
+END;
+/
+ERROR 42000: RETURN is only allowed in a FUNCTION
+CREATE FUNCTION f1 (a INT) RETURN INT
+AS
+BEGIN
+RETURN;
+END;
+/
+ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ';
+END' at line 4
+CREATE PROCEDURE p1 (a IN OUT INT)
+AS
+BEGIN
+IF a < 10 THEN
+BEGIN
+a:= a - 1;
+RETURN;
+END;
+END IF;
+a:= a + 1;
+EXCEPTION
+WHEN OTHERS THEN RETURN;
+END;
+/
+SET @v=10;
+CALL p1(@v);
+SELECT @v;
+@v
+11
+SET @v=9;
+CALL p1(@v);
+SELECT @v;
+@v
+8
+DROP PROCEDURE p1;
+CREATE PROCEDURE p1 (a IN OUT INT)
+AS
+BEGIN
+DROP TABLE t1_non_existent;
+EXCEPTION
+WHEN OTHERS THEN
+BEGIN
+a:= 100;
+RETURN;
+END;
+END;
+/
+SET @v=10;
+CALL p1(@v);
+SELECT @v;
+@v
+100
+DROP PROCEDURE p1;