diff options
Diffstat (limited to 'mysql-test/suite/compat/oracle/r/sp-param.result')
| -rw-r--r-- | mysql-test/suite/compat/oracle/r/sp-param.result | 423 |
1 files changed, 423 insertions, 0 deletions
diff --git a/mysql-test/suite/compat/oracle/r/sp-param.result b/mysql-test/suite/compat/oracle/r/sp-param.result new file mode 100644 index 00000000..bb415bd7 --- /dev/null +++ b/mysql-test/suite/compat/oracle/r/sp-param.result @@ -0,0 +1,423 @@ +SET sql_mode=ORACLE; +# +# MDEV-10596 Allow VARCHAR and VARCHAR2 without length as a data type of routine parameters and in RETURN clause +# +CREATE FUNCTION f1(param CHAR) RETURN CHAR AS BEGIN RETURN param; END;; +SHOW CREATE FUNCTION f1; +Function sql_mode Create Function character_set_client collation_connection Database Collation +f1 PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ORACLE,NO_KEY_OPTIONS,NO_TABLE_OPTIONS,NO_FIELD_OPTIONS,NO_AUTO_CREATE_USER,SIMULTANEOUS_ASSIGNMENT CREATE DEFINER="root"@"localhost" FUNCTION "f1"(param CHAR) RETURN varchar(2000) CHARSET latin1 COLLATE latin1_swedish_ci +AS BEGIN RETURN param; END latin1 latin1_swedish_ci latin1_swedish_ci +SELECT LENGTH(f1(REPEAT('a',2000)));; +LENGTH(f1(REPEAT('a',2000))) +2000 +CREATE TABLE t1 AS SELECT f1(REPEAT('a',2000)) AS a;; +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE "t1" ( + "a" varchar(2000) DEFAULT NULL +) +DROP TABLE t1; +DROP FUNCTION f1; +CREATE FUNCTION f1(param NCHAR) RETURN NCHAR AS BEGIN RETURN param; END;; +SHOW CREATE FUNCTION f1; +Function sql_mode Create Function character_set_client collation_connection Database Collation +f1 PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ORACLE,NO_KEY_OPTIONS,NO_TABLE_OPTIONS,NO_FIELD_OPTIONS,NO_AUTO_CREATE_USER,SIMULTANEOUS_ASSIGNMENT CREATE DEFINER="root"@"localhost" FUNCTION "f1"(param NCHAR) RETURN varchar(2000) CHARSET utf8mb3 COLLATE utf8mb3_general_ci +AS BEGIN RETURN param; END latin1 latin1_swedish_ci latin1_swedish_ci +SELECT LENGTH(f1(REPEAT('a',2000)));; +LENGTH(f1(REPEAT('a',2000))) +2000 +CREATE TABLE t1 AS SELECT f1(REPEAT('a',2000)) AS a;; +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE "t1" ( + "a" varchar(2000) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL +) +DROP TABLE t1; +DROP FUNCTION f1; +CREATE FUNCTION f1(param BINARY) RETURN BINARY AS BEGIN RETURN param; END;; +SHOW CREATE FUNCTION f1; +Function sql_mode Create Function character_set_client collation_connection Database Collation +f1 PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ORACLE,NO_KEY_OPTIONS,NO_TABLE_OPTIONS,NO_FIELD_OPTIONS,NO_AUTO_CREATE_USER,SIMULTANEOUS_ASSIGNMENT CREATE DEFINER="root"@"localhost" FUNCTION "f1"(param BINARY) RETURN varbinary(2000) +AS BEGIN RETURN param; END latin1 latin1_swedish_ci latin1_swedish_ci +SELECT LENGTH(f1(REPEAT('a',2000)));; +LENGTH(f1(REPEAT('a',2000))) +2000 +CREATE TABLE t1 AS SELECT f1(REPEAT('a',2000)) AS a;; +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE "t1" ( + "a" varbinary(2000) DEFAULT NULL +) +DROP TABLE t1; +DROP FUNCTION f1; +CREATE FUNCTION f1(param VARCHAR) RETURN VARCHAR AS BEGIN RETURN param; END;; +SHOW CREATE FUNCTION f1; +Function sql_mode Create Function character_set_client collation_connection Database Collation +f1 PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ORACLE,NO_KEY_OPTIONS,NO_TABLE_OPTIONS,NO_FIELD_OPTIONS,NO_AUTO_CREATE_USER,SIMULTANEOUS_ASSIGNMENT CREATE DEFINER="root"@"localhost" FUNCTION "f1"(param VARCHAR) RETURN varchar(4000) CHARSET latin1 COLLATE latin1_swedish_ci +AS BEGIN RETURN param; END latin1 latin1_swedish_ci latin1_swedish_ci +SELECT LENGTH(f1(REPEAT('a',4000)));; +LENGTH(f1(REPEAT('a',4000))) +4000 +CREATE TABLE t1 AS SELECT f1(REPEAT('a',4000)) AS a;; +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE "t1" ( + "a" varchar(4000) DEFAULT NULL +) +DROP TABLE t1; +DROP FUNCTION f1; +CREATE FUNCTION f1(param VARCHAR2) RETURN VARCHAR2 AS BEGIN RETURN param; END;; +SHOW CREATE FUNCTION f1; +Function sql_mode Create Function character_set_client collation_connection Database Collation +f1 PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ORACLE,NO_KEY_OPTIONS,NO_TABLE_OPTIONS,NO_FIELD_OPTIONS,NO_AUTO_CREATE_USER,SIMULTANEOUS_ASSIGNMENT CREATE DEFINER="root"@"localhost" FUNCTION "f1"(param VARCHAR2) RETURN varchar(4000) CHARSET latin1 COLLATE latin1_swedish_ci +AS BEGIN RETURN param; END latin1 latin1_swedish_ci latin1_swedish_ci +SELECT LENGTH(f1(REPEAT('a',4000)));; +LENGTH(f1(REPEAT('a',4000))) +4000 +CREATE TABLE t1 AS SELECT f1(REPEAT('a',4000)) AS a;; +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE "t1" ( + "a" varchar(4000) DEFAULT NULL +) +DROP TABLE t1; +DROP FUNCTION f1; +CREATE FUNCTION f1(param NVARCHAR) RETURN NVARCHAR AS BEGIN RETURN param; END;; +SHOW CREATE FUNCTION f1; +Function sql_mode Create Function character_set_client collation_connection Database Collation +f1 PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ORACLE,NO_KEY_OPTIONS,NO_TABLE_OPTIONS,NO_FIELD_OPTIONS,NO_AUTO_CREATE_USER,SIMULTANEOUS_ASSIGNMENT CREATE DEFINER="root"@"localhost" FUNCTION "f1"(param NVARCHAR) RETURN varchar(4000) CHARSET utf8mb3 COLLATE utf8mb3_general_ci +AS BEGIN RETURN param; END latin1 latin1_swedish_ci latin1_swedish_ci +SELECT LENGTH(f1(REPEAT('a',4000)));; +LENGTH(f1(REPEAT('a',4000))) +4000 +CREATE TABLE t1 AS SELECT f1(REPEAT('a',4000)) AS a;; +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE "t1" ( + "a" varchar(4000) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL +) +DROP TABLE t1; +DROP FUNCTION f1; +CREATE FUNCTION f1(param VARBINARY) RETURN VARBINARY AS BEGIN RETURN param; END;; +SHOW CREATE FUNCTION f1; +Function sql_mode Create Function character_set_client collation_connection Database Collation +f1 PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ORACLE,NO_KEY_OPTIONS,NO_TABLE_OPTIONS,NO_FIELD_OPTIONS,NO_AUTO_CREATE_USER,SIMULTANEOUS_ASSIGNMENT CREATE DEFINER="root"@"localhost" FUNCTION "f1"(param VARBINARY) RETURN varbinary(4000) +AS BEGIN RETURN param; END latin1 latin1_swedish_ci latin1_swedish_ci +SELECT LENGTH(f1(REPEAT('a',4000)));; +LENGTH(f1(REPEAT('a',4000))) +4000 +CREATE TABLE t1 AS SELECT f1(REPEAT('a',4000)) AS a;; +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE "t1" ( + "a" varbinary(4000) DEFAULT NULL +) +DROP TABLE t1; +DROP FUNCTION f1; +CREATE FUNCTION f1(param RAW) RETURN RAW AS BEGIN RETURN param; END;; +SHOW CREATE FUNCTION f1; +Function sql_mode Create Function character_set_client collation_connection Database Collation +f1 PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ORACLE,NO_KEY_OPTIONS,NO_TABLE_OPTIONS,NO_FIELD_OPTIONS,NO_AUTO_CREATE_USER,SIMULTANEOUS_ASSIGNMENT CREATE DEFINER="root"@"localhost" FUNCTION "f1"(param RAW) RETURN varbinary(4000) +AS BEGIN RETURN param; END latin1 latin1_swedish_ci latin1_swedish_ci +SELECT LENGTH(f1(REPEAT('a',4000)));; +LENGTH(f1(REPEAT('a',4000))) +4000 +CREATE TABLE t1 AS SELECT f1(REPEAT('a',4000)) AS a;; +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE "t1" ( + "a" varbinary(4000) DEFAULT NULL +) +DROP TABLE t1; +DROP FUNCTION f1; + +MDEV-13919 sql_mode=ORACLE: Derive length of VARCHAR SP parameters with no length from actual parameters + +set sql_mode= 'oracle,strict_trans_tables'; +CREATE OR REPLACE PROCEDURE p1(pinout INOUT varchar, pin IN varchar) +AS +BEGIN +pinout:=pin; +END; +/ +call p1(@w,'0123456789') +/ +declare w varchar(10); +begin +call p1(w,'0123456789'); +end; +/ +declare w varchar(5); +begin +call p1(w,'0123456789'); +end; +/ +ERROR 22001: Data too long for column 'pinout' at row 0 +declare w varchar(20); +begin +w:='aaa'; +call p1(w,'0123456789'); +end; +/ +declare w varchar(8); +begin +w:='aaa'; +call p1(w,'0123456789'); +end; +/ +ERROR 22001: Data too long for column 'pinout' at row 0 +declare str varchar(6000); +pout varchar(6000); +begin +str:=lpad('x',6000,'y'); +call p1(pout,str); +select length(pout); +end; +/ +length(pout) +6000 +declare str varchar(6000); +pout varchar(4000); +begin +str:=lpad('x',6000,'y'); +call p1(pout,str); +select length(pout); +end; +/ +ERROR 22001: Data too long for column 'pinout' at row 0 +declare str varchar(40000); +pout varchar(60000); +begin +str:=lpad('x',40000,'y'); +call p1(pout,str); +select length(pout); +end; +/ +length(pout) +40000 +declare str text(80000); +pout text(80000); +begin +str:=lpad('x',80000,'y'); +call p1(pout,str); +select length(pout); +end; +/ +ERROR 22001: Data too long for column 'pin' at row 0 +declare str text(80000); +pout text(80000); +begin +str:=lpad('x',60000,'y'); +call p1(pout,str); +select length(pout); +end; +/ +length(pout) +60000 +drop procedure p1 +/ +SET sql_mode=ORACLE; +CREATE PROCEDURE p1(pinout INOUT varchar, pin IN varchar) +AS +BEGIN +pinout:=pin; +END; +/ +CREATE PROCEDURE p2(len INT) +AS +pinout VARCHAR(10); +pin VARCHAR(30); +BEGIN +pin:= REPEAT('x', len); +p1(pinout, pin); +SELECT LENGTH(pinout); +END; +/ +CALL p2(10); +LENGTH(pinout) +10 +CALL p2(11); +LENGTH(pinout) +10 +Warnings: +Warning 1265 Data truncated for column 'pinout' at row 0 +DROP PROCEDURE p1; +DROP PROCEDURE p2; +SET sql_mode=ORACLE; +CREATE FUNCTION f1(pin VARCHAR, padlen INT) RETURN TEXT +AS +BEGIN +pin:=LPAD(pin, padlen); +RETURN pin; +END; +/ +CREATE PROCEDURE p2(padlen INT) AS +str TEXT :='x'; +BEGIN +SELECT LENGTH(f1(str,padlen)); +END; +/ +CALL p2(65535); +LENGTH(f1(str,padlen)) +65532 +Warnings: +Warning 1265 Data truncated for column 'pin' at row 0 +CALL p2(65536); +LENGTH(f1(str,padlen)) +65532 +Warnings: +Warning 1265 Data truncated for column 'pin' at row 0 +DROP PROCEDURE p2; +DROP FUNCTION f1; +SET sql_mode='ORACLE,STRICT_TRANS_TABLES'; +CREATE PROCEDURE p1(pinout INOUT VARCHAR CHARACTER SET utf8, +pin IN VARCHAR CHARACTER SET utf8) +AS +BEGIN +pinout:=pin; +END; +/ +CREATE PROCEDURE p2(padlen INT) AS +str VARCHAR(40000) CHARACTER SET latin1; +pout VARCHAR(60000) CHARACTER SET latin1; +BEGIN +str:=lpad('x',padlen,'y'); +p1(pout,str); +SELECT length(pout); +END; +/ +CALL p2(21844); +length(pout) +21844 +CALL p2(21845); +ERROR 22001: Data too long for column 'pin' at row 0 +CALL p2(21846); +ERROR 22001: Data too long for column 'pin' at row 0 +DROP PROCEDURE p2; +DROP PROCEDURE p1; +SET sql_mode='ORACLE,STRICT_TRANS_TABLES'; +CREATE PROCEDURE p1(pinout INOUT VARCHAR CHARACTER SET utf8, +pin IN VARCHAR CHARACTER SET utf8) +AS +BEGIN +pinout:=pin; +END; +/ +CREATE PROCEDURE p2(padlen INT) AS +str TEXT CHARACTER SET utf8; +pout TEXT CHARACTER SET utf8; +BEGIN +str:=lpad('x',padlen,'y'); +p1(pout,str); +SELECT length(pout); +END; +/ +CALL p2(21844); +length(pout) +21844 +CALL p2(21845); +ERROR 22001: Data too long for column 'pin' at row 0 +CALL p2(21846); +ERROR 22001: Data too long for column 'pin' at row 0 +DROP PROCEDURE p2; +DROP PROCEDURE p1; +SET sql_mode='ORACLE,STRICT_TRANS_TABLES'; +CREATE FUNCTION f1(pin VARCHAR CHARACTER SET latin1, padlen INT) RETURN TEXT +AS +BEGIN +pin:=LPAD(pin, padlen); +RETURN pin; +END; +/ +CREATE PROCEDURE p2(padlen INT) AS +str TEXT CHARACTER SET latin1 :='x'; +BEGIN +SELECT LENGTH(f1(str,padlen)); +END; +/ +CALL p2(65532); +LENGTH(f1(str,padlen)) +65532 +CALL p2(65533); +ERROR 22001: Data too long for column 'pin' at row 0 +CALL p2(65534); +ERROR 22001: Data too long for column 'pin' at row 0 +CALL p2(65535); +ERROR 22001: Data too long for column 'pin' at row 0 +CALL p2(65536); +ERROR 22001: Data too long for column 'pin' at row 0 +DROP PROCEDURE p2; +DROP FUNCTION f1; +SET sql_mode='ORACLE,STRICT_TRANS_TABLES'; +CREATE FUNCTION f1(pin VARCHAR CHARACTER SET utf8, padlen INT) RETURN TEXT +AS +BEGIN +pin:=LPAD(pin, padlen); +RETURN pin; +END; +/ +CREATE PROCEDURE p2(padlen INT) AS +str TEXT CHARACTER SET utf8 := 'x'; +BEGIN +SELECT LENGTH(f1(str,padlen)); +END; +/ +CALL p2(21844); +LENGTH(f1(str,padlen)) +21844 +CALL p2(21845); +ERROR 22001: Data too long for column 'pin' at row 0 +CALL p2(21846); +ERROR 22001: Data too long for column 'pin' at row 0 +DROP PROCEDURE p2; +DROP FUNCTION f1; +SET sql_mode='ORACLE,STRICT_TRANS_TABLES'; +CREATE FUNCTION f1(pin VARCHAR CHARACTER SET utf8, padlen INT) RETURN TEXT +AS +BEGIN +pin:=LPAD(pin, padlen); +RETURN pin; +END; +/ +CREATE PROCEDURE p2(padlen INT) AS +str TEXT CHARACTER SET latin1 := 'x'; +BEGIN +SELECT LENGTH(f1(str,padlen)); +END; +/ +CALL p2(21844); +LENGTH(f1(str,padlen)) +21844 +CALL p2(21845); +ERROR 22001: Data too long for column 'pin' at row 0 +CALL p2(21846); +ERROR 22001: Data too long for column 'pin' at row 0 +DROP PROCEDURE p2; +DROP FUNCTION f1; +SET sql_mode='ORACLE,STRICT_TRANS_TABLES'; +CREATE FUNCTION f1(pin VARCHAR CHARACTER SET latin1, padlen INT) RETURN TEXT +AS +BEGIN +pin:=LPAD(pin, padlen); +RETURN pin; +END; +/ +CREATE PROCEDURE p2(padlen INT) AS +str TEXT CHARACTER SET utf8 := 'x'; +BEGIN +SELECT LENGTH(f1(str,padlen)); +END; +/ +CALL p2(65532); +LENGTH(f1(str,padlen)) +65532 +CALL p2(65533); +ERROR 22001: Data too long for column 'pin' at row 0 +CALL p2(65534); +ERROR 22001: Data too long for column 'pin' at row 0 +CALL p2(65535); +ERROR 22001: Data too long for column 'pin' at row 0 +CALL p2(65536); +ERROR 22001: Data too long for column 'pin' at row 0 +DROP PROCEDURE p2; +DROP FUNCTION f1; |