diff options
Diffstat (limited to 'mysql-test/suite/compat/oracle')
| -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 | 30 | ||||
| -rw-r--r-- | mysql-test/suite/compat/oracle/r/sp-package-mdl.result | 4 | ||||
| -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/r/versioning.result | 8 | ||||
| -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 | ||||
| -rw-r--r-- | mysql-test/suite/compat/oracle/t/versioning.test | 10 |
15 files changed, 626 insertions, 27 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..a09459ad7cd 100644 --- a/mysql-test/suite/compat/oracle/r/sp-cursor.result +++ b/mysql-test/suite/compat/oracle/r/sp-cursor.result @@ -459,7 +459,7 @@ CALL p1('b1'); msg Fetched a record a=0 Warnings: -Warning 1366 Incorrect integer value: 'b1' for column 'p_a' at row 1 +Warning 1366 Incorrect integer value: 'b1' for column ``.``.`p_a` at row 1 DROP PROCEDURE p1; # # One parameter in SELECT list + subselect @@ -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-mdl.result b/mysql-test/suite/compat/oracle/r/sp-package-mdl.result index 18cc834461c..bb46341f202 100644 --- a/mysql-test/suite/compat/oracle/r/sp-package-mdl.result +++ b/mysql-test/suite/compat/oracle/r/sp-package-mdl.result @@ -61,8 +61,8 @@ TABLE_NAME pkg1.p1 CONN 2 INFO DROP PACKAGE pkg1 STATE Waiting for stored package body metadata lock -LOCK_MODE MDL_INTENTION_EXCLUSIVE -LOCK_TYPE Global read lock +LOCK_MODE MDL_BACKUP_DDL +LOCK_TYPE Backup lock TABLE_NAME CONN 2 INFO DROP PACKAGE pkg1 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/r/versioning.result b/mysql-test/suite/compat/oracle/r/versioning.result index ebedcf0f462..bbecfa1f6b1 100644 --- a/mysql-test/suite/compat/oracle/r/versioning.result +++ b/mysql-test/suite/compat/oracle/r/versioning.result @@ -14,3 +14,11 @@ SELECT * FROM t1 FOR SYSTEM_TIME AS OF (NOW()+INTERVAL 10 YEAR); a 20 DROP TABLE t1; +# +# MDEV-17959 Assertion `opt_bootstrap || mysql_parse_status || thd->lex->select_stack_top == 0' failed in parse_sql upon DELETE HISTORY under ORACLE mode +# +SET SQL_MODE= ORACLE; +CREATE TABLE t1 (a INT); +DELETE HISTORY FROM t1; +ERROR HY000: Table `t1` is not system-versioned +DROP TABLE t1; 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; diff --git a/mysql-test/suite/compat/oracle/t/versioning.test b/mysql-test/suite/compat/oracle/t/versioning.test index d70058c56e4..abcca8c588a 100644 --- a/mysql-test/suite/compat/oracle/t/versioning.test +++ b/mysql-test/suite/compat/oracle/t/versioning.test @@ -11,3 +11,13 @@ INSERT INTO t1 VALUES (20); SELECT * FROM t1 FOR SYSTEM_TIME ALL; SELECT * FROM t1 FOR SYSTEM_TIME AS OF (NOW()+INTERVAL 10 YEAR); DROP TABLE t1; + +--echo # +--echo # MDEV-17959 Assertion `opt_bootstrap || mysql_parse_status || thd->lex->select_stack_top == 0' failed in parse_sql upon DELETE HISTORY under ORACLE mode +--echo # + +SET SQL_MODE= ORACLE; +CREATE TABLE t1 (a INT); +--error ER_VERS_NOT_VERSIONED +DELETE HISTORY FROM t1; +DROP TABLE t1; |