path: root/mysql-test/main/information_schema_parameters.result

# ========== parameters.1 ==========
USE INFORMATION_SCHEMA;
SHOW CREATE TABLE INFORMATION_SCHEMA.PARAMETERS;
Table	Create Table
PARAMETERS	CREATE TEMPORARY TABLE `PARAMETERS` (
  `SPECIFIC_CATALOG` varchar(512) NOT NULL,
  `SPECIFIC_SCHEMA` varchar(64) NOT NULL,
  `SPECIFIC_NAME` varchar(64) NOT NULL,
  `ORDINAL_POSITION` int(21) NOT NULL,
  `PARAMETER_MODE` varchar(5),
  `PARAMETER_NAME` varchar(64),
  `DATA_TYPE` varchar(64) NOT NULL,
  `CHARACTER_MAXIMUM_LENGTH` int(21),
  `CHARACTER_OCTET_LENGTH` int(21),
  `NUMERIC_PRECISION` int(21),
  `NUMERIC_SCALE` int(21),
  `DATETIME_PRECISION` bigint(21) unsigned,
  `CHARACTER_SET_NAME` varchar(64),
  `COLLATION_NAME` varchar(64),
  `DTD_IDENTIFIER` longtext NOT NULL,
  `ROUTINE_TYPE` varchar(9) NOT NULL
)  DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci
SELECT * FROM information_schema.columns
WHERE table_schema = 'information_schema'
  AND table_name   = 'parameters'
ORDER BY ordinal_position;
TABLE_CATALOG	def
TABLE_SCHEMA	information_schema
TABLE_NAME	PARAMETERS
COLUMN_NAME	SPECIFIC_CATALOG
ORDINAL_POSITION	1
COLUMN_DEFAULT	NULL
IS_NULLABLE	NO
DATA_TYPE	varchar
CHARACTER_MAXIMUM_LENGTH	512
CHARACTER_OCTET_LENGTH	1536
NUMERIC_PRECISION	NULL
NUMERIC_SCALE	NULL
DATETIME_PRECISION	NULL
CHARACTER_SET_NAME	utf8mb3
COLLATION_NAME	utf8mb3_general_ci
COLUMN_TYPE	varchar(512)
COLUMN_KEY	
EXTRA	
PRIVILEGES	#
COLUMN_COMMENT	
IS_GENERATED	NEVER
GENERATION_EXPRESSION	NULL
TABLE_CATALOG	def
TABLE_SCHEMA	information_schema
TABLE_NAME	PARAMETERS
COLUMN_NAME	SPECIFIC_SCHEMA
ORDINAL_POSITION	2
COLUMN_DEFAULT	NULL
IS_NULLABLE	NO
DATA_TYPE	varchar
CHARACTER_MAXIMUM_LENGTH	64
CHARACTER_OCTET_LENGTH	192
NUMERIC_PRECISION	NULL
NUMERIC_SCALE	NULL
DATETIME_PRECISION	NULL
CHARACTER_SET_NAME	utf8mb3
COLLATION_NAME	utf8mb3_general_ci
COLUMN_TYPE	varchar(64)
COLUMN_KEY	
EXTRA	
PRIVILEGES	#
COLUMN_COMMENT	
IS_GENERATED	NEVER
GENERATION_EXPRESSION	NULL
TABLE_CATALOG	def
TABLE_SCHEMA	information_schema
TABLE_NAME	PARAMETERS
COLUMN_NAME	SPECIFIC_NAME
ORDINAL_POSITION	3
COLUMN_DEFAULT	NULL
IS_NULLABLE	NO
DATA_TYPE	varchar
CHARACTER_MAXIMUM_LENGTH	64
CHARACTER_OCTET_LENGTH	192
NUMERIC_PRECISION	NULL
NUMERIC_SCALE	NULL
DATETIME_PRECISION	NULL
CHARACTER_SET_NAME	utf8mb3
COLLATION_NAME	utf8mb3_general_ci
COLUMN_TYPE	varchar(64)
COLUMN_KEY	
EXTRA	
PRIVILEGES	#
COLUMN_COMMENT	
IS_GENERATED	NEVER
GENERATION_EXPRESSION	NULL
TABLE_CATALOG	def
TABLE_SCHEMA	information_schema
TABLE_NAME	PARAMETERS
COLUMN_NAME	ORDINAL_POSITION
ORDINAL_POSITION	4
COLUMN_DEFAULT	NULL
IS_NULLABLE	NO
DATA_TYPE	int
CHARACTER_MAXIMUM_LENGTH	NULL
CHARACTER_OCTET_LENGTH	NULL
NUMERIC_PRECISION	10
NUMERIC_SCALE	0
DATETIME_PRECISION	NULL
CHARACTER_SET_NAME	NULL
COLLATION_NAME	NULL
COLUMN_TYPE	int(21)
COLUMN_KEY	
EXTRA	
PRIVILEGES	#
COLUMN_COMMENT	
IS_GENERATED	NEVER
GENERATION_EXPRESSION	NULL
TABLE_CATALOG	def
TABLE_SCHEMA	information_schema
TABLE_NAME	PARAMETERS
COLUMN_NAME	PARAMETER_MODE
ORDINAL_POSITION	5
COLUMN_DEFAULT	NULL
IS_NULLABLE	YES
DATA_TYPE	varchar
CHARACTER_MAXIMUM_LENGTH	5
CHARACTER_OCTET_LENGTH	15
NUMERIC_PRECISION	NULL
NUMERIC_SCALE	NULL
DATETIME_PRECISION	NULL
CHARACTER_SET_NAME	utf8mb3
COLLATION_NAME	utf8mb3_general_ci
COLUMN_TYPE	varchar(5)
COLUMN_KEY	
EXTRA	
PRIVILEGES	#
COLUMN_COMMENT	
IS_GENERATED	NEVER
GENERATION_EXPRESSION	NULL
TABLE_CATALOG	def
TABLE_SCHEMA	information_schema
TABLE_NAME	PARAMETERS
COLUMN_NAME	PARAMETER_NAME
ORDINAL_POSITION	6
COLUMN_DEFAULT	NULL
IS_NULLABLE	YES
DATA_TYPE	varchar
CHARACTER_MAXIMUM_LENGTH	64
CHARACTER_OCTET_LENGTH	192
NUMERIC_PRECISION	NULL
NUMERIC_SCALE	NULL
DATETIME_PRECISION	NULL
CHARACTER_SET_NAME	utf8mb3
COLLATION_NAME	utf8mb3_general_ci
COLUMN_TYPE	varchar(64)
COLUMN_KEY	
EXTRA	
PRIVILEGES	#
COLUMN_COMMENT	
IS_GENERATED	NEVER
GENERATION_EXPRESSION	NULL
TABLE_CATALOG	def
TABLE_SCHEMA	information_schema
TABLE_NAME	PARAMETERS
COLUMN_NAME	DATA_TYPE
ORDINAL_POSITION	7
COLUMN_DEFAULT	NULL
IS_NULLABLE	NO
DATA_TYPE	varchar
CHARACTER_MAXIMUM_LENGTH	64
CHARACTER_OCTET_LENGTH	192
NUMERIC_PRECISION	NULL
NUMERIC_SCALE	NULL
DATETIME_PRECISION	NULL
CHARACTER_SET_NAME	utf8mb3
COLLATION_NAME	utf8mb3_general_ci
COLUMN_TYPE	varchar(64)
COLUMN_KEY	
EXTRA	
PRIVILEGES	#
COLUMN_COMMENT	
IS_GENERATED	NEVER
GENERATION_EXPRESSION	NULL
TABLE_CATALOG	def
TABLE_SCHEMA	information_schema
TABLE_NAME	PARAMETERS
COLUMN_NAME	CHARACTER_MAXIMUM_LENGTH
ORDINAL_POSITION	8
COLUMN_DEFAULT	NULL
IS_NULLABLE	YES
DATA_TYPE	int
CHARACTER_MAXIMUM_LENGTH	NULL
CHARACTER_OCTET_LENGTH	NULL
NUMERIC_PRECISION	10
NUMERIC_SCALE	0
DATETIME_PRECISION	NULL
CHARACTER_SET_NAME	NULL
COLLATION_NAME	NULL
COLUMN_TYPE	int(21)
COLUMN_KEY	
EXTRA	
PRIVILEGES	#
COLUMN_COMMENT	
IS_GENERATED	NEVER
GENERATION_EXPRESSION	NULL
TABLE_CATALOG	def
TABLE_SCHEMA	information_schema
TABLE_NAME	PARAMETERS
COLUMN_NAME	CHARACTER_OCTET_LENGTH
ORDINAL_POSITION	9
COLUMN_DEFAULT	NULL
IS_NULLABLE	YES
DATA_TYPE	int
CHARACTER_MAXIMUM_LENGTH	NULL
CHARACTER_OCTET_LENGTH	NULL
NUMERIC_PRECISION	10
NUMERIC_SCALE	0
DATETIME_PRECISION	NULL
CHARACTER_SET_NAME	NULL
COLLATION_NAME	NULL
COLUMN_TYPE	int(21)
COLUMN_KEY	
EXTRA	
PRIVILEGES	#
COLUMN_COMMENT	
IS_GENERATED	NEVER
GENERATION_EXPRESSION	NULL
TABLE_CATALOG	def
TABLE_SCHEMA	information_schema
TABLE_NAME	PARAMETERS
COLUMN_NAME	NUMERIC_PRECISION
ORDINAL_POSITION	10
COLUMN_DEFAULT	NULL
IS_NULLABLE	YES
DATA_TYPE	int
CHARACTER_MAXIMUM_LENGTH	NULL
CHARACTER_OCTET_LENGTH	NULL
NUMERIC_PRECISION	10
NUMERIC_SCALE	0
DATETIME_PRECISION	NULL
CHARACTER_SET_NAME	NULL
COLLATION_NAME	NULL
COLUMN_TYPE	int(21)
COLUMN_KEY	
EXTRA	
PRIVILEGES	#
COLUMN_COMMENT	
IS_GENERATED	NEVER
GENERATION_EXPRESSION	NULL
TABLE_CATALOG	def
TABLE_SCHEMA	information_schema
TABLE_NAME	PARAMETERS
COLUMN_NAME	NUMERIC_SCALE
ORDINAL_POSITION	11
COLUMN_DEFAULT	NULL
IS_NULLABLE	YES
DATA_TYPE	int
CHARACTER_MAXIMUM_LENGTH	NULL
CHARACTER_OCTET_LENGTH	NULL
NUMERIC_PRECISION	10
NUMERIC_SCALE	0
DATETIME_PRECISION	NULL
CHARACTER_SET_NAME	NULL
COLLATION_NAME	NULL
COLUMN_TYPE	int(21)
COLUMN_KEY	
EXTRA	
PRIVILEGES	#
COLUMN_COMMENT	
IS_GENERATED	NEVER
GENERATION_EXPRESSION	NULL
TABLE_CATALOG	def
TABLE_SCHEMA	information_schema
TABLE_NAME	PARAMETERS
COLUMN_NAME	DATETIME_PRECISION
ORDINAL_POSITION	12
COLUMN_DEFAULT	NULL
IS_NULLABLE	YES
DATA_TYPE	bigint
CHARACTER_MAXIMUM_LENGTH	NULL
CHARACTER_OCTET_LENGTH	NULL
NUMERIC_PRECISION	20
NUMERIC_SCALE	0
DATETIME_PRECISION	NULL
CHARACTER_SET_NAME	NULL
COLLATION_NAME	NULL
COLUMN_TYPE	bigint(21) unsigned
COLUMN_KEY	
EXTRA	
PRIVILEGES	#
COLUMN_COMMENT	
IS_GENERATED	NEVER
GENERATION_EXPRESSION	NULL
TABLE_CATALOG	def
TABLE_SCHEMA	information_schema
TABLE_NAME	PARAMETERS
COLUMN_NAME	CHARACTER_SET_NAME
ORDINAL_POSITION	13
COLUMN_DEFAULT	NULL
IS_NULLABLE	YES
DATA_TYPE	varchar
CHARACTER_MAXIMUM_LENGTH	64
CHARACTER_OCTET_LENGTH	192
NUMERIC_PRECISION	NULL
NUMERIC_SCALE	NULL
DATETIME_PRECISION	NULL
CHARACTER_SET_NAME	utf8mb3
COLLATION_NAME	utf8mb3_general_ci
COLUMN_TYPE	varchar(64)
COLUMN_KEY	
EXTRA	
PRIVILEGES	#
COLUMN_COMMENT	
IS_GENERATED	NEVER
GENERATION_EXPRESSION	NULL
TABLE_CATALOG	def
TABLE_SCHEMA	information_schema
TABLE_NAME	PARAMETERS
COLUMN_NAME	COLLATION_NAME
ORDINAL_POSITION	14
COLUMN_DEFAULT	NULL
IS_NULLABLE	YES
DATA_TYPE	varchar
CHARACTER_MAXIMUM_LENGTH	64
CHARACTER_OCTET_LENGTH	192
NUMERIC_PRECISION	NULL
NUMERIC_SCALE	NULL
DATETIME_PRECISION	NULL
CHARACTER_SET_NAME	utf8mb3
COLLATION_NAME	utf8mb3_general_ci
COLUMN_TYPE	varchar(64)
COLUMN_KEY	
EXTRA	
PRIVILEGES	#
COLUMN_COMMENT	
IS_GENERATED	NEVER
GENERATION_EXPRESSION	NULL
TABLE_CATALOG	def
TABLE_SCHEMA	information_schema
TABLE_NAME	PARAMETERS
COLUMN_NAME	DTD_IDENTIFIER
ORDINAL_POSITION	15
COLUMN_DEFAULT	NULL
IS_NULLABLE	NO
DATA_TYPE	longtext
CHARACTER_MAXIMUM_LENGTH	4294967295
CHARACTER_OCTET_LENGTH	4294967295
NUMERIC_PRECISION	NULL
NUMERIC_SCALE	NULL
DATETIME_PRECISION	NULL
CHARACTER_SET_NAME	utf8mb3
COLLATION_NAME	utf8mb3_general_ci
COLUMN_TYPE	longtext
COLUMN_KEY	
EXTRA	
PRIVILEGES	#
COLUMN_COMMENT	
IS_GENERATED	NEVER
GENERATION_EXPRESSION	NULL
TABLE_CATALOG	def
TABLE_SCHEMA	information_schema
TABLE_NAME	PARAMETERS
COLUMN_NAME	ROUTINE_TYPE
ORDINAL_POSITION	16
COLUMN_DEFAULT	NULL
IS_NULLABLE	NO
DATA_TYPE	varchar
CHARACTER_MAXIMUM_LENGTH	9
CHARACTER_OCTET_LENGTH	27
NUMERIC_PRECISION	NULL
NUMERIC_SCALE	NULL
DATETIME_PRECISION	NULL
CHARACTER_SET_NAME	utf8mb3
COLLATION_NAME	utf8mb3_general_ci
COLUMN_TYPE	varchar(9)
COLUMN_KEY	
EXTRA	
PRIVILEGES	#
COLUMN_COMMENT	
IS_GENERATED	NEVER
GENERATION_EXPRESSION	NULL
DESCRIBE INFORMATION_SCHEMA.PARAMETERS;
Field	Type	Null	Key	Default	Extra
SPECIFIC_CATALOG	varchar(512)	NO		NULL	
SPECIFIC_SCHEMA	varchar(64)	NO		NULL	
SPECIFIC_NAME	varchar(64)	NO		NULL	
ORDINAL_POSITION	int(21)	NO		NULL	
PARAMETER_MODE	varchar(5)	YES		NULL	
PARAMETER_NAME	varchar(64)	YES		NULL	
DATA_TYPE	varchar(64)	NO		NULL	
CHARACTER_MAXIMUM_LENGTH	int(21)	YES		NULL	
CHARACTER_OCTET_LENGTH	int(21)	YES		NULL	
NUMERIC_PRECISION	int(21)	YES		NULL	
NUMERIC_SCALE	int(21)	YES		NULL	
DATETIME_PRECISION	bigint(21) unsigned	YES		NULL	
CHARACTER_SET_NAME	varchar(64)	YES		NULL	
COLLATION_NAME	varchar(64)	YES		NULL	
DTD_IDENTIFIER	longtext	NO		NULL	
ROUTINE_TYPE	varchar(9)	NO		NULL	
# ========== parameters.2 ==========
DROP DATABASE IF EXISTS i_s_parameters_test;
CREATE DATABASE i_s_parameters_test;
USE i_s_parameters_test;
CREATE FUNCTION test_func1 (s char(20) RETURNS CHAR(50)
RETURN CONCAT('Hello', ,s,'!');
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'RETURNS CHAR(50)
RETURN CONCAT('Hello', ,s,'!')' at line 1
SELECT * FROM INFORMATION_SCHEMA.PARAMETERS
WHERE SPECIFIC_SCHEMA = 'i_s_parameters_test' AND SPECIFIC_NAME = 'test_func1';
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
# ========== parameters.3 ==========
DROP DATABASE IF EXISTS i_s_parameters_test;
CREATE DATABASE i_s_parameters_test;
USE i_s_parameters_test;
CREATE FUNCTION test_func1 (s char(20)) RETURNS CHAR(50)
RETURN CONCAT('Hello, ',s,'!');
SELECT * FROM INFORMATION_SCHEMA.PARAMETERS
WHERE SPECIFIC_SCHEMA = 'i_s_parameters_test' AND SPECIFIC_NAME = 'test_func1';
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
def	i_s_parameters_test	test_func1	0	NULL	NULL	char	50	50	NULL	NULL	NULL	latin1	latin1_swedish_ci	char(50)	FUNCTION
def	i_s_parameters_test	test_func1	1	IN	s	char	20	20	NULL	NULL	NULL	latin1	latin1_swedish_ci	char(20)	FUNCTION
DROP FUNCTION test_func1;
SELECT * FROM INFORMATION_SCHEMA.PARAMETERS
WHERE SPECIFIC_SCHEMA = 'i_s_parameters_test' AND SPECIFIC_NAME = 'test_func1';
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
# ========== parameters.4 ==========
DROP DATABASE IF EXISTS i_s_parameters_test;
CREATE DATABASE i_s_parameters_test;
USE i_s_parameters_test;
CREATE PROCEDURE testproc (IN param1 INT)
BEGIN
SELECT 2+2 as param1;
END;
//
SELECT * FROM INFORMATION_SCHEMA.PARAMETERS
WHERE SPECIFIC_SCHEMA = 'i_s_parameters_test' AND SPECIFIC_NAME = 'testproc';
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
def	i_s_parameters_test	testproc	1	IN	param1	int	NULL	NULL	10	0	NULL	NULL	NULL	int(11)	PROCEDURE
# ========== parameters.5 ==========
DROP DATABASE IF EXISTS i_s_parameters_test;
CREATE DATABASE i_s_parameters_test;
USE i_s_parameters_test;
CREATE PROCEDURE test_proc(INOUT P INT) SET @x=P*2;
SELECT * FROM INFORMATION_SCHEMA.PARAMETERS
WHERE SPECIFIC_SCHEMA = 'i_s_parameters_test' AND SPECIFIC_NAME = 'test_proc';
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
def	i_s_parameters_test	test_proc	1	INOUT	P	int	NULL	NULL	10	0	NULL	NULL	NULL	int(11)	PROCEDURE
# ========== parameters.6 ==========
DROP DATABASE IF EXISTS i_s_parameters_test;
CREATE DATABASE i_s_parameters_test;
USE i_s_parameters_test;
CREATE PROCEDURE test_proc(OUT p VARCHAR(10)) SET P='test';
SELECT * FROM INFORMATION_SCHEMA.PARAMETERS
WHERE SPECIFIC_SCHEMA = 'i_s_parameters_test' AND SPECIFIC_NAME = 'test_proc';
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
def	i_s_parameters_test	test_proc	1	OUT	p	varchar	10	10	NULL	NULL	NULL	latin1	latin1_swedish_ci	varchar(10)	PROCEDURE
# ========== parameters.7 ==========
DROP DATABASE IF EXISTS i_s_parameters_test;
CREATE DATABASE i_s_parameters_test;
USE i_s_parameters_test;
CREATE FUNCTION test_func1 (s char(20), t char(20)) RETURNS CHAR(40)
RETURN CONCAT(s,t);
SELECT * FROM INFORMATION_SCHEMA.PARAMETERS
WHERE SPECIFIC_SCHEMA = 'i_s_parameters_test' AND SPECIFIC_NAME = 'test_func1';
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
def	i_s_parameters_test	test_func1	0	NULL	NULL	char	40	40	NULL	NULL	NULL	latin1	latin1_swedish_ci	char(40)	FUNCTION
def	i_s_parameters_test	test_func1	1	IN	s	char	20	20	NULL	NULL	NULL	latin1	latin1_swedish_ci	char(20)	FUNCTION
def	i_s_parameters_test	test_func1	2	IN	t	char	20	20	NULL	NULL	NULL	latin1	latin1_swedish_ci	char(20)	FUNCTION
# ========== parameters.8 ==========
DROP DATABASE IF EXISTS i_s_parameters_test;
CREATE DATABASE i_s_parameters_test;
USE i_s_parameters_test;
CREATE FUNCTION test_func1 (s char(20)) RETURNS CHAR(50)
RETURN CONCAT('Hello, ',s,'!');
SELECT * FROM INFORMATION_SCHEMA.PARAMETERS
WHERE SPECIFIC_SCHEMA = 'i_s_parameters_test' AND SPECIFIC_NAME = 'test_func1';
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
def	i_s_parameters_test	test_func1	0	NULL	NULL	char	50	50	NULL	NULL	NULL	latin1	latin1_swedish_ci	char(50)	FUNCTION
def	i_s_parameters_test	test_func1	1	IN	s	char	20	20	NULL	NULL	NULL	latin1	latin1_swedish_ci	char(20)	FUNCTION
# ========== parameters.9 ==========
DROP DATABASE IF EXISTS i_s_parameters_test;
CREATE DATABASE i_s_parameters_test;
USE i_s_parameters_test;
CREATE FUNCTION test_func2 (s int) RETURNS INT RETURN s*2;
SELECT * FROM INFORMATION_SCHEMA.PARAMETERS
WHERE SPECIFIC_SCHEMA = 'i_s_parameters_test' AND SPECIFIC_NAME = 'test_func2';
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
def	i_s_parameters_test	test_func2	0	NULL	NULL	int	NULL	NULL	10	0	NULL	NULL	NULL	int(11)	FUNCTION
def	i_s_parameters_test	test_func2	1	IN	s	int	NULL	NULL	10	0	NULL	NULL	NULL	int(11)	FUNCTION
# ========== parameters.10 ==========
DROP DATABASE IF EXISTS i_s_parameters_test;
CREATE DATABASE i_s_parameters_test;
USE i_s_parameters_test;
CREATE FUNCTION test_func5 (s date) RETURNS TIMESTAMP
RETURN CURRENT_TIMESTAMP;
SELECT * FROM INFORMATION_SCHEMA.PARAMETERS
WHERE SPECIFIC_SCHEMA = 'i_s_parameters_test' AND SPECIFIC_NAME = 'test_func5';
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
def	i_s_parameters_test	test_func5	0	NULL	NULL	timestamp	NULL	NULL	NULL	NULL	0	NULL	NULL	timestamp	FUNCTION
def	i_s_parameters_test	test_func5	1	IN	s	date	NULL	NULL	NULL	NULL	NULL	NULL	NULL	date	FUNCTION
# ========== parameters.11 ==========
DROP DATABASE IF EXISTS i_s_parameters_test;
CREATE DATABASE i_s_parameters_test;
USE i_s_parameters_test;
CREATE FUNCTION test_func5 (s date) RETURNS TIMESTAMP
RETURN CURRENT_TIMESTAMP;
SELECT * FROM INFORMATION_SCHEMA.PARAMETERS
WHERE SPECIFIC_SCHEMA = 'i_s_parameters_test' AND SPECIFIC_NAME = 'test_func5';
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
def	i_s_parameters_test	test_func5	0	NULL	NULL	timestamp	NULL	NULL	NULL	NULL	0	NULL	NULL	timestamp	FUNCTION
def	i_s_parameters_test	test_func5	1	IN	s	date	NULL	NULL	NULL	NULL	NULL	NULL	NULL	date	FUNCTION
ALTER FUNCTION test_func5 COMMENT 'new comment added';
SELECT * FROM INFORMATION_SCHEMA.PARAMETERS
WHERE SPECIFIC_SCHEMA = 'i_s_parameters_test' AND SPECIFIC_NAME = 'test_func5';
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
def	i_s_parameters_test	test_func5	0	NULL	NULL	timestamp	NULL	NULL	NULL	NULL	0	NULL	NULL	timestamp	FUNCTION
def	i_s_parameters_test	test_func5	1	IN	s	date	NULL	NULL	NULL	NULL	NULL	NULL	NULL	date	FUNCTION
# ========== parameters.12 ==========
DROP DATABASE IF EXISTS i_s_parameters_test;
CREATE DATABASE i_s_parameters_test CHARACTER SET  utf8;
USE i_s_parameters_test;
CREATE FUNCTION test_func5 (s CHAR(20)) RETURNS VARCHAR(30)
RETURN CONCAT('XYZ, ' ,s);
SELECT * FROM INFORMATION_SCHEMA.PARAMETERS
WHERE SPECIFIC_SCHEMA = 'i_s_parameters_test' AND SPECIFIC_NAME = 'test_func5';
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
def	i_s_parameters_test	test_func5	0	NULL	NULL	varchar	30	90	NULL	NULL	NULL	utf8mb3	utf8mb3_general_ci	varchar(30)	FUNCTION
def	i_s_parameters_test	test_func5	1	IN	s	char	20	60	NULL	NULL	NULL	utf8mb3	utf8mb3_general_ci	char(20)	FUNCTION
DROP DATABASE i_s_parameters_test;
USE test;
#
# Start of 10.3 tests
#
#
# MDEV-15416 Crash when reading I_S.PARAMETERS
#
CREATE PROCEDURE p1(a0 TYPE OF t1.a,
a1 TYPE OF test.t1.a,
b0 ROW TYPE OF t1,
b1 ROW TYPE OF test.t1,
c ROW(a INT,b DOUBLE))
BEGIN
END;
$$
SELECT *, '--------' FROM INFORMATION_SCHEMA.PARAMETERS WHERE SPECIFIC_NAME = 'p1';
SPECIFIC_CATALOG	def
SPECIFIC_SCHEMA	test
SPECIFIC_NAME	p1
ORDINAL_POSITION	1
PARAMETER_MODE	IN
PARAMETER_NAME	a0
DATA_TYPE	TYPE OF
CHARACTER_MAXIMUM_LENGTH	NULL
CHARACTER_OCTET_LENGTH	NULL
NUMERIC_PRECISION	NULL
NUMERIC_SCALE	NULL
DATETIME_PRECISION	NULL
CHARACTER_SET_NAME	NULL
COLLATION_NAME	NULL
DTD_IDENTIFIER	TYPE OF `t1`.`a`
ROUTINE_TYPE	PROCEDURE
--------	--------
SPECIFIC_CATALOG	def
SPECIFIC_SCHEMA	test
SPECIFIC_NAME	p1
ORDINAL_POSITION	2
PARAMETER_MODE	IN
PARAMETER_NAME	a1
DATA_TYPE	TYPE OF
CHARACTER_MAXIMUM_LENGTH	NULL
CHARACTER_OCTET_LENGTH	NULL
NUMERIC_PRECISION	NULL
NUMERIC_SCALE	NULL
DATETIME_PRECISION	NULL
CHARACTER_SET_NAME	NULL
COLLATION_NAME	NULL
DTD_IDENTIFIER	TYPE OF `test`.`t1`.`a`
ROUTINE_TYPE	PROCEDURE
--------	--------
SPECIFIC_CATALOG	def
SPECIFIC_SCHEMA	test
SPECIFIC_NAME	p1
ORDINAL_POSITION	3
PARAMETER_MODE	IN
PARAMETER_NAME	b0
DATA_TYPE	ROW TYPE OF
CHARACTER_MAXIMUM_LENGTH	NULL
CHARACTER_OCTET_LENGTH	NULL
NUMERIC_PRECISION	NULL
NUMERIC_SCALE	NULL
DATETIME_PRECISION	NULL
CHARACTER_SET_NAME	NULL
COLLATION_NAME	NULL
DTD_IDENTIFIER	ROW TYPE OF `t1`
ROUTINE_TYPE	PROCEDURE
--------	--------
SPECIFIC_CATALOG	def
SPECIFIC_SCHEMA	test
SPECIFIC_NAME	p1
ORDINAL_POSITION	4
PARAMETER_MODE	IN
PARAMETER_NAME	b1
DATA_TYPE	ROW TYPE OF
CHARACTER_MAXIMUM_LENGTH	NULL
CHARACTER_OCTET_LENGTH	NULL
NUMERIC_PRECISION	NULL
NUMERIC_SCALE	NULL
DATETIME_PRECISION	NULL
CHARACTER_SET_NAME	NULL
COLLATION_NAME	NULL
DTD_IDENTIFIER	ROW TYPE OF `test`.`t1`
ROUTINE_TYPE	PROCEDURE
--------	--------
SPECIFIC_CATALOG	def
SPECIFIC_SCHEMA	test
SPECIFIC_NAME	p1
ORDINAL_POSITION	5
PARAMETER_MODE	IN
PARAMETER_NAME	c
DATA_TYPE	ROW
CHARACTER_MAXIMUM_LENGTH	NULL
CHARACTER_OCTET_LENGTH	NULL
NUMERIC_PRECISION	NULL
NUMERIC_SCALE	NULL
DATETIME_PRECISION	NULL
CHARACTER_SET_NAME	NULL
COLLATION_NAME	NULL
DTD_IDENTIFIER	ROW
ROUTINE_TYPE	PROCEDURE
--------	--------
DROP PROCEDURE p1;
#
# MDEV-20609 Full table scan in INFORMATION_SCHEMA.PARAMETERS/ROUTINES
#
DROP DATABASE IF EXISTS i_s_parameters_test;
CREATE DATABASE i_s_parameters_test;
USE i_s_parameters_test;
CREATE FUNCTION test_func5 (s CHAR(20)) RETURNS VARCHAR(30)
RETURN CONCAT('XYZ, ' ,s);
#
# We cannot use the index due to missing condition on SPECIFIC_SCHEMA,
# but we will use SPECIFIC_NAME for filtering records from mysql.proc
FLUSH STATUS;
SELECT * FROM INFORMATION_SCHEMA.PARAMETERS
WHERE SPECIFIC_NAME = 'test_func5';
SPECIFIC_CATALOG	def
SPECIFIC_SCHEMA	i_s_parameters_test
SPECIFIC_NAME	test_func5
ORDINAL_POSITION	0
PARAMETER_MODE	NULL
PARAMETER_NAME	NULL
DATA_TYPE	varchar
CHARACTER_MAXIMUM_LENGTH	30
CHARACTER_OCTET_LENGTH	30
NUMERIC_PRECISION	NULL
NUMERIC_SCALE	NULL
DATETIME_PRECISION	NULL
CHARACTER_SET_NAME	latin1
COLLATION_NAME	latin1_swedish_ci
DTD_IDENTIFIER	varchar(30)
ROUTINE_TYPE	FUNCTION
SPECIFIC_CATALOG	def
SPECIFIC_SCHEMA	i_s_parameters_test
SPECIFIC_NAME	test_func5
ORDINAL_POSITION	1
PARAMETER_MODE	IN
PARAMETER_NAME	s
DATA_TYPE	char
CHARACTER_MAXIMUM_LENGTH	20
CHARACTER_OCTET_LENGTH	20
NUMERIC_PRECISION	NULL
NUMERIC_SCALE	NULL
DATETIME_PRECISION	NULL
CHARACTER_SET_NAME	latin1
COLLATION_NAME	latin1_swedish_ci
DTD_IDENTIFIER	char(20)
ROUTINE_TYPE	FUNCTION
SHOW STATUS LIKE 'handler_read%next';
Variable_name	Value
Handler_read_next	count_routines
Handler_read_rnd_next	3
#
# We cannot use the index due to CONCAT(), and filtering by SPECIFIC_NAME
# does not work either since SPECIFIC_NAME = 'not_existing_proc'. See
# the difference in counters in comparison to the previous test
FLUSH STATUS;
SELECT * FROM INFORMATION_SCHEMA.PARAMETERS
WHERE CONCAT(SPECIFIC_SCHEMA) = 'i_s_parameters_test'
  AND SPECIFIC_NAME = 'not_existing_proc';
SHOW STATUS LIKE 'handler_read%next';
Variable_name	Value
Handler_read_next	count_routines
Handler_read_rnd_next	1
#
# Now the index must be used
FLUSH STATUS;
SELECT * FROM INFORMATION_SCHEMA.PARAMETERS
WHERE SPECIFIC_SCHEMA = 'i_s_parameters_test'
  AND SPECIFIC_NAME = 'test_func5';
SPECIFIC_CATALOG	def
SPECIFIC_SCHEMA	i_s_parameters_test
SPECIFIC_NAME	test_func5
ORDINAL_POSITION	0
PARAMETER_MODE	NULL
PARAMETER_NAME	NULL
DATA_TYPE	varchar
CHARACTER_MAXIMUM_LENGTH	30
CHARACTER_OCTET_LENGTH	30
NUMERIC_PRECISION	NULL
NUMERIC_SCALE	NULL
DATETIME_PRECISION	NULL
CHARACTER_SET_NAME	latin1
COLLATION_NAME	latin1_swedish_ci
DTD_IDENTIFIER	varchar(30)
ROUTINE_TYPE	FUNCTION
SPECIFIC_CATALOG	def
SPECIFIC_SCHEMA	i_s_parameters_test
SPECIFIC_NAME	test_func5
ORDINAL_POSITION	1
PARAMETER_MODE	IN
PARAMETER_NAME	s
DATA_TYPE	char
CHARACTER_MAXIMUM_LENGTH	20
CHARACTER_OCTET_LENGTH	20
NUMERIC_PRECISION	NULL
NUMERIC_SCALE	NULL
DATETIME_PRECISION	NULL
CHARACTER_SET_NAME	latin1
COLLATION_NAME	latin1_swedish_ci
DTD_IDENTIFIER	char(20)
ROUTINE_TYPE	FUNCTION
SHOW STATUS LIKE 'handler_read%next';
Variable_name	Value
Handler_read_next	1
Handler_read_rnd_next	3
#
# Using the first key part of the index
FLUSH STATUS;
SELECT * FROM INFORMATION_SCHEMA.PARAMETERS
WHERE SPECIFIC_SCHEMA = 'i_s_parameters_test';
SPECIFIC_CATALOG	def
SPECIFIC_SCHEMA	i_s_parameters_test
SPECIFIC_NAME	test_func5
ORDINAL_POSITION	0
PARAMETER_MODE	NULL
PARAMETER_NAME	NULL
DATA_TYPE	varchar
CHARACTER_MAXIMUM_LENGTH	30
CHARACTER_OCTET_LENGTH	30
NUMERIC_PRECISION	NULL
NUMERIC_SCALE	NULL
DATETIME_PRECISION	NULL
CHARACTER_SET_NAME	latin1
COLLATION_NAME	latin1_swedish_ci
DTD_IDENTIFIER	varchar(30)
ROUTINE_TYPE	FUNCTION
SPECIFIC_CATALOG	def
SPECIFIC_SCHEMA	i_s_parameters_test
SPECIFIC_NAME	test_func5
ORDINAL_POSITION	1
PARAMETER_MODE	IN
PARAMETER_NAME	s
DATA_TYPE	char
CHARACTER_MAXIMUM_LENGTH	20
CHARACTER_OCTET_LENGTH	20
NUMERIC_PRECISION	NULL
NUMERIC_SCALE	NULL
DATETIME_PRECISION	NULL
CHARACTER_SET_NAME	latin1
COLLATION_NAME	latin1_swedish_ci
DTD_IDENTIFIER	char(20)
ROUTINE_TYPE	FUNCTION
SHOW STATUS LIKE 'handler_read%next';
Variable_name	Value
Handler_read_next	1
Handler_read_rnd_next	3
#
# Test non-latin letters in procedure name
SET NAMES koi8r;
CREATE PROCEDURE `процедурка`(a INT) SELECT a;
#
# The index must be used
FLUSH STATUS;
SELECT * FROM INFORMATION_SCHEMA.PARAMETERS
WHERE SPECIFIC_SCHEMA = 'i_s_parameters_test'
  AND SPECIFIC_NAME = 'процедурка';
SPECIFIC_CATALOG	def
SPECIFIC_SCHEMA	i_s_parameters_test
SPECIFIC_NAME	процедурка
ORDINAL_POSITION	1
PARAMETER_MODE	IN
PARAMETER_NAME	a
DATA_TYPE	int
CHARACTER_MAXIMUM_LENGTH	NULL
CHARACTER_OCTET_LENGTH	NULL
NUMERIC_PRECISION	10
NUMERIC_SCALE	0
DATETIME_PRECISION	NULL
CHARACTER_SET_NAME	NULL
COLLATION_NAME	NULL
DTD_IDENTIFIER	int(11)
ROUTINE_TYPE	PROCEDURE
SHOW STATUS LIKE 'handler_read%next';
Variable_name	Value
Handler_read_next	1
Handler_read_rnd_next	2
SELECT COUNT(*) FROM information_schema.PARAMETERS
WHERE SPECIFIC_CATALOG = NULL;
COUNT(*)
#
DROP DATABASE i_s_parameters_test;
USE test;