diff options
author | Marko Mäkelä <marko.makela@mariadb.com> | 2018-11-19 19:58:27 +0200 |
---|---|---|
committer | Marko Mäkelä <marko.makela@mariadb.com> | 2018-11-19 20:22:33 +0200 |
commit | dde2ca4aa108b611b5fdfc970146b28461ef08bf (patch) | |
tree | 08f90bb9d54413af23230ccf99f18391596afa34 /mysql-test/suite/compat | |
parent | b5ac863f1494920b5e7035c9dfa0ebfdaa50a15d (diff) | |
parent | fd58bb71e22196c3c5e8d20b92bce6f343f1dea1 (diff) | |
download | mariadb-git-dde2ca4aa108b611b5fdfc970146b28461ef08bf.tar.gz |
Merge 10.3 into 10.4
Diffstat (limited to 'mysql-test/suite/compat')
-rw-r--r-- | mysql-test/suite/compat/oracle/r/parser.result | 110 | ||||
-rw-r--r-- | mysql-test/suite/compat/oracle/r/sp-code.result | 42 | ||||
-rw-r--r-- | mysql-test/suite/compat/oracle/r/sp-cursor-rowtype.result | 25 | ||||
-rw-r--r-- | mysql-test/suite/compat/oracle/r/sp-cursor.result | 28 | ||||
-rw-r--r-- | mysql-test/suite/compat/oracle/r/sp-package.result | 48 | ||||
-rw-r--r-- | mysql-test/suite/compat/oracle/r/sp.result | 60 | ||||
-rw-r--r-- | mysql-test/suite/compat/oracle/t/parser.test | 154 | ||||
-rw-r--r-- | mysql-test/suite/compat/oracle/t/sp-code.test | 2 | ||||
-rw-r--r-- | mysql-test/suite/compat/oracle/t/sp-cursor-rowtype.test | 23 | ||||
-rw-r--r-- | mysql-test/suite/compat/oracle/t/sp-cursor.test | 35 | ||||
-rw-r--r-- | mysql-test/suite/compat/oracle/t/sp-package.test | 57 | ||||
-rw-r--r-- | mysql-test/suite/compat/oracle/t/sp.test | 45 |
12 files changed, 605 insertions, 24 deletions
diff --git a/mysql-test/suite/compat/oracle/r/parser.result b/mysql-test/suite/compat/oracle/r/parser.result index c8600c29bd4..1c60c1434a3 100644 --- a/mysql-test/suite/compat/oracle/r/parser.result +++ b/mysql-test/suite/compat/oracle/r/parser.result @@ -499,3 +499,113 @@ test.comment() Warnings: Note 1585 This function 'comment' has the same name as a native function DROP FUNCTION comment; +# +# MDEV-17660 sql_mode=ORACLE: Some keywords do not work as label names: history, system, versioning, without +# +BEGIN +<<date_format>> +NULL; +END; +/ +BEGIN +<<decode>> +NULL; +END; +/ +BEGIN +<<history>> +NULL; +END; +/ +BEGIN +<<system>> +NULL; +END; +/ +BEGIN +<<versioning>> +NULL; +END; +/ +BEGIN +<<without>> +NULL; +END; +/ +# +# MDEV-17666 sql_mode=ORACLE: Keyword ELSEIF should not be reserved +# +DECLARE +ELSEIF INT; +BEGIN +ELSEIF:=1; +END; +/ +BEGIN +<<ELSEIF>> +NULL; +END; +/ +# +# MDEV-17693 Shift/reduce conflicts for NAMES,ROLE,PASSWORD in the option_value_no_option_type grammar +# +CREATE TABLE names (names INT); +SELECT names FROM names AS names; +names +DROP TABLE names; +CREATE TABLE password (password INT); +SELECT password FROM password AS password; +password +DROP TABLE password; +CREATE TABLE role (role INT); +SELECT role FROM role AS role; +role +DROP TABLE role; +DECLARE +names VARCHAR(32) DEFAULT '[names]'; +password VARCHAR(32) DEFAULT '[password]'; +role VARCHAR(32) DEFAULT '[role]'; +BEGIN +<<names>> +SELECT names; +<<password>> +SELECT password; +<<role>> +SELECT role; +END; +$$ +names +[names] +password +[password] +role +[role] +DECLARE +names VARCHAR(32); +BEGIN +SET names='[names]'; +END; +$$ +ERROR 42000: Variable 'names' must be quoted with `...`, or renamed +DECLARE +password VARCHAR(32); +BEGIN +SET password='[password]'; +END; +$$ +ERROR 42000: Variable 'password' must be quoted with `...`, or renamed +DECLARE +role VARCHAR(32); +BEGIN +SET role='[role]'; +END; +$$ +SELECT @@GLOBAL.names; +ERROR HY000: Unknown system variable 'names' +SELECT @@GLOBAL.password; +ERROR HY000: Unknown system variable 'password' +SELECT @@GLOBAL.role; +ERROR HY000: Unknown system variable 'role' +# +# End of 10.3 tests +# 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-cursor-rowtype.result b/mysql-test/suite/compat/oracle/r/sp-cursor-rowtype.result index 093d52ba4e3..a60bbc38883 100644 --- a/mysql-test/suite/compat/oracle/r/sp-cursor-rowtype.result +++ b/mysql-test/suite/compat/oracle/r/sp-cursor-rowtype.result @@ -1479,3 +1479,28 @@ f1() 1 DROP FUNCTION f1; DROP TABLE t1; +# +# MDEV-17278 CURSOR FOR LOOP - ERROR: unexpected end of stream, read 0 bytes (SERVER CRASH) +# +CREATE TABLE t1 (id2 int, id int, en1 enum('aaa','a','b','c')); +INSERT INTO t1 VALUES(1,1,'aaa'),(2,2,'a'),(3,3,'b'),(4,4,'c'); +CREATE PROCEDURE p1() +AS +BEGIN +FOR rec IN (SELECT en1 FROM t1) +LOOP +SELECT rec.en1; +END LOOP; +END; +$$ +CALL p1(); +rec.en1 +aaa +rec.en1 +a +rec.en1 +b +rec.en1 +c +DROP PROCEDURE p1; +DROP TABLE t1; diff --git a/mysql-test/suite/compat/oracle/r/sp-cursor.result b/mysql-test/suite/compat/oracle/r/sp-cursor.result index 03211509f8b..e539f38e307 100644 --- a/mysql-test/suite/compat/oracle/r/sp-cursor.result +++ b/mysql-test/suite/compat/oracle/r/sp-cursor.result @@ -949,3 +949,31 @@ def c%FOUND 3 1 0 Y 32896 0 63 def c%ROWCOUNT 8 21 1 Y 32896 0 63 c%ISOPEN c%NOTFOUND c%FOUND c%ROWCOUNT 1 NULL NULL 0 +# +# MDEV-17387 MariaDB Server giving wrong error while executing select query from procedure +# +CREATE TABLE t1 +( +JOBN varchar(18) NOT NULL, +pk int(11) NOT NULL, +PRIMARY KEY (pk), +KEY (JOBN) +); +CREATE PROCEDURE p1 +AS +lS NUMBER(10) :=0; +CURSOR cBPD IS SELECT * FROM t1 WHERE JOBN='x'; +BEGIN +FOR lbpd IN cBPD LOOP +lS:=lS+1; +END LOOP; +EXCEPTION +WHEN OTHERS THEN +BEGIN +SELECT SQLERRM; +END; +END; +$$ +CALL p1(); +DROP PROCEDURE p1; +DROP TABLE t1; diff --git a/mysql-test/suite/compat/oracle/r/sp-package.result b/mysql-test/suite/compat/oracle/r/sp-package.result index 1ea53daf43a..4f0f05b1939 100644 --- a/mysql-test/suite/compat/oracle/r/sp-package.result +++ b/mysql-test/suite/compat/oracle/r/sp-package.result @@ -2878,3 +2878,51 @@ msg This is p01 DROP PACKAGE pkg1; DROP TABLE t1; +# +# MDEV-17387 MariaDB Server giving wrong error while executing select query from procedure +# +CREATE TABLE t1 ( +CTR varchar(2) NOT NULL, +COR varchar(3) NOT NULL, +DATE datetime NOT NULL, +CHAN varchar(4) NOT NULL, +CNO varchar(20) NOT NULL, +JOBN varchar(18) NOT NULL, +C1 varchar(30) DEFAULT NULL, +C2 varchar(30) DEFAULT NULL, +TIME datetime DEFAULT NULL, +AMT decimal(12,2) DEFAULT NULL, +DT datetime NOT NULL, +pk int(11) NOT NULL, +PRIMARY KEY (pk), +KEY Indx1 (JOBN) +); +CREATE PACKAGE xyz IS +PROCEDURE xyz123(ctr IN VARCHAR2,Jn IN VARCHAR2,R OUT VARCHAR2); +END; +$$ +CREATE OR REPLACE PACKAGE BODY xyz IS +PROCEDURE xyz123( +ctr IN VARCHAR2, +Jn IN VARCHAR2, +R OUT VARCHAR2) +AS +lS NUMBER(10) :=0; +CURSOR cBPD IS +SELECT CTR, COR, DATE, CHAN, CNO, C1, C2, TIME, AMT +FROM t1 WHERE JOBN=Jn; +BEGIN +FOR lbpd IN cBPD +LOOP +lS:=lS+1; +END LOOP; +EXCEPTION +WHEN OTHERS THEN +BEGIN +SELECT SQLERRM; +END; +END; +END $$ +CALL xyz.xyz123(17,18,@R); +DROP PACKAGE xyz; +DROP TABLE t1; diff --git a/mysql-test/suite/compat/oracle/r/sp.result b/mysql-test/suite/compat/oracle/r/sp.result index 5cdfd517562..6db999b238f 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 @@ -2494,3 +2494,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/parser.test b/mysql-test/suite/compat/oracle/t/parser.test index 86b6b270ccd..067fd9beb48 100644 --- a/mysql-test/suite/compat/oracle/t/parser.test +++ b/mysql-test/suite/compat/oracle/t/parser.test @@ -258,3 +258,157 @@ enable_prepare_warnings; SELECT test.comment() FROM DUAL; disable_prepare_warnings; DROP FUNCTION comment; + + +--echo # +--echo # MDEV-17660 sql_mode=ORACLE: Some keywords do not work as label names: history, system, versioning, without +--echo # + +DELIMITER /; +BEGIN +<<date_format>> + NULL; +END; +/ +DELIMITER ;/ + + +DELIMITER /; +BEGIN +<<decode>> + NULL; +END; +/ +DELIMITER ;/ + + +DELIMITER /; +BEGIN +<<history>> + NULL; +END; +/ +DELIMITER ;/ + + +DELIMITER /; +BEGIN +<<system>> + NULL; +END; +/ +DELIMITER ;/ + + +DELIMITER /; +BEGIN +<<versioning>> + NULL; +END; +/ +DELIMITER ;/ + + +DELIMITER /; +BEGIN +<<without>> + NULL; +END; +/ +DELIMITER ;/ + + +--echo # +--echo # MDEV-17666 sql_mode=ORACLE: Keyword ELSEIF should not be reserved +--echo # + +DELIMITER /; +DECLARE + ELSEIF INT; +BEGIN + ELSEIF:=1; +END; +/ +DELIMITER ;/ + +DELIMITER /; +BEGIN +<<ELSEIF>> + NULL; +END; +/ +DELIMITER ;/ + + +--echo # +--echo # MDEV-17693 Shift/reduce conflicts for NAMES,ROLE,PASSWORD in the option_value_no_option_type grammar +--echo # + +CREATE TABLE names (names INT); +SELECT names FROM names AS names; +DROP TABLE names; + +CREATE TABLE password (password INT); +SELECT password FROM password AS password; +DROP TABLE password; + +CREATE TABLE role (role INT); +SELECT role FROM role AS role; +DROP TABLE role; + +DELIMITER $$; +DECLARE + names VARCHAR(32) DEFAULT '[names]'; + password VARCHAR(32) DEFAULT '[password]'; + role VARCHAR(32) DEFAULT '[role]'; +BEGIN +<<names>> + SELECT names; +<<password>> + SELECT password; +<<role>> + SELECT role; +END; +$$ +DELIMITER ;$$ + +DELIMITER $$; +--error ER_SP_BAD_VAR_SHADOW +DECLARE + names VARCHAR(32); +BEGIN + SET names='[names]'; +END; +$$ +DELIMITER ;$$ + +DELIMITER $$; +--error ER_SP_BAD_VAR_SHADOW +DECLARE + password VARCHAR(32); +BEGIN + SET password='[password]'; +END; +$$ +DELIMITER ;$$ + +DELIMITER $$; +DECLARE + role VARCHAR(32); +BEGIN + SET role='[role]'; +END; +$$ +DELIMITER ;$$ + +--error ER_UNKNOWN_SYSTEM_VARIABLE +SELECT @@GLOBAL.names; +--error ER_UNKNOWN_SYSTEM_VARIABLE +SELECT @@GLOBAL.password; +--error ER_UNKNOWN_SYSTEM_VARIABLE +SELECT @@GLOBAL.role; + + +--echo # +--echo # End of 10.3 tests +--echo # 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-cursor-rowtype.test b/mysql-test/suite/compat/oracle/t/sp-cursor-rowtype.test index 31e28d5c8de..ba0ca9b6a60 100644 --- a/mysql-test/suite/compat/oracle/t/sp-cursor-rowtype.test +++ b/mysql-test/suite/compat/oracle/t/sp-cursor-rowtype.test @@ -1572,3 +1572,26 @@ DELIMITER ;$$ SELECT f1(); DROP FUNCTION f1; DROP TABLE t1; + + +--echo # +--echo # MDEV-17278 CURSOR FOR LOOP - ERROR: unexpected end of stream, read 0 bytes (SERVER CRASH) +--echo # + +CREATE TABLE t1 (id2 int, id int, en1 enum('aaa','a','b','c')); +INSERT INTO t1 VALUES(1,1,'aaa'),(2,2,'a'),(3,3,'b'),(4,4,'c'); + +DELIMITER $$; +CREATE PROCEDURE p1() +AS +BEGIN + FOR rec IN (SELECT en1 FROM t1) + LOOP + SELECT rec.en1; + END LOOP; +END; +$$ +DELIMITER ;$$ +CALL p1(); +DROP PROCEDURE p1; +DROP TABLE t1; diff --git a/mysql-test/suite/compat/oracle/t/sp-cursor.test b/mysql-test/suite/compat/oracle/t/sp-cursor.test index 5a8b7b69f67..03b8b016ee0 100644 --- a/mysql-test/suite/compat/oracle/t/sp-cursor.test +++ b/mysql-test/suite/compat/oracle/t/sp-cursor.test @@ -952,3 +952,38 @@ $$ DELIMITER ;$$ --enable_ps_protocol --disable_metadata + + +--echo # +--echo # MDEV-17387 MariaDB Server giving wrong error while executing select query from procedure +--echo # + +CREATE TABLE t1 +( + JOBN varchar(18) NOT NULL, + pk int(11) NOT NULL, + PRIMARY KEY (pk), + KEY (JOBN) +); + +DELIMITER $$; +CREATE PROCEDURE p1 +AS + lS NUMBER(10) :=0; + CURSOR cBPD IS SELECT * FROM t1 WHERE JOBN='x'; +BEGIN + FOR lbpd IN cBPD LOOP + lS:=lS+1; + END LOOP; +EXCEPTION + WHEN OTHERS THEN + BEGIN + SELECT SQLERRM; + END; +END; +$$ +DELIMITER ;$$ + +CALL p1(); +DROP PROCEDURE p1; +DROP TABLE t1; diff --git a/mysql-test/suite/compat/oracle/t/sp-package.test b/mysql-test/suite/compat/oracle/t/sp-package.test index e61dd37467c..04357a7487a 100644 --- a/mysql-test/suite/compat/oracle/t/sp-package.test +++ b/mysql-test/suite/compat/oracle/t/sp-package.test @@ -2624,3 +2624,60 @@ DELIMITER ;$$ CALL pkg1.p00; DROP PACKAGE pkg1; DROP TABLE t1; + + +--echo # +--echo # MDEV-17387 MariaDB Server giving wrong error while executing select query from procedure +--echo # + +CREATE TABLE t1 ( + CTR varchar(2) NOT NULL, + COR varchar(3) NOT NULL, + DATE datetime NOT NULL, + CHAN varchar(4) NOT NULL, + CNO varchar(20) NOT NULL, + JOBN varchar(18) NOT NULL, + C1 varchar(30) DEFAULT NULL, + C2 varchar(30) DEFAULT NULL, + TIME datetime DEFAULT NULL, + AMT decimal(12,2) DEFAULT NULL, + DT datetime NOT NULL, + pk int(11) NOT NULL, + PRIMARY KEY (pk), + KEY Indx1 (JOBN) +); + +DELIMITER $$; + +CREATE PACKAGE xyz IS + PROCEDURE xyz123(ctr IN VARCHAR2,Jn IN VARCHAR2,R OUT VARCHAR2); +END; +$$ + +CREATE OR REPLACE PACKAGE BODY xyz IS + PROCEDURE xyz123( + ctr IN VARCHAR2, + Jn IN VARCHAR2, + R OUT VARCHAR2) + AS + lS NUMBER(10) :=0; + CURSOR cBPD IS + SELECT CTR, COR, DATE, CHAN, CNO, C1, C2, TIME, AMT + FROM t1 WHERE JOBN=Jn; + BEGIN + FOR lbpd IN cBPD + LOOP + lS:=lS+1; + END LOOP; + EXCEPTION + WHEN OTHERS THEN + BEGIN + SELECT SQLERRM; + END; + END; +END $$ +DELIMITER ;$$ + +CALL xyz.xyz123(17,18,@R); +DROP PACKAGE xyz; +DROP TABLE t1; diff --git a/mysql-test/suite/compat/oracle/t/sp.test b/mysql-test/suite/compat/oracle/t/sp.test index de2a4a5e4e7..96b4cd59fbd 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 @@ -2344,3 +2344,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; |