MDEV-17253 Oracle compatibility: The REVERSE key word for FOR loop behaves incorrectly

4 files changed, 125 insertions, 24 deletions

diff --git a/mysql-test/suite/compat/oracle/r/sp-code.result b/mysql-test/suite/compat/oracle/r/sp-code.result
index 1049563511c..1c6aacc8743 100644
--- a/mysql-test/suite/compat/oracle/r/sp-code.result
+++ b/mysql-test/suite/compat/oracle/r/sp-code.result
@@ -579,8 +579,8 @@ SHOW FUNCTION CODE f1;
 Pos	Instruction
 0	set total@2 0
 1	set i@3 1
-2	set [upper_bound]@4 a@0
-3	jump_if_not 9(9) i@3 <= [upper_bound]@4
+2	set [target_bound]@4 a@0
+3	jump_if_not 9(9) i@3 <= [target_bound]@4
 4	set total@2 total@2 + i@3
 5	jump_if_not 7(7) i@3 = b@1
 6	jump 9
@@ -598,7 +598,7 @@ CREATE FUNCTION f1 (a INT, b INT) RETURN INT
 AS
 total INT := 0;
 BEGIN
-FOR i IN REVERSE a..1
+FOR i IN REVERSE 1..a
 LOOP
 total:= total + i;
 IF i = b THEN
@@ -612,8 +612,8 @@ SHOW FUNCTION CODE f1;
 Pos	Instruction
 0	set total@2 0
 1	set i@3 a@0
-2	set [upper_bound]@4 1
-3	jump_if_not 9(9) i@3 >= [upper_bound]@4
+2	set [target_bound]@4 1
+3	jump_if_not 9(9) i@3 >= [target_bound]@4
 4	set total@2 total@2 + i@3
 5	jump_if_not 7(7) i@3 = b@1
 6	jump 9
@@ -651,12 +651,12 @@ SHOW FUNCTION CODE f1;
 Pos	Instruction
 0	set total@4 0
 1	set ia@5 1
-2	set [upper_bound]@6 a@0
-3	jump_if_not 17(17) ia@5 <= [upper_bound]@6
+2	set [target_bound]@6 a@0
+3	jump_if_not 17(17) ia@5 <= [target_bound]@6
 4	set total@4 total@4 + 1000
 5	set ib@7 1
-6	set [upper_bound]@8 b@2
-7	jump_if_not 15(15) ib@7 <= [upper_bound]@8
+6	set [target_bound]@8 b@2
+7	jump_if_not 15(15) ib@7 <= [target_bound]@8
 8	set total@4 total@4 + 1
 9	jump_if_not 11(0) ib@7 = limitb@3
 10	jump 15
@@ -698,8 +698,8 @@ SHOW FUNCTION CODE f1;
 Pos	Instruction
 0	set total@1 0
 1	set i@2 1
-2	set [upper_bound]@3 a@0
-3	jump_if_not 11(11) i@2 <= [upper_bound]@3
+2	set [target_bound]@3 a@0
+3	jump_if_not 11(11) i@2 <= [target_bound]@3
 4	set total@1 total@1 + 1000
 5	jump_if_not 8(8) i@2 = 5
 6	set i@2 i@2 + 1
@@ -735,11 +735,11 @@ SHOW FUNCTION CODE f1;
 Pos	Instruction
 0	set total@1 0
 1	set i@2 1
-2	set [upper_bound]@3 a@0
-3	jump_if_not 16(16) i@2 <= [upper_bound]@3
+2	set [target_bound]@3 a@0
+3	jump_if_not 16(16) i@2 <= [target_bound]@3
 4	set j@4 1
-5	set [upper_bound]@5 2
-6	jump_if_not 14(14) j@4 <= [upper_bound]@5
+5	set [target_bound]@5 2
+6	jump_if_not 14(14) j@4 <= [target_bound]@5
 7	set total@1 total@1 + 1000
 8	jump_if_not 11(11) i@2 = 5
 9	set i@2 i@2 + 1
@@ -778,11 +778,11 @@ SHOW FUNCTION CODE f1;
 Pos	Instruction
 0	set total@1 0
 1	set j@2 1
-2	set [upper_bound]@3 2
-3	jump_if_not 16(16) j@2 <= [upper_bound]@3
+2	set [target_bound]@3 2
+3	jump_if_not 16(16) j@2 <= [target_bound]@3
 4	set i@4 1
-5	set [upper_bound]@5 a@0
-6	jump_if_not 14(14) i@4 <= [upper_bound]@5
+5	set [target_bound]@5 a@0
+6	jump_if_not 14(14) i@4 <= [target_bound]@5
 7	set total@1 total@1 + 1000
 8	jump_if_not 11(11) i@4 = 5
 9	set i@4 i@4 + 1
@@ -814,8 +814,8 @@ SHOW FUNCTION CODE f1;
 Pos	Instruction
 0	set total@1 0
 1	set i@2 1
-2	set [upper_bound]@3 a@0
-3	jump_if_not 10(10) i@2 <= [upper_bound]@3
+2	set [target_bound]@3 a@0
+3	jump_if_not 10(10) i@2 <= [target_bound]@3
 4	jump_if_not 7(0) i@2 = 5
 5	set i@2 i@2 + 1
 6	jump 3
diff --git a/mysql-test/suite/compat/oracle/r/sp.result b/mysql-test/suite/compat/oracle/r/sp.result
index 8e126b3f724..8fa49c4092a 100644
--- a/mysql-test/suite/compat/oracle/r/sp.result
+++ b/mysql-test/suite/compat/oracle/r/sp.result
@@ -843,7 +843,7 @@ CREATE FUNCTION f1 (a INT, b INT) RETURN INT
 AS
 total INT := 0;
 BEGIN
-FOR i IN REVERSE a..1
+FOR i IN REVERSE 1..a
 LOOP
 total:= total + i;
 IF i = b THEN
@@ -2502,3 +2502,61 @@ BEGIN
 SELECT 'a' IN ('b',v);
 END $$
 ERROR HY000: Illegal parameter data types varchar and row for operation 'in'
+#
+# MDEV-17253 Oracle compatibility: The REVERSE key word for FOR loop behaves incorrectly
+#
+DECLARE
+totalprice DECIMAL(12,2):=NULL;
+loop_start INTEGER := 1;
+BEGIN
+FOR idx IN REVERSE loop_start..10 LOOP
+SELECT idx;
+END LOOP;
+END;
+$$
+idx
+10
+idx
+9
+idx
+8
+idx
+7
+idx
+6
+idx
+5
+idx
+4
+idx
+3
+idx
+2
+idx
+1
+CREATE PROCEDURE p1 AS
+loop_start INTEGER := 1;
+BEGIN
+FOR idx IN REVERSE 3..loop_start LOOP
+SELECT idx;
+END LOOP;
+END;
+$$
+CALL p1();
+DROP PROCEDURE p1;
+CREATE PROCEDURE p1 AS
+loop_start INTEGER := 1;
+BEGIN
+FOR idx IN REVERSE loop_start..3 LOOP
+SELECT idx;
+END LOOP;
+END;
+$$
+CALL p1();
+idx
+3
+idx
+2
+idx
+1
+DROP PROCEDURE p1;
diff --git a/mysql-test/suite/compat/oracle/t/sp-code.test b/mysql-test/suite/compat/oracle/t/sp-code.test
index 9a1f64e54b4..ea66ed80d2a 100644
--- a/mysql-test/suite/compat/oracle/t/sp-code.test
+++ b/mysql-test/suite/compat/oracle/t/sp-code.test
@@ -470,7 +470,7 @@ CREATE FUNCTION f1 (a INT, b INT) RETURN INT
 AS
   total INT := 0;
 BEGIN
-  FOR i IN REVERSE a..1
+  FOR i IN REVERSE 1..a
   LOOP
     total:= total + i;
     IF i = b THEN
diff --git a/mysql-test/suite/compat/oracle/t/sp.test b/mysql-test/suite/compat/oracle/t/sp.test
index b88271ad3e2..6020bd95993 100644
--- a/mysql-test/suite/compat/oracle/t/sp.test
+++ b/mysql-test/suite/compat/oracle/t/sp.test
@@ -918,7 +918,7 @@ CREATE FUNCTION f1 (a INT, b INT) RETURN INT
 AS
   total INT := 0;
 BEGIN
-  FOR i IN REVERSE a..1
+  FOR i IN REVERSE 1..a
   LOOP
     total:= total + i;
     IF i = b THEN
@@ -2352,3 +2352,46 @@ BEGIN
 END $$
 DELIMITER ;$$
 
+--echo #
+--echo # MDEV-17253 Oracle compatibility: The REVERSE key word for FOR loop behaves incorrectly
+--echo #
+
+DELIMITER $$;
+DECLARE
+  totalprice DECIMAL(12,2):=NULL;
+  loop_start INTEGER := 1;
+BEGIN
+  FOR idx IN REVERSE loop_start..10 LOOP
+    SELECT idx;
+  END LOOP;
+END;
+$$
+DELIMITER ;$$
+
+
+DELIMITER $$;
+CREATE PROCEDURE p1 AS
+  loop_start INTEGER := 1;
+BEGIN
+  FOR idx IN REVERSE 3..loop_start LOOP
+    SELECT idx;
+  END LOOP;
+END;
+$$
+DELIMITER ;$$
+CALL p1();
+DROP PROCEDURE p1;
+
+
+DELIMITER $$;
+CREATE PROCEDURE p1 AS
+  loop_start INTEGER := 1;
+BEGIN
+  FOR idx IN REVERSE loop_start..3 LOOP
+    SELECT idx;
+  END LOOP;
+END;
+$$
+DELIMITER ;$$
+CALL p1();
+DROP PROCEDURE p1;