mirror/mariadb
1
0
Fork 0
mirror of https://github.com/MariaDB/server.git synced 2025-02-12 00:15:35 +01:00
Code Issues Projects Releases Packages Wiki Activity Actions
mariadb/mysql-test/suite/compat/oracle/t/sp-param.test
Alexander Barkov 556a40dce0 MDEV-35229 NOCOPY has become reserved word bringing wide incompatibility 
This patch was suggested by Sergei Golubchik.

It reverts the second patch from the PR:

  commit fa5eeb4931
    Fixed ALTER TABLE NOCOPY keyword failure

and adds NOCOPY_SYM into keyword_func_sp_var_and_label.

The price is one extra shift/recuce conflict in yy_oracle.yy.
This should to tolerable.
2024-10-30 13:58:20 +04:00

571 lines
10 KiB
Text
Raw Permalink Blame History

--source include/test_db_charset_latin1.inc
SET sql_mode=ORACLE;
--echo #
--echo # MDEV-10596 Allow VARCHAR and VARCHAR2 without length as a data type of routine parameters and in RETURN clause
--echo #
--let type = CHAR
--let length = 2000
--source sp-param.inc
--let type = NCHAR
--let length = 2000
--source sp-param.inc
--let type = BINARY
--let length = 2000
--source sp-param.inc
--let type = VARCHAR
--let length = 4000
--source sp-param.inc
--let type = VARCHAR2
--let length = 4000
--source sp-param.inc
--let type = NVARCHAR
--let length = 4000
--source sp-param.inc
--let type = VARBINARY
--let length = 4000
--source sp-param.inc
--let type = RAW
--let length = 4000
--source sp-param.inc
--echo
--echo MDEV-13919 sql_mode=ORACLE: Derive length of VARCHAR SP parameters with no length from actual parameters
--echo
set sql_mode= 'oracle,strict_trans_tables';
delimiter /;
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;
/
--error ER_DATA_TOO_LONG
declare w varchar(5);
begin
call p1(w,'0123456789');
end;
/
declare w varchar(20);
begin
w:='aaa';
call p1(w,'0123456789');
end;
/
--error ER_DATA_TOO_LONG
declare w varchar(8);
begin
w:='aaa';
call p1(w,'0123456789');
end;
/
declare str varchar(6000);
pout varchar(6000);
begin
str:=lpad('x',6000,'y');
call p1(pout,str);
select length(pout);
end;
/
--error ER_DATA_TOO_LONG
declare str varchar(6000);
pout varchar(4000);
begin
str:=lpad('x',6000,'y');
call p1(pout,str);
select length(pout);
end;
/
declare str varchar(40000);
pout varchar(60000);
begin
str:=lpad('x',40000,'y');
call p1(pout,str);
select length(pout);
end;
/
--error ER_DATA_TOO_LONG
declare str text(80000);
pout text(80000);
begin
str:=lpad('x',80000,'y');
call p1(pout,str);
select length(pout);
end;
/
declare str text(80000);
pout text(80000);
begin
str:=lpad('x',60000,'y');
call p1(pout,str);
select length(pout);
end;
/
drop procedure p1
/
DELIMITER ;/
#
# Procedure, non-strict mode
#
SET sql_mode=ORACLE;
DELIMITER /;
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;
/
DELIMITER ;/
CALL p2(10);
CALL p2(11);
DROP PROCEDURE p1;
DROP PROCEDURE p2;
#
# Function, not-strict mode
#
SET sql_mode=ORACLE;
DELIMITER /;
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;
/
DELIMITER ;/
CALL p2(65535);
CALL p2(65536);
DROP PROCEDURE p2;
DROP FUNCTION f1;
#
# Procedure, utf8 formal parameter, latin actual parameter
#
SET sql_mode='ORACLE,STRICT_TRANS_TABLES';
DELIMITER /;
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;
/
DELIMITER ;/
CALL p2(21844);
--error ER_DATA_TOO_LONG
CALL p2(21845);
--error ER_DATA_TOO_LONG
CALL p2(21846);
DROP PROCEDURE p2;
DROP PROCEDURE p1;
#
# Procedure, utf8 formal parameter, utf8 actual parameter
#
SET sql_mode='ORACLE,STRICT_TRANS_TABLES';
DELIMITER /;
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;
/
DELIMITER ;/
CALL p2(21844);
--error ER_DATA_TOO_LONG
CALL p2(21845);
--error ER_DATA_TOO_LONG
CALL p2(21846);
DROP PROCEDURE p2;
DROP PROCEDURE p1;
#
# Function, latin1 formal parameter, latin1 actual parameter
#
SET sql_mode='ORACLE,STRICT_TRANS_TABLES';
DELIMITER /;
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;
/
DELIMITER ;/
CALL p2(65532);
--error ER_DATA_TOO_LONG
CALL p2(65533);
--error ER_DATA_TOO_LONG
CALL p2(65534);
--error ER_DATA_TOO_LONG
CALL p2(65535);
--error ER_DATA_TOO_LONG
CALL p2(65536);
DROP PROCEDURE p2;
DROP FUNCTION f1;
#
# Function, utf8 formal parameter, utf8 actual parameter
#
SET sql_mode='ORACLE,STRICT_TRANS_TABLES';
DELIMITER /;
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;
/
DELIMITER ;/
CALL p2(21844);
--error ER_DATA_TOO_LONG
CALL p2(21845);
--error ER_DATA_TOO_LONG
CALL p2(21846);
DROP PROCEDURE p2;
DROP FUNCTION f1;
#
# Function, utf8 formal parameter, latin1 actual parameter
#
SET sql_mode='ORACLE,STRICT_TRANS_TABLES';
DELIMITER /;
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;
/
DELIMITER ;/
CALL p2(21844);
--error ER_DATA_TOO_LONG
CALL p2(21845);
--error ER_DATA_TOO_LONG
CALL p2(21846);
DROP PROCEDURE p2;
DROP FUNCTION f1;
#
# Function, latin1 formal parameter, utf8 actual parameter
#
SET sql_mode='ORACLE,STRICT_TRANS_TABLES';
DELIMITER /;
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;
/
DELIMITER ;/
CALL p2(65532);
--error ER_DATA_TOO_LONG
CALL p2(65533);
--error ER_DATA_TOO_LONG
CALL p2(65534);
--error ER_DATA_TOO_LONG
CALL p2(65535);
--error ER_DATA_TOO_LONG
CALL p2(65536);
DROP PROCEDURE p2;
DROP FUNCTION f1;
--source include/test_db_charset_restore.inc
--echo #
--echo # MDEV-34316 sql_mode=ORACLE: Ignore the NOCOPY keyword in stored routine parameters
--echo #
SET sql_mode=ORACLE;
--echo #
--echo # sql_mode=ORACLE. Test with function
--echo #
DELIMITER /;
CREATE OR REPLACE FUNCTION example_func(
p_in1 IN VARCHAR(255),
p_in2 IN NOCOPY VARCHAR(255),
p_out1 OUT INT,
p_out2 OUT NOCOPY INT,
p_in_out1 IN OUT VARCHAR(255),
p_in_out2 IN OUT NOCOPY VARCHAR(255),
p_in_out3 INOUT NUMBER,
p_in_out4 INOUT NOCOPY NUMBER) RETURN NUMBER AS
BEGIN
RETURN 0;
END;
/
DELIMITER ;/
DROP FUNCTION example_func;
--echo #
--echo # sql_mode=ORACLE. Test with procedure
--echo #
DELIMITER /;
CREATE OR REPLACE PROCEDURE example_proc(
p_in1 IN VARCHAR(255),
p_in2 IN NOCOPY VARCHAR(255),
p_out1 OUT INT,
p_out2 OUT NOCOPY INT,
p_in_out1 IN OUT VARCHAR(255),
p_in_out2 IN OUT NOCOPY VARCHAR(255),
p_in_out3 INOUT NUMBER,
p_in_out4 INOUT NOCOPY NUMBER) AS
BEGIN
END;
/
DELIMITER ;/
DROP PROCEDURE example_proc;
SET sql_mode=DEFAULT;
DELIMITER $$;
--echo #
--echo # sql_mode=DEFAULT to perform the negative test case. Test with function, IN NOCOPY
--echo #
--error ER_UNKNOWN_DATA_TYPE
CREATE OR REPLACE FUNCTION example_func(IN NOCOPY p_in INT) RETURNS INT
BEGIN
RETURN 0;
END;
$$
--echo #
--echo # sql_mode=DEFAULT to perform the negative test case. Test with function, OUT NOCOPY
--echo #
--error ER_UNKNOWN_DATA_TYPE
CREATE OR REPLACE FUNCTION example_func(OUT NOCOPY p_out INT) RETURNS INT
BEGIN
RETURN 0;
END;
$$
--echo #
--echo # sql_mode=DEFAULT to perform the negative test case. Test with function, INOUT NOCOPY
--echo #
--error ER_UNKNOWN_DATA_TYPE
CREATE OR REPLACE FUNCTION example_func(INOUT NOCOPY p_inout INT) RETURNS INT
BEGIN
RETURN 0;
END;
$$
--echo #
--echo # sql_mode=DEFAULT to perform the negative test case. Test with procedure, IN NOCOPY
--echo #
--error ER_UNKNOWN_DATA_TYPE
CREATE OR REPLACE PROCEDURE example_proc(IN NOCOPY p_in INT)
BEGIN
END;
$$
--echo #
--echo # sql_mode=DEFAULT to perform the negative test case. Test with procedure, OUT NOCOPY
--echo #
--error ER_UNKNOWN_DATA_TYPE
CREATE OR REPLACE PROCEDURE example_proc(OUT NOCOPY p_out INT)
BEGIN
END;
$$
--echo #
--echo # sql_mode=DEFAULT to perform the negative test case. Test with procedure, INOUT NOCOPY
--echo #
--error ER_UNKNOWN_DATA_TYPE
CREATE OR REPLACE PROCEDURE example_proc(INOUT NOCOPY p_inout INT)
BEGIN
END;
$$
DELIMITER ;$$
--echo #
--echo # MDEV-35229 NOCOPY has become reserved word bringing wide incompatibility
--echo #
SET sql_mode=ORACLE;
--let keyword=nocopy
--source include/keyword_non_reserved.inc
# Statements like "CREATE FUNCTION nocopy" and "SELECT nocopy()" below
# erroneously print a warning:
# This function 'nocopy' has the same name as a native function
# Though it's not really a function, it's just a keyword.
# The warning is not printed with ps protocol. Let's disable ps protocol.
--disable_ps_protocol
# nocopy is a parameter name
DELIMITER $$;
CREATE FUNCTION nocopy (nocopy INT) RETURN INT AS
BEGIN
RETURN nocopy;
END;
$$
DELIMITER ;$$
SELECT nocopy(1);
DROP FUNCTION nocopy;
# The first nocopy is a parameter name, the second nocopy - is the mode
DELIMITER $$;
CREATE FUNCTION nocopy (nocopy nocopy INT) RETURN INT AS
BEGIN
RETURN nocopy;
END;
$$
DELIMITER ;$$
SELECT nocopy(1);
DROP FUNCTION nocopy;
--enable_ps_protocol
# nocopy is a parameter name
DELIMITER $$;
CREATE PROCEDURE nocopy (nocopy INT) AS
BEGIN
SELECT nocopy;
END;
$$
DELIMITER ;$$
CALL nocopy(1);
DROP PROCEDURE nocopy;
# The first nocopy is a parameter name, the second nocopy - is the mode
DELIMITER $$;
CREATE PROCEDURE nocopy (nocopy nocopy INT) AS
BEGIN
SELECT nocopy;
END;
$$
DELIMITER ;$$
CALL nocopy(1);
DROP PROCEDURE nocopy;
DELIMITER $$;
DECLARE
nocopy INT := 1;
BEGIN
<<nocopy>>
WHILE 1
LOOP
SELECT nocopy;
LEAVE nocopy;
END LOOP;
END;
$$
DELIMITER ;$$
--echo #
--echo # End of 11.7 tests
--echo #
Reference in a new issue View git blame Copy permalink