--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 #