mirror of
https://github.com/MariaDB/server.git
synced 2025-08-27 21:01:37 +02:00
5f880b3d4a
MariaDB 11.8 and above now supports `DEFAULT` values for parameters in stored procedures and functions. This commit introduces the `INFORMATION_SCHEMA.PARAMETERS.PARAMETER_DEFAULT` column to display the default value of parameters. The value is only shown if the current user or any of it's enabled roles is the owner of parameter's `SPECIFIC_SCHEMA`, otherwise `NULL`.
543 lines
23 KiB
Text
543 lines
23 KiB
Text
# Start of 11.8 tests
|
|
#
|
|
# MDEV-10862 Stored procedures: default values for parameters (optional parameters)
|
|
#
|
|
#
|
|
# Basic default parameter test for procedures
|
|
#
|
|
CREATE OR REPLACE PROCEDURE p1(par1 INT, par2 INT DEFAULT 1)
|
|
BEGIN
|
|
SELECT par1, par2 FROM DUAL;
|
|
END;
|
|
$$
|
|
CALL p1(2,3);
|
|
par1 par2
|
|
2 3
|
|
CALL p1(2);
|
|
par1 par2
|
|
2 1
|
|
CALL p1(2,3,4);
|
|
ERROR 42000: Incorrect number of arguments for PROCEDURE test.p1; expected 2, got 3
|
|
CALL p1();
|
|
ERROR 42000: Incorrect number of arguments for PROCEDURE test.p1; expected 2, got 0
|
|
DROP PROCEDURE p1;
|
|
#
|
|
# Basic default parameter test for functions
|
|
#
|
|
CREATE OR REPLACE FUNCTION f1(par1 INT, par2 INT DEFAULT 1) RETURNS INT
|
|
RETURN par1 + par2;
|
|
$$
|
|
SELECT f1(2,3) FROM DUAL;
|
|
f1(2,3)
|
|
5
|
|
SELECT f1(2) FROM DUAL;
|
|
f1(2)
|
|
3
|
|
SELECT f1(2,3,4) FROM DUAL;
|
|
ERROR 42000: Incorrect number of arguments for FUNCTION test.f1; expected 2, got 3
|
|
SELECT f1() FROM DUAL;
|
|
ERROR 42000: Incorrect number of arguments for FUNCTION test.f1; expected 2, got 0
|
|
DROP FUNCTION f1;
|
|
#
|
|
# Test various data types
|
|
#
|
|
CREATE TABLE t1 (a INT, b VARCHAR(10));
|
|
CREATE DATABASE d1;
|
|
USE d1;
|
|
CREATE TABLE t1 (a INT, b VARCHAR(10));
|
|
USE test;
|
|
CREATE OR REPLACE PROCEDURE p1(par1 INT DEFAULT 1,
|
|
par2 VARCHAR(10) DEFAULT 'abc',
|
|
par3 DATE DEFAULT '2010-01-01',
|
|
par4 DECIMAL(10,2) DEFAULT 1.23,
|
|
par5 DOUBLE DEFAULT 1.23,
|
|
par6 FLOAT DEFAULT 1.23,
|
|
par7 CHAR DEFAULT 'a',
|
|
par8 BINARY DEFAULT 'a',
|
|
par9 BLOB DEFAULT 'a',
|
|
par10 TEXT DEFAULT 'a',
|
|
par11 ENUM('a','b') DEFAULT 'a',
|
|
par12 SET('a','b') DEFAULT 'a',
|
|
par13 TIMESTAMP DEFAULT '2010-01-01 00:00:00',
|
|
par14 DATETIME DEFAULT '2010-01-01 00:00:00',
|
|
par15 TIME DEFAULT '00:00:00',
|
|
par16 YEAR DEFAULT 2010,
|
|
par17 BOOLEAN DEFAULT TRUE,
|
|
par18 TYPE OF t1.a DEFAULT 10,
|
|
par19 TYPE OF d1.t1.a DEFAULT 10,
|
|
par20 ROW TYPE OF t1 DEFAULT ROW(1, 'cde'),
|
|
par21 ROW TYPE OF d1.t1 DEFAULT ROW(1, 'cde'))
|
|
BEGIN
|
|
SELECT par1, par2, par3, par4, par5, par6, par7, par8, par9,
|
|
par10, par11, par12, par13, par14, par15, par16, par17, par18,
|
|
par19, par20.b, par21.b
|
|
FROM DUAL;
|
|
END;
|
|
$$
|
|
CALL p1();
|
|
par1 par2 par3 par4 par5 par6 par7 par8 par9 par10 par11 par12 par13 par14 par15 par16 par17 par18 par19 par20.b par21.b
|
|
1 abc 2010-01-01 1.23 1.23 1.23 a a a a a a 2010-01-01 00:00:00 2010-01-01 00:00:00 00:00:00 2010 1 10 10 cde cde
|
|
SELECT * FROM information_schema.PARAMETERS WHERE SPECIFIC_NAME = 'p1';
|
|
SPECIFIC_CATALOG SPECIFIC_SCHEMA SPECIFIC_NAME ORDINAL_POSITION PARAMETER_MODE PARAMETER_NAME DATA_TYPE CHARACTER_MAXIMUM_LENGTH CHARACTER_OCTET_LENGTH NUMERIC_PRECISION NUMERIC_SCALE DATETIME_PRECISION CHARACTER_SET_NAME COLLATION_NAME DTD_IDENTIFIER ROUTINE_TYPE PARAMETER_DEFAULT
|
|
def test p1 1 IN par1 int NULL NULL 10 0 NULL NULL NULL int(11) PROCEDURE 1
|
|
def test p1 2 IN par2 varchar 10 40 NULL NULL NULL utf8mb4 utf8mb4_uca1400_ai_ci varchar(10) PROCEDURE 'abc'
|
|
def test p1 3 IN par3 date NULL NULL NULL NULL NULL NULL NULL date PROCEDURE '2010-01-01'
|
|
def test p1 4 IN par4 decimal NULL NULL 10 2 NULL NULL NULL decimal(10,2) PROCEDURE 1.23
|
|
def test p1 5 IN par5 double NULL NULL 22 NULL NULL NULL NULL double PROCEDURE 1.23
|
|
def test p1 6 IN par6 float NULL NULL 12 NULL NULL NULL NULL float PROCEDURE 1.23
|
|
def test p1 7 IN par7 char 1 4 NULL NULL NULL utf8mb4 utf8mb4_uca1400_ai_ci char(1) PROCEDURE 'a'
|
|
def test p1 8 IN par8 binary 1 1 NULL NULL NULL NULL NULL binary(1) PROCEDURE 'a'
|
|
def test p1 9 IN par9 blob 65535 65535 NULL NULL NULL NULL NULL blob PROCEDURE 'a'
|
|
def test p1 10 IN par10 text 65535 65535 NULL NULL NULL utf8mb4 utf8mb4_uca1400_ai_ci text PROCEDURE 'a'
|
|
def test p1 11 IN par11 enum 1 4 NULL NULL NULL utf8mb4 utf8mb4_uca1400_ai_ci enum('a','b') PROCEDURE 'a'
|
|
def test p1 12 IN par12 set 3 12 NULL NULL NULL utf8mb4 utf8mb4_uca1400_ai_ci set('a','b') PROCEDURE 'a'
|
|
def test p1 13 IN par13 timestamp NULL NULL NULL NULL 0 NULL NULL timestamp PROCEDURE '2010-01-01 00:00:00'
|
|
def test p1 14 IN par14 datetime NULL NULL NULL NULL 0 NULL NULL datetime PROCEDURE '2010-01-01 00:00:00'
|
|
def test p1 15 IN par15 time NULL NULL NULL NULL 0 NULL NULL time PROCEDURE '00:00:00'
|
|
def test p1 16 IN par16 year NULL NULL NULL NULL NULL NULL NULL year(4) PROCEDURE 2010
|
|
def test p1 17 IN par17 tinyint NULL NULL 3 0 NULL NULL NULL tinyint(1) PROCEDURE 1
|
|
def test p1 18 IN par18 TYPE OF NULL NULL NULL NULL NULL NULL NULL TYPE OF `t1`.`a` PROCEDURE 10
|
|
def test p1 19 IN par19 TYPE OF NULL NULL NULL NULL NULL NULL NULL TYPE OF `d1`.`t1`.`a` PROCEDURE 10
|
|
def test p1 20 IN par20 ROW TYPE OF NULL NULL NULL NULL NULL NULL NULL ROW TYPE OF `t1` PROCEDURE (1,'cde')
|
|
def test p1 21 IN par21 ROW TYPE OF NULL NULL NULL NULL NULL NULL NULL ROW TYPE OF `d1`.`t1` PROCEDURE (1,'cde')
|
|
DROP PROCEDURE p1;
|
|
DROP TABLE t1;
|
|
DROP DATABASE d1;
|
|
#
|
|
# Default parameters in package's routines
|
|
#
|
|
CREATE OR REPLACE PACKAGE p1
|
|
PROCEDURE p1(par1 INT,
|
|
par2 INT DEFAULT 2);
|
|
FUNCTION f1(par1 INT,
|
|
par2 INT DEFAULT 2) RETURNS INT;
|
|
END;
|
|
$$
|
|
CREATE OR REPLACE PACKAGE BODY p1
|
|
PROCEDURE p1(par1 INT,
|
|
par2 INT DEFAULT 2)
|
|
BEGIN
|
|
SELECT par1, par2 FROM DUAL;
|
|
END;
|
|
FUNCTION f1(par1 INT,
|
|
par2 INT DEFAULT 2) RETURNS INT
|
|
BEGIN
|
|
RETURN par1 + par2;
|
|
END;
|
|
END;
|
|
$$
|
|
CALL p1.p1(1,4);
|
|
par1 par2
|
|
1 4
|
|
CALL p1.p1(1);
|
|
par1 par2
|
|
1 2
|
|
SELECT p1.f1(1,4) FROM DUAL;
|
|
p1.f1(1,4)
|
|
5
|
|
SELECT p1.f1(1) FROM DUAL;
|
|
p1.f1(1)
|
|
3
|
|
DROP PACKAGE p1;
|
|
#
|
|
# MDEV-35925 OUT and INOUT parameters shouldn't expect a default value
|
|
#
|
|
CREATE PROCEDURE sp1(IN p1 INT DEFAULT 0, OUT p2 INT)
|
|
BEGIN
|
|
SET p2 = p2 + 1;
|
|
END;
|
|
--error ER_NOT_SUPPORTED_YET
|
|
CREATE PROCEDURE sp1(IN p1 INT DEFAULT 0, IN OUT p2 INT)
|
|
BEGIN
|
|
SET p2 = p2 + 1;
|
|
END;
|
|
$$
|
|
ERROR 42000: This version of MariaDB doesn't yet support 'IN sparam1 <type> DEFAULT <expr>, OUT spparam2 <type>'
|
|
# End of 11.8 tests
|
|
#
|
|
# MDEV-37054: Implement INFORMATION_SCHEMA.PARAMETERS.PARAMETER_DEFAULT column
|
|
#
|
|
CREATE TABLE t (a INT);
|
|
INSERT INTO t values (1);
|
|
# Procedure with default parameters
|
|
CREATE OR REPLACE PROCEDURE p1(a INT,
|
|
b INTEGER DEFAULT 9999,
|
|
c INT DEFAULT NULL) DETERMINISTIC
|
|
BEGIN
|
|
SELECT b;
|
|
END;
|
|
//
|
|
CREATE OR REPLACE PROCEDURE p2(a INT DEFAULT 99,
|
|
b VARCHAR(5) DEFAULT 'maria',
|
|
c VARCHAR(10) DEFAULT CONCAT(b, 'db'))
|
|
BEGIN
|
|
SELECT b;
|
|
END;
|
|
//
|
|
CREATE OR REPLACE PROCEDURE p3(a INT DEFAULT 1,
|
|
b INT DEFAULT (a+1),
|
|
c INT DEFAULT POW(2,b),
|
|
d INET6 DEFAULT INET6_NTOA(UNHEX('0A000101')))
|
|
BEGIN
|
|
SELECT a, b, c FROM DUAL;
|
|
END;
|
|
//
|
|
CREATE OR REPLACE PROCEDURE p4(x INT DEFAULT (SELECT a from t))
|
|
BEGIN
|
|
SELECT x FROM DUAL;
|
|
END;
|
|
//
|
|
CREATE OR REPLACE FUNCTION func(a INT DEFAULT 123) RETURNS INT
|
|
BEGIN
|
|
RETURN a;
|
|
END;
|
|
//
|
|
CREATE OR REPLACE PROCEDURE p5(x INT DEFAULT func())
|
|
BEGIN
|
|
SELECT x FROM DUAL;
|
|
END;
|
|
//
|
|
CREATE OR REPLACE PROCEDURE p6(a TIMESTAMP DEFAULT NOW(), b DATE DEFAULT CURDATE())
|
|
BEGIN
|
|
SELECT a, b;
|
|
END;
|
|
//
|
|
CREATE OR REPLACE PROCEDURE p7(a VARCHAR(5) DEFAULT 'maria',
|
|
b VARCHAR(10) DEFAULT CONCAT(a, NULL, 'db'))
|
|
BEGIN
|
|
SELECT b from dual;
|
|
END;
|
|
//
|
|
# Constant default parameters
|
|
SHOW CREATE PROCEDURE p1;
|
|
Procedure p1
|
|
sql_mode STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
|
|
Create Procedure CREATE DEFINER=`root`@`localhost` PROCEDURE `p1`(a INT,
|
|
b INTEGER DEFAULT 9999,
|
|
c INT DEFAULT NULL)
|
|
DETERMINISTIC
|
|
BEGIN
|
|
SELECT b;
|
|
END
|
|
character_set_client latin1
|
|
collation_connection latin1_swedish_ci
|
|
Database Collation utf8mb4_uca1400_ai_ci
|
|
SELECT * FROM information_schema.PARAMETERS WHERE SPECIFIC_NAME = 'p1';
|
|
SPECIFIC_CATALOG SPECIFIC_SCHEMA SPECIFIC_NAME ORDINAL_POSITION PARAMETER_MODE PARAMETER_NAME DATA_TYPE CHARACTER_MAXIMUM_LENGTH CHARACTER_OCTET_LENGTH NUMERIC_PRECISION NUMERIC_SCALE DATETIME_PRECISION CHARACTER_SET_NAME COLLATION_NAME DTD_IDENTIFIER ROUTINE_TYPE PARAMETER_DEFAULT
|
|
def test p1 1 IN a int NULL NULL 10 0 NULL NULL NULL int(11) PROCEDURE NULL
|
|
def test p1 2 IN b int NULL NULL 10 0 NULL NULL NULL int(11) PROCEDURE 9999
|
|
def test p1 3 IN c int NULL NULL 10 0 NULL NULL NULL int(11) PROCEDURE NULL
|
|
SHOW CREATE PROCEDURE p2;
|
|
Procedure p2
|
|
sql_mode STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
|
|
Create Procedure CREATE DEFINER=`root`@`localhost` PROCEDURE `p2`(a INT DEFAULT 99,
|
|
b VARCHAR(5) DEFAULT 'maria',
|
|
c VARCHAR(10) DEFAULT CONCAT(b, 'db'))
|
|
BEGIN
|
|
SELECT b;
|
|
END
|
|
character_set_client latin1
|
|
collation_connection latin1_swedish_ci
|
|
Database Collation utf8mb4_uca1400_ai_ci
|
|
SELECT * FROM information_schema.PARAMETERS WHERE SPECIFIC_NAME = 'p2';
|
|
SPECIFIC_CATALOG SPECIFIC_SCHEMA SPECIFIC_NAME ORDINAL_POSITION PARAMETER_MODE PARAMETER_NAME DATA_TYPE CHARACTER_MAXIMUM_LENGTH CHARACTER_OCTET_LENGTH NUMERIC_PRECISION NUMERIC_SCALE DATETIME_PRECISION CHARACTER_SET_NAME COLLATION_NAME DTD_IDENTIFIER ROUTINE_TYPE PARAMETER_DEFAULT
|
|
def test p2 1 IN a int NULL NULL 10 0 NULL NULL NULL int(11) PROCEDURE 99
|
|
def test p2 2 IN b varchar 5 20 NULL NULL NULL utf8mb4 utf8mb4_uca1400_ai_ci varchar(5) PROCEDURE 'maria'
|
|
def test p2 3 IN c varchar 10 40 NULL NULL NULL utf8mb4 utf8mb4_uca1400_ai_ci varchar(10) PROCEDURE concat(`b`,'db')
|
|
# Default parameter as an epxression and function
|
|
CALL p3();
|
|
a b c
|
|
1 2 4
|
|
SHOW CREATE PROCEDURE p3;
|
|
Procedure p3
|
|
sql_mode STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
|
|
Create Procedure CREATE DEFINER=`root`@`localhost` PROCEDURE `p3`(a INT DEFAULT 1,
|
|
b INT DEFAULT (a+1),
|
|
c INT DEFAULT POW(2,b),
|
|
d INET6 DEFAULT INET6_NTOA(UNHEX('0A000101')))
|
|
BEGIN
|
|
SELECT a, b, c FROM DUAL;
|
|
END
|
|
character_set_client latin1
|
|
collation_connection latin1_swedish_ci
|
|
Database Collation utf8mb4_uca1400_ai_ci
|
|
SELECT * FROM information_schema.PARAMETERS WHERE SPECIFIC_NAME = 'p3';
|
|
SPECIFIC_CATALOG SPECIFIC_SCHEMA SPECIFIC_NAME ORDINAL_POSITION PARAMETER_MODE PARAMETER_NAME DATA_TYPE CHARACTER_MAXIMUM_LENGTH CHARACTER_OCTET_LENGTH NUMERIC_PRECISION NUMERIC_SCALE DATETIME_PRECISION CHARACTER_SET_NAME COLLATION_NAME DTD_IDENTIFIER ROUTINE_TYPE PARAMETER_DEFAULT
|
|
def test p3 1 IN a int NULL NULL 10 0 NULL NULL NULL int(11) PROCEDURE 1
|
|
def test p3 2 IN b int NULL NULL 10 0 NULL NULL NULL int(11) PROCEDURE (`a` + 1)
|
|
def test p3 3 IN c int NULL NULL 10 0 NULL NULL NULL int(11) PROCEDURE pow(2,`b`)
|
|
def test p3 4 IN d inet6 NULL NULL NULL NULL NULL NULL NULL inet6 PROCEDURE inet6_ntoa(unhex('0A000101'))
|
|
# Subquery as default value
|
|
SHOW CREATE PROCEDURE p4;
|
|
Procedure p4
|
|
sql_mode STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
|
|
Create Procedure CREATE DEFINER=`root`@`localhost` PROCEDURE `p4`(x INT DEFAULT (SELECT a from t))
|
|
BEGIN
|
|
SELECT x FROM DUAL;
|
|
END
|
|
character_set_client latin1
|
|
collation_connection latin1_swedish_ci
|
|
Database Collation utf8mb4_uca1400_ai_ci
|
|
SELECT * FROM information_schema.PARAMETERS WHERE SPECIFIC_NAME = 'p4';
|
|
SPECIFIC_CATALOG SPECIFIC_SCHEMA SPECIFIC_NAME ORDINAL_POSITION PARAMETER_MODE PARAMETER_NAME DATA_TYPE CHARACTER_MAXIMUM_LENGTH CHARACTER_OCTET_LENGTH NUMERIC_PRECISION NUMERIC_SCALE DATETIME_PRECISION CHARACTER_SET_NAME COLLATION_NAME DTD_IDENTIFIER ROUTINE_TYPE PARAMETER_DEFAULT
|
|
def test p4 1 IN x int NULL NULL 10 0 NULL NULL NULL int(11) PROCEDURE (select `a` from `t`)
|
|
# another stored function as a default parameter
|
|
SHOW CREATE PROCEDURE p5;
|
|
Procedure p5
|
|
sql_mode STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
|
|
Create Procedure CREATE DEFINER=`root`@`localhost` PROCEDURE `p5`(x INT DEFAULT func())
|
|
BEGIN
|
|
SELECT x FROM DUAL;
|
|
END
|
|
character_set_client latin1
|
|
collation_connection latin1_swedish_ci
|
|
Database Collation utf8mb4_uca1400_ai_ci
|
|
SELECT * FROM information_schema.PARAMETERS WHERE SPECIFIC_NAME = 'p5';
|
|
SPECIFIC_CATALOG SPECIFIC_SCHEMA SPECIFIC_NAME ORDINAL_POSITION PARAMETER_MODE PARAMETER_NAME DATA_TYPE CHARACTER_MAXIMUM_LENGTH CHARACTER_OCTET_LENGTH NUMERIC_PRECISION NUMERIC_SCALE DATETIME_PRECISION CHARACTER_SET_NAME COLLATION_NAME DTD_IDENTIFIER ROUTINE_TYPE PARAMETER_DEFAULT
|
|
def test p5 1 IN x int NULL NULL 10 0 NULL NULL NULL int(11) PROCEDURE `func`()
|
|
# date-time functions as default params
|
|
SHOW CREATE PROCEDURE p6;
|
|
Procedure p6
|
|
sql_mode STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
|
|
Create Procedure CREATE DEFINER=`root`@`localhost` PROCEDURE `p6`(a TIMESTAMP DEFAULT NOW(), b DATE DEFAULT CURDATE())
|
|
BEGIN
|
|
SELECT a, b;
|
|
END
|
|
character_set_client latin1
|
|
collation_connection latin1_swedish_ci
|
|
Database Collation utf8mb4_uca1400_ai_ci
|
|
SELECT * FROM information_schema.PARAMETERS WHERE SPECIFIC_NAME = 'p6';
|
|
SPECIFIC_CATALOG SPECIFIC_SCHEMA SPECIFIC_NAME ORDINAL_POSITION PARAMETER_MODE PARAMETER_NAME DATA_TYPE CHARACTER_MAXIMUM_LENGTH CHARACTER_OCTET_LENGTH NUMERIC_PRECISION NUMERIC_SCALE DATETIME_PRECISION CHARACTER_SET_NAME COLLATION_NAME DTD_IDENTIFIER ROUTINE_TYPE PARAMETER_DEFAULT
|
|
def test p6 1 IN a timestamp NULL NULL NULL NULL 0 NULL NULL timestamp PROCEDURE current_timestamp()
|
|
def test p6 2 IN b date NULL NULL NULL NULL NULL NULL NULL date PROCEDURE curdate()
|
|
# sql_mode specific functions as default params
|
|
SHOW CREATE PROCEDURE p7;
|
|
Procedure p7
|
|
sql_mode STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
|
|
Create Procedure CREATE DEFINER=`root`@`localhost` PROCEDURE `p7`(a VARCHAR(5) DEFAULT 'maria',
|
|
b VARCHAR(10) DEFAULT CONCAT(a, NULL, 'db'))
|
|
BEGIN
|
|
SELECT b from dual;
|
|
END
|
|
character_set_client latin1
|
|
collation_connection latin1_swedish_ci
|
|
Database Collation utf8mb4_uca1400_ai_ci
|
|
# sql_mode=DEFAULT
|
|
SELECT * FROM information_schema.PARAMETERS WHERE SPECIFIC_NAME = 'p7';
|
|
SPECIFIC_CATALOG SPECIFIC_SCHEMA SPECIFIC_NAME ORDINAL_POSITION PARAMETER_MODE PARAMETER_NAME DATA_TYPE CHARACTER_MAXIMUM_LENGTH CHARACTER_OCTET_LENGTH NUMERIC_PRECISION NUMERIC_SCALE DATETIME_PRECISION CHARACTER_SET_NAME COLLATION_NAME DTD_IDENTIFIER ROUTINE_TYPE PARAMETER_DEFAULT
|
|
def test p7 1 IN a varchar 5 20 NULL NULL NULL utf8mb4 utf8mb4_uca1400_ai_ci varchar(5) PROCEDURE 'maria'
|
|
def test p7 2 IN b varchar 10 40 NULL NULL NULL utf8mb4 utf8mb4_uca1400_ai_ci varchar(10) PROCEDURE concat(`a`,NULL,'db')
|
|
# sql_mode=ORACLE
|
|
SET sql_mode=ORACLE;
|
|
SELECT * FROM information_schema.PARAMETERS WHERE SPECIFIC_NAME = 'p7';
|
|
SPECIFIC_CATALOG SPECIFIC_SCHEMA SPECIFIC_NAME ORDINAL_POSITION PARAMETER_MODE PARAMETER_NAME DATA_TYPE CHARACTER_MAXIMUM_LENGTH CHARACTER_OCTET_LENGTH NUMERIC_PRECISION NUMERIC_SCALE DATETIME_PRECISION CHARACTER_SET_NAME COLLATION_NAME DTD_IDENTIFIER ROUTINE_TYPE PARAMETER_DEFAULT
|
|
def test p7 1 IN a varchar 5 20 NULL NULL NULL utf8mb4 utf8mb4_uca1400_ai_ci varchar(5) PROCEDURE 'maria'
|
|
def test p7 2 IN b varchar 10 40 NULL NULL NULL utf8mb4 utf8mb4_uca1400_ai_ci varchar(10) PROCEDURE concat(`a`,NULL,'db')
|
|
SET sql_mode=DEFAULT;
|
|
DROP PROCEDURE p1;
|
|
DROP PROCEDURE p2;
|
|
DROP PROCEDURE p3;
|
|
DROP PROCEDURE p4;
|
|
DROP PROCEDURE p5;
|
|
DROP PROCEDURE p6;
|
|
DROP PROCEDURE p7;
|
|
DROP FUNCTION func;
|
|
# Functions with defualt parameters
|
|
CREATE OR REPLACE FUNCTION f1(a INT, b INT DEFAULT 9999, c INT DEFAULT NULL) RETURNS INT
|
|
BEGIN
|
|
RETURN b;
|
|
END;
|
|
//
|
|
CREATE OR REPLACE FUNCTION f2(a INT DEFAULT 99, b INT DEFAULT (a+1)) RETURNS INT
|
|
BEGIN
|
|
RETURN b;
|
|
END;
|
|
//
|
|
CREATE OR REPLACE FUNCTION f3(a INT DEFAULT 8,
|
|
b INT DEFAULT LOG2(a),
|
|
c VARCHAR(5) DEFAULT (IF(LOG2(a)=3,'yes','no'))) RETURNS INT
|
|
BEGIN
|
|
RETURN b;
|
|
END;
|
|
//
|
|
CREATE OR REPLACE FUNCTION f4(x INT DEFAULT (SELECT a FROM t)) RETURNS INT
|
|
BEGIN
|
|
RETURN x;
|
|
END;
|
|
//
|
|
CREATE OR REPLACE FUNCTION f5(a TIMESTAMP DEFAULT NOW(), b DATE DEFAULT CURDATE()) RETURNS DATE
|
|
BEGIN
|
|
RETURN b;
|
|
END;
|
|
//
|
|
CREATE OR REPLACE FUNCTION f6(a VARCHAR(5) DEFAULT 'maria',
|
|
b VARCHAR(10) DEFAULT CONCAT(a, NULL, 'db')) RETURNS VARCHAR(10)
|
|
BEGIN
|
|
RETURN b;
|
|
END;
|
|
//
|
|
# Constant Default parameter
|
|
SHOW CREATE FUNCTION f1;
|
|
Function f1
|
|
sql_mode STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
|
|
Create Function CREATE DEFINER=`root`@`localhost` FUNCTION `f1`(a INT, b INT DEFAULT 9999, c INT DEFAULT NULL) RETURNS int(11)
|
|
BEGIN
|
|
RETURN b;
|
|
END
|
|
character_set_client latin1
|
|
collation_connection latin1_swedish_ci
|
|
Database Collation utf8mb4_uca1400_ai_ci
|
|
SELECT * FROM information_schema.PARAMETERS WHERE SPECIFIC_NAME = 'f1';
|
|
SPECIFIC_CATALOG SPECIFIC_SCHEMA SPECIFIC_NAME ORDINAL_POSITION PARAMETER_MODE PARAMETER_NAME DATA_TYPE CHARACTER_MAXIMUM_LENGTH CHARACTER_OCTET_LENGTH NUMERIC_PRECISION NUMERIC_SCALE DATETIME_PRECISION CHARACTER_SET_NAME COLLATION_NAME DTD_IDENTIFIER ROUTINE_TYPE PARAMETER_DEFAULT
|
|
def test f1 0 NULL NULL int NULL NULL 10 0 NULL NULL NULL int(11) FUNCTION NULL
|
|
def test f1 1 IN a int NULL NULL 10 0 NULL NULL NULL int(11) FUNCTION NULL
|
|
def test f1 2 IN b int NULL NULL 10 0 NULL NULL NULL int(11) FUNCTION 9999
|
|
def test f1 3 IN c int NULL NULL 10 0 NULL NULL NULL int(11) FUNCTION NULL
|
|
# Default parameter as an expression
|
|
SHOW CREATE FUNCTION f2;
|
|
Function f2
|
|
sql_mode STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
|
|
Create Function CREATE DEFINER=`root`@`localhost` FUNCTION `f2`(a INT DEFAULT 99, b INT DEFAULT (a+1)) RETURNS int(11)
|
|
BEGIN
|
|
RETURN b;
|
|
END
|
|
character_set_client latin1
|
|
collation_connection latin1_swedish_ci
|
|
Database Collation utf8mb4_uca1400_ai_ci
|
|
SELECT * FROM information_schema.PARAMETERS WHERE SPECIFIC_NAME = 'f2';
|
|
SPECIFIC_CATALOG SPECIFIC_SCHEMA SPECIFIC_NAME ORDINAL_POSITION PARAMETER_MODE PARAMETER_NAME DATA_TYPE CHARACTER_MAXIMUM_LENGTH CHARACTER_OCTET_LENGTH NUMERIC_PRECISION NUMERIC_SCALE DATETIME_PRECISION CHARACTER_SET_NAME COLLATION_NAME DTD_IDENTIFIER ROUTINE_TYPE PARAMETER_DEFAULT
|
|
def test f2 0 NULL NULL int NULL NULL 10 0 NULL NULL NULL int(11) FUNCTION NULL
|
|
def test f2 1 IN a int NULL NULL 10 0 NULL NULL NULL int(11) FUNCTION 99
|
|
def test f2 2 IN b int NULL NULL 10 0 NULL NULL NULL int(11) FUNCTION (`a` + 1)
|
|
# Default parameter as a function
|
|
SHOW CREATE FUNCTION f3;
|
|
Function f3
|
|
sql_mode STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
|
|
Create Function CREATE DEFINER=`root`@`localhost` FUNCTION `f3`(a INT DEFAULT 8,
|
|
b INT DEFAULT LOG2(a),
|
|
c VARCHAR(5) DEFAULT (IF(LOG2(a)=3,'yes','no'))) RETURNS int(11)
|
|
BEGIN
|
|
RETURN b;
|
|
END
|
|
character_set_client latin1
|
|
collation_connection latin1_swedish_ci
|
|
Database Collation utf8mb4_uca1400_ai_ci
|
|
SELECT f3();
|
|
f3()
|
|
3
|
|
SELECT * FROM information_schema.PARAMETERS WHERE SPECIFIC_NAME = 'f3';
|
|
SPECIFIC_CATALOG SPECIFIC_SCHEMA SPECIFIC_NAME ORDINAL_POSITION PARAMETER_MODE PARAMETER_NAME DATA_TYPE CHARACTER_MAXIMUM_LENGTH CHARACTER_OCTET_LENGTH NUMERIC_PRECISION NUMERIC_SCALE DATETIME_PRECISION CHARACTER_SET_NAME COLLATION_NAME DTD_IDENTIFIER ROUTINE_TYPE PARAMETER_DEFAULT
|
|
def test f3 0 NULL NULL int NULL NULL 10 0 NULL NULL NULL int(11) FUNCTION NULL
|
|
def test f3 1 IN a int NULL NULL 10 0 NULL NULL NULL int(11) FUNCTION 8
|
|
def test f3 2 IN b int NULL NULL 10 0 NULL NULL NULL int(11) FUNCTION log2(`a`)
|
|
def test f3 3 IN c varchar 5 20 NULL NULL NULL utf8mb4 utf8mb4_uca1400_ai_ci varchar(5) FUNCTION if(log2(`a`) = 3,'yes','no')
|
|
# Subquery as function's default parameter
|
|
SHOW CREATE FUNCTION f4;
|
|
Function f4
|
|
sql_mode STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
|
|
Create Function CREATE DEFINER=`root`@`localhost` FUNCTION `f4`(x INT DEFAULT (SELECT a FROM t)) RETURNS int(11)
|
|
BEGIN
|
|
RETURN x;
|
|
END
|
|
character_set_client latin1
|
|
collation_connection latin1_swedish_ci
|
|
Database Collation utf8mb4_uca1400_ai_ci
|
|
SELECT * FROM information_schema.PARAMETERS WHERE SPECIFIC_NAME = 'f4';
|
|
SPECIFIC_CATALOG SPECIFIC_SCHEMA SPECIFIC_NAME ORDINAL_POSITION PARAMETER_MODE PARAMETER_NAME DATA_TYPE CHARACTER_MAXIMUM_LENGTH CHARACTER_OCTET_LENGTH NUMERIC_PRECISION NUMERIC_SCALE DATETIME_PRECISION CHARACTER_SET_NAME COLLATION_NAME DTD_IDENTIFIER ROUTINE_TYPE PARAMETER_DEFAULT
|
|
def test f4 0 NULL NULL int NULL NULL 10 0 NULL NULL NULL int(11) FUNCTION NULL
|
|
def test f4 1 IN x int NULL NULL 10 0 NULL NULL NULL int(11) FUNCTION (select `a` from `t`)
|
|
# date-time functions as default params
|
|
SHOW CREATE FUNCTION f5;
|
|
Function f5
|
|
sql_mode STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
|
|
Create Function CREATE DEFINER=`root`@`localhost` FUNCTION `f5`(a TIMESTAMP DEFAULT NOW(), b DATE DEFAULT CURDATE()) RETURNS date
|
|
BEGIN
|
|
RETURN b;
|
|
END
|
|
character_set_client latin1
|
|
collation_connection latin1_swedish_ci
|
|
Database Collation utf8mb4_uca1400_ai_ci
|
|
SELECT * FROM information_schema.PARAMETERS WHERE SPECIFIC_NAME = 'f5';
|
|
SPECIFIC_CATALOG SPECIFIC_SCHEMA SPECIFIC_NAME ORDINAL_POSITION PARAMETER_MODE PARAMETER_NAME DATA_TYPE CHARACTER_MAXIMUM_LENGTH CHARACTER_OCTET_LENGTH NUMERIC_PRECISION NUMERIC_SCALE DATETIME_PRECISION CHARACTER_SET_NAME COLLATION_NAME DTD_IDENTIFIER ROUTINE_TYPE PARAMETER_DEFAULT
|
|
def test f5 0 NULL NULL date NULL NULL NULL NULL NULL NULL NULL date FUNCTION NULL
|
|
def test f5 1 IN a timestamp NULL NULL NULL NULL 0 NULL NULL timestamp FUNCTION current_timestamp()
|
|
def test f5 2 IN b date NULL NULL NULL NULL NULL NULL NULL date FUNCTION curdate()
|
|
# sql_mode specific functions as default params
|
|
SHOW CREATE FUNCTION f6;
|
|
Function f6
|
|
sql_mode STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
|
|
Create Function CREATE DEFINER=`root`@`localhost` FUNCTION `f6`(a VARCHAR(5) DEFAULT 'maria',
|
|
b VARCHAR(10) DEFAULT CONCAT(a, NULL, 'db')) RETURNS varchar(10) CHARSET utf8mb4 COLLATE utf8mb4_uca1400_ai_ci
|
|
BEGIN
|
|
RETURN b;
|
|
END
|
|
character_set_client latin1
|
|
collation_connection latin1_swedish_ci
|
|
Database Collation utf8mb4_uca1400_ai_ci
|
|
# sql_mode=DEFAULT
|
|
SELECT * FROM information_schema.PARAMETERS WHERE SPECIFIC_NAME = 'f6';
|
|
SPECIFIC_CATALOG SPECIFIC_SCHEMA SPECIFIC_NAME ORDINAL_POSITION PARAMETER_MODE PARAMETER_NAME DATA_TYPE CHARACTER_MAXIMUM_LENGTH CHARACTER_OCTET_LENGTH NUMERIC_PRECISION NUMERIC_SCALE DATETIME_PRECISION CHARACTER_SET_NAME COLLATION_NAME DTD_IDENTIFIER ROUTINE_TYPE PARAMETER_DEFAULT
|
|
def test f6 0 NULL NULL varchar 10 40 NULL NULL NULL utf8mb4 utf8mb4_uca1400_ai_ci varchar(10) FUNCTION NULL
|
|
def test f6 1 IN a varchar 5 20 NULL NULL NULL utf8mb4 utf8mb4_uca1400_ai_ci varchar(5) FUNCTION 'maria'
|
|
def test f6 2 IN b varchar 10 40 NULL NULL NULL utf8mb4 utf8mb4_uca1400_ai_ci varchar(10) FUNCTION concat(`a`,NULL,'db')
|
|
# sql_mode=ORACLE
|
|
SET sql_mode=ORACLE;
|
|
SELECT * FROM information_schema.PARAMETERS WHERE SPECIFIC_NAME = 'f6';
|
|
SPECIFIC_CATALOG SPECIFIC_SCHEMA SPECIFIC_NAME ORDINAL_POSITION PARAMETER_MODE PARAMETER_NAME DATA_TYPE CHARACTER_MAXIMUM_LENGTH CHARACTER_OCTET_LENGTH NUMERIC_PRECISION NUMERIC_SCALE DATETIME_PRECISION CHARACTER_SET_NAME COLLATION_NAME DTD_IDENTIFIER ROUTINE_TYPE PARAMETER_DEFAULT
|
|
def test f6 0 NULL NULL varchar 10 40 NULL NULL NULL utf8mb4 utf8mb4_uca1400_ai_ci varchar(10) FUNCTION NULL
|
|
def test f6 1 IN a varchar 5 20 NULL NULL NULL utf8mb4 utf8mb4_uca1400_ai_ci varchar(5) FUNCTION 'maria'
|
|
def test f6 2 IN b varchar 10 40 NULL NULL NULL utf8mb4 utf8mb4_uca1400_ai_ci varchar(10) FUNCTION concat(`a`,NULL,'db')
|
|
SET sql_mode=DEFAULT;
|
|
DROP FUNCTION f1;
|
|
DROP FUNCTION f2;
|
|
DROP FUNCTION f3;
|
|
DROP FUNCTION f4;
|
|
DROP FUNCTION f5;
|
|
DROP FUNCTION f6;
|
|
DROP TABLE t;
|
|
# Test to verify consistent printing of default values
|
|
# This test checks that a column's default value and a parameter's default
|
|
# value are printed in the same way.
|
|
CREATE TABLE t (a1 INT,
|
|
a2 INT DEFAULT NULL,
|
|
a3 INT NOT NULL,
|
|
a4 VARCHAR(10) NOT NULL,
|
|
b INT DEFAULT 1,
|
|
c INT DEFAULT (b+1),
|
|
d INT DEFAULT POW(2,b),
|
|
e VARCHAR(10) DEFAULT 'maria',
|
|
f VARCHAR(10) DEFAULT CONCAT(e,'db'));
|
|
CREATE PROCEDURE proc(a1 INT,
|
|
a2 INT DEFAULT NULL,
|
|
b INT DEFAULT 1,
|
|
c INT DEFAULT (b+1),
|
|
d INT DEFAULT POW(2,b),
|
|
e VARCHAR(10) DEFAULT 'maria',
|
|
f VARCHAR(10) DEFAULT CONCAT(e,'db'))
|
|
BEGIN
|
|
SELECT d FROM DUAL;
|
|
END;
|
|
//
|
|
SELECT COLUMN_NAME, COLUMN_DEFAULT, (COLUMN_DEFAULT IS NULL) AS `IS_NULL` FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 't';
|
|
COLUMN_NAME COLUMN_DEFAULT IS_NULL
|
|
a1 NULL 0
|
|
a2 NULL 0
|
|
a3 NULL 1
|
|
a4 NULL 1
|
|
b 1 0
|
|
c (`b` + 1) 0
|
|
d pow(2,`b`) 0
|
|
e 'maria' 0
|
|
f concat(`e`,'db') 0
|
|
SELECT PARAMETER_NAME, PARAMETER_DEFAULT, (PARAMETER_DEFAULT IS NULL) AS `IS_NULL` FROM INFORMATION_SCHEMA.PARAMETERS WHERE SPECIFIC_NAME = 'proc';
|
|
PARAMETER_NAME PARAMETER_DEFAULT IS_NULL
|
|
a1 NULL 1
|
|
a2 NULL 0
|
|
b 1 0
|
|
c (`b` + 1) 0
|
|
d pow(2,`b`) 0
|
|
e 'maria' 0
|
|
f concat(`e`,'db') 0
|
|
DROP TABLE t;
|
|
DROP PROCEDURE proc;
|
|
# End of 12.2 tests
|