mirror of
https://github.com/MariaDB/server.git
synced 2026-01-29 06:49:08 +01:00
453 lines
11 KiB
Text
453 lines
11 KiB
Text
--echo # Start of 11.8 tests
|
|
|
|
--echo #
|
|
--echo # MDEV-10862 Stored procedures: default values for parameters (optional parameters)
|
|
--echo #
|
|
|
|
SET sql_mode=oracle;
|
|
|
|
--echo #
|
|
--echo # Basic default parameter test for procedures
|
|
--echo #
|
|
DELIMITER $$;
|
|
CREATE OR REPLACE PROCEDURE p1(par1 IN INT, par2 IN INT DEFAULT 1)
|
|
AS
|
|
BEGIN
|
|
SELECT par1, par2 FROM DUAL;
|
|
END;
|
|
$$
|
|
DELIMITER ;$$
|
|
CALL p1(2,3);
|
|
CALL p1(2);
|
|
--error ER_SP_WRONG_NO_OF_ARGS
|
|
CALL p1(2,3,4);
|
|
--error ER_SP_WRONG_NO_OF_ARGS
|
|
CALL p1();
|
|
|
|
DROP PROCEDURE p1;
|
|
|
|
--echo #
|
|
--echo # Basic default parameter test for functions
|
|
--echo #
|
|
DELIMITER $$;
|
|
CREATE OR REPLACE FUNCTION f1(par1 INT, par2 INT DEFAULT 1) RETURN INT
|
|
AS
|
|
BEGIN
|
|
RETURN par1 + par2;
|
|
END;
|
|
$$
|
|
DELIMITER ;$$
|
|
SELECT f1(2,3) FROM DUAL;
|
|
SELECT f1(2) FROM DUAL;
|
|
--error ER_SP_WRONG_NO_OF_ARGS
|
|
SELECT f1(2,3,4) FROM DUAL;
|
|
--error ER_SP_WRONG_NO_OF_ARGS
|
|
SELECT f1() FROM DUAL;
|
|
|
|
DROP FUNCTION f1;
|
|
|
|
--echo #
|
|
--echo # OUT parameters cannot have default values
|
|
--echo #
|
|
DELIMITER $$;
|
|
--error ER_INVALID_DEFAULT_PARAM
|
|
CREATE OR REPLACE PROCEDURE p1(par1 IN INT DEFAULT 1, par2 OUT INT DEFAULT 2)
|
|
AS
|
|
BEGIN
|
|
SELECT par1 FROM DUAL;
|
|
END;
|
|
$$
|
|
DELIMITER ;$$
|
|
|
|
--echo #
|
|
--echo # INOUT parameters cannot have default values
|
|
--echo #
|
|
DELIMITER $$;
|
|
--error ER_INVALID_DEFAULT_PARAM
|
|
CREATE OR REPLACE PROCEDURE p1(par1 INOUT INT DEFAULT 1, par2 OUT INT DEFAULT 2)
|
|
AS
|
|
BEGIN
|
|
SELECT par1 FROM DUAL;
|
|
END;
|
|
$$
|
|
DELIMITER ;$$
|
|
|
|
--echo #
|
|
--echo # Test various data types
|
|
--echo #
|
|
CREATE TABLE t1 (a INT, b VARCHAR(10));
|
|
|
|
CREATE DATABASE d1;
|
|
USE d1;
|
|
CREATE TABLE t1 (a INT, b VARCHAR(10));
|
|
|
|
USE test;
|
|
DELIMITER $$;
|
|
CREATE OR REPLACE PROCEDURE p1(
|
|
par1 IN INT DEFAULT 1,
|
|
par2 IN VARCHAR(10) DEFAULT 'abc',
|
|
par3 IN DATETIME DEFAULT '2010-01-01 12:34:56',
|
|
par4 IN DECIMAL(10,2) DEFAULT 123.45,
|
|
par5 IN FLOAT DEFAULT 123.45,
|
|
par6 IN DOUBLE DEFAULT 123.45,
|
|
par7 IN CHAR DEFAULT 'a',
|
|
par8 IN BOOLEAN DEFAULT TRUE,
|
|
par9 IN ROW(a INT, b VARCHAR(10)) DEFAULT ROW(1, 'abc'),
|
|
par10 IN t1.a%TYPE DEFAULT 10,
|
|
par11 IN d1.t1.a%TYPE DEFAULT 10,
|
|
par12 IN t1%ROWTYPE DEFAULT (1, 'cde'),
|
|
par13 IN d1.t1%ROWTYPE DEFAULT (1, 'cde')
|
|
)
|
|
AS
|
|
BEGIN
|
|
SELECT par1, par2, par3, par4, par5, par6, par7, par8, par9.b, par10, par11, par12.b, par13.b FROM DUAL;
|
|
END;
|
|
$$
|
|
DELIMITER ;$$
|
|
CALL p1();
|
|
|
|
# PARAMETER_DEFAULT column test- differnt types
|
|
SELECT * FROM information_schema.PARAMETERS WHERE SPECIFIC_NAME = 'p1';
|
|
|
|
DROP PROCEDURE p1;
|
|
DROP TABLE t1;
|
|
DROP DATABASE d1;
|
|
|
|
--echo #
|
|
--echo # Expression as default value is not evaluated if value is provided
|
|
--echo #
|
|
CREATE TABLE t1 (a INT AUTO_INCREMENT PRIMARY KEY);
|
|
DELIMITER $$;
|
|
CREATE FUNCTION f1() RETURN INT
|
|
AS
|
|
BEGIN
|
|
INSERT INTO t1 VALUES (NULL);
|
|
RETURN LAST_INSERT_ID();
|
|
END;
|
|
$$
|
|
CREATE PROCEDURE p1(par1 IN INT DEFAULT f1())
|
|
AS
|
|
BEGIN
|
|
SELECT par1 FROM DUAL;
|
|
END;
|
|
$$
|
|
DELIMITER ;$$
|
|
|
|
--disable_ps_protocol
|
|
CALL p1();
|
|
CALL p1(10);
|
|
CALL p1();
|
|
--enable_ps_protocol
|
|
|
|
# PARAMETER_DEFAULT column test- function as default value
|
|
SELECT * FROM information_schema.PARAMETERS WHERE SPECIFIC_NAME = 'p1';
|
|
|
|
DROP PROCEDURE p1;
|
|
DROP FUNCTION f1;
|
|
DROP TABLE t1;
|
|
|
|
--echo #
|
|
--echo # Subquery as default value
|
|
--echo # NOTE: Oracle does not allow this
|
|
--echo #
|
|
CREATE TABLE t1 (a INT);
|
|
INSERT INTO t1 VALUES (1);
|
|
DELIMITER $$;
|
|
CREATE PROCEDURE p1(par1 IN INT DEFAULT (SELECT a FROM t1))
|
|
AS
|
|
BEGIN
|
|
SELECT par1 FROM DUAL;
|
|
END;
|
|
$$
|
|
DELIMITER ;$$
|
|
|
|
CALL p1();
|
|
CALL p1(10);
|
|
|
|
# PARAMETER_DEFAULT column test- subquery as default
|
|
SELECT * FROM information_schema.PARAMETERS WHERE SPECIFIC_NAME = 'p1';
|
|
|
|
DROP PROCEDURE p1;
|
|
DROP TABLE t1;
|
|
|
|
--echo #
|
|
--echo # Default value from another parameter
|
|
--echo # NOTE: Oracle does not allow this
|
|
--echo #
|
|
DELIMITER $$;
|
|
CREATE PROCEDURE p1(par1 IN INT, par2 IN INT DEFAULT par1)
|
|
AS
|
|
BEGIN
|
|
SELECT par1, par2 FROM DUAL;
|
|
END;
|
|
$$
|
|
DELIMITER ;$$
|
|
|
|
CALL p1(10);
|
|
CALL p1(10,20);
|
|
|
|
# PARAMETER_DEFAULT column test- default value referencing another parameter
|
|
SELECT * FROM information_schema.PARAMETERS WHERE SPECIFIC_NAME = 'p1';
|
|
|
|
DROP PROCEDURE p1;
|
|
|
|
--echo #
|
|
--echo # Default value referencing self
|
|
--echo # NOTE: Oracle does not allow this
|
|
--echo #
|
|
DELIMITER $$;
|
|
--error ER_SP_UNDECLARED_VAR
|
|
CREATE PROCEDURE p1(par1 IN INT DEFAULT par1)
|
|
AS
|
|
BEGIN
|
|
NULL;
|
|
END;
|
|
$$
|
|
DELIMITER ;$$
|
|
|
|
--echo #
|
|
--echo # Default parameters in package's routines
|
|
--echo #
|
|
DELIMITER $$;
|
|
CREATE PACKAGE p1 AS
|
|
PROCEDURE p1(par1 IN INT, par2 IN INT DEFAULT 1);
|
|
FUNCTION f1(par1 IN INT, par2 IN INT DEFAULT 1) RETURN INT;
|
|
END p1;
|
|
$$
|
|
CREATE PACKAGE BODY p1 AS
|
|
PROCEDURE p1(par1 IN INT, par2 IN INT DEFAULT 1)
|
|
AS
|
|
BEGIN
|
|
SELECT par1, par2 FROM DUAL;
|
|
END;
|
|
|
|
FUNCTION f1(par1 IN INT, par2 IN INT DEFAULT 1) RETURN INT
|
|
AS
|
|
BEGIN
|
|
RETURN par1 + par2;
|
|
END;
|
|
END p1;
|
|
$$
|
|
DELIMITER ;$$
|
|
|
|
CALL p1.p1(5);
|
|
CALL p1.p1(5, 10);
|
|
SELECT p1.f1(5) FROM DUAL;
|
|
SELECT p1.f1(5, 10) FROM DUAL;
|
|
|
|
DROP PACKAGE p1;
|
|
|
|
|
|
--echo #
|
|
--echo # MDEV-35925 OUT and INOUT parameters shouldn't expect a default value
|
|
--echo #
|
|
|
|
# An IN param with default followed by an OUT param
|
|
DELIMITER $$;
|
|
--error ER_NOT_SUPPORTED_YET
|
|
CREATE PROCEDURE sp1(p1 IN INT DEFAULT 0, p2 OUT INT)
|
|
BEGIN
|
|
SET p2 = p2 + 1;
|
|
END;
|
|
--error ER_NOT_SUPPORTED_YET
|
|
CREATE PROCEDURE sp1(p1 IN INT DEFAULT 0, p2 INOUT INT)
|
|
BEGIN
|
|
SET p2 = p2 + 1;
|
|
END;
|
|
$$
|
|
DELIMITER ;$$
|
|
|
|
--echo #
|
|
--echo # MDEV-37489: SIGSEGV in get_param_default_value | store_schema_params
|
|
--echo #
|
|
|
|
--DELIMITER //
|
|
CREATE OR REPLACE PROCEDURE p0 (x INT DEFAULT func())
|
|
AS
|
|
BEGIN
|
|
SELECT x;
|
|
END;
|
|
//
|
|
--DELIMITER ;
|
|
|
|
SET SESSION max_session_mem_used=8192;
|
|
--ERROR ER_OPTION_PREVENTS_STATEMENT
|
|
CALL p0();
|
|
|
|
SET @@max_session_mem_used=DEFAULT;
|
|
--ERROR ER_SP_DOES_NOT_EXIST
|
|
CALL p0();
|
|
|
|
SELECT * FROM information_schema.PARAMETERS where specific_name = 'p0';
|
|
|
|
--echo # with func() defined
|
|
--DELIMITER //
|
|
CREATE FUNCTION func(x INT DEFAULT 10) RETURN INT
|
|
AS
|
|
BEGIN
|
|
RETURN x;
|
|
END;
|
|
//
|
|
|
|
CREATE OR REPLACE PROCEDURE p0 (x INT DEFAULT func())
|
|
AS
|
|
BEGIN
|
|
SELECT x;
|
|
END;
|
|
//
|
|
--DELIMITER ;
|
|
|
|
SET SESSION max_session_mem_used=8192;
|
|
--ERROR ER_OPTION_PREVENTS_STATEMENT
|
|
CALL p0();
|
|
|
|
SET @@max_session_mem_used=DEFAULT;
|
|
CALL p0();
|
|
|
|
SELECT * FROM information_schema.PARAMETERS where specific_name = 'p0';
|
|
|
|
--echo # with multiple functions
|
|
--DELIMITER //
|
|
CREATE FUNCTION func2(x INT DEFAULT 10) RETURN INT
|
|
AS
|
|
BEGIN
|
|
RETURN x;
|
|
END;
|
|
//
|
|
|
|
CREATE OR REPLACE PROCEDURE p0 (x INT DEFAULT func(), y INT DEFAULT func2())
|
|
AS
|
|
BEGIN
|
|
SELECT x, y;
|
|
END;
|
|
//
|
|
--DELIMITER ;
|
|
|
|
SET SESSION max_session_mem_used=8192;
|
|
--ERROR ER_OPTION_PREVENTS_STATEMENT
|
|
CALL p0();
|
|
|
|
SET @@max_session_mem_used=DEFAULT;
|
|
CALL p0();
|
|
|
|
SELECT * FROM information_schema.PARAMETERS where specific_name = 'p0';
|
|
|
|
--echo # with function and constant default param
|
|
--DELIMITER //
|
|
CREATE OR REPLACE PROCEDURE p0 (x INT DEFAULT func(), y INT DEFAULT func2(), z INT DEFAULT 10)
|
|
AS
|
|
BEGIN
|
|
SELECT x, y, z;
|
|
END;
|
|
//
|
|
--DELIMITER ;
|
|
|
|
SET SESSION max_session_mem_used=8192;
|
|
--ERROR ER_OPTION_PREVENTS_STATEMENT
|
|
CALL p0();
|
|
|
|
SET @@max_session_mem_used=DEFAULT;
|
|
CALL p0();
|
|
|
|
SELECT * FROM information_schema.PARAMETERS where specific_name = 'p0';
|
|
|
|
DROP PROCEDURE p0;
|
|
DROP FUNCTION func;
|
|
DROP FUNCTION func2;
|
|
|
|
--echo # End of 11.8 tests
|
|
|
|
--echo #
|
|
--echo # MDEV-37054: Implement INFORMATION_SCHEMA.PARAMETERS.PARAMETER_DEFAULT column
|
|
--echo #
|
|
|
|
--echo # Default parameter values for procedure
|
|
CREATE TABLE t (a INT);
|
|
INSERT INTO t values (1);
|
|
|
|
DELIMITER $$;
|
|
CREATE OR REPLACE FUNCTION func(a INT DEFAULT 123) RETURN INT
|
|
AS
|
|
BEGIN
|
|
RETURN a;
|
|
END;
|
|
$$
|
|
|
|
CREATE PROCEDURE proc(p0 INT, # no default value for parameter
|
|
p1 INT DEFAULT 1, # constant default value
|
|
p2 INT DEFAULT p1+1, # expression referring other parameter
|
|
p3 INT DEFAULT pow(2,p2), # function as default value
|
|
p4 INT DEFAULT (SELECT a from t), # subquery as default value
|
|
p5 VARCHAR(5) DEFAULT 'maria', # varchar default
|
|
p6 VARCHAR(10) DEFAULT CONCAT(p5, NULL, 'db'), # sql_mode specific function
|
|
p7 INET6 DEFAULT INET6_NTOA(UNHEX('0A000101')), # misc. functions as default
|
|
p8 INT DEFAULT func(), # stored function as default
|
|
p9 TIMESTAMP DEFAULT NOW(), # timestmap function as default
|
|
p10 DATE DEFAULT CURDATE()) # date function as default
|
|
AS
|
|
BEGIN
|
|
SELECT p6 FROM DUAL;
|
|
END;
|
|
$$
|
|
DELIMITER ;$$
|
|
|
|
SHOW CREATE PROCEDURE proc;
|
|
|
|
--echo # Default param value with sql_mode=ORACLE
|
|
SELECT * FROM information_schema.PARAMETERS WHERE SPECIFIC_NAME = 'proc';
|
|
|
|
--echo # Default param value with sql_mode=DEFAULT
|
|
SET sql_mode=DEFAULT;
|
|
SELECT * FROM information_schema.PARAMETERS WHERE SPECIFIC_NAME = 'proc';
|
|
SET sql_mode=ORACLE;
|
|
|
|
DROP PROCEDURE proc;
|
|
DROP FUNCTION func;
|
|
DROP TABLE t;
|
|
|
|
--echo # Default parameter values of functions
|
|
CREATE TABLE t (a INT);
|
|
INSERT INTO t values (1);
|
|
|
|
DELIMITER $$;
|
|
CREATE OR REPLACE FUNCTION f(a INT DEFAULT 123) RETURN INT
|
|
AS
|
|
BEGIN
|
|
RETURN a;
|
|
END;
|
|
$$
|
|
|
|
CREATE FUNCTION func(p0 INT, # no default value for parameter
|
|
p1 INT DEFAULT 1, # constant default value
|
|
p2 INT DEFAULT p1+1, # expression referring other parameter
|
|
p3 INT DEFAULT pow(2,p2), # function as default value
|
|
p4 INT DEFAULT (SELECT a from t), # subquery as default value
|
|
p5 VARCHAR(5) DEFAULT 'maria', # varchar default
|
|
p6 VARCHAR(10) DEFAULT CONCAT(p5, NULL, 'db'), # sql_mode specific function
|
|
p7 INET6 DEFAULT INET6_NTOA(UNHEX('0A000101')), # misc. functions as default
|
|
p8 INT DEFAULT func(), # stored function as default
|
|
p9 TIMESTAMP DEFAULT NOW(), # timestmap function as default
|
|
p10 DATE DEFAULT CURDATE()) # date function as default
|
|
RETURN VARCHAR(10)
|
|
AS
|
|
BEGIN
|
|
RETURN p6;
|
|
END;
|
|
$$
|
|
DELIMITER ;$$
|
|
|
|
SHOW CREATE FUNCTION func;
|
|
|
|
--echo # Default param value with sql_mode=ORACLE
|
|
SELECT * FROM information_schema.PARAMETERS WHERE SPECIFIC_NAME = 'func';
|
|
|
|
--echo # Default param value with sql_mode=DEFAULT
|
|
SET sql_mode=DEFAULT;
|
|
SELECT * FROM information_schema.PARAMETERS WHERE SPECIFIC_NAME = 'func';
|
|
SET sql_mode=ORACLE;
|
|
|
|
DROP FUNCTION func;
|
|
DROP FUNCTION f;
|
|
DROP TABLE t;
|
|
|
|
--echo # End of 12.2 tests
|