path: root/mysql-test/r/information_schema_routines.result

# ========== routines.1 ==========
USE INFORMATION_SCHEMA;
SHOW CREATE TABLE INFORMATION_SCHEMA.ROUTINES;
Table	Create Table
ROUTINES	CREATE TEMPORARY TABLE `ROUTINES` (
  `SPECIFIC_NAME` varchar(64) NOT NULL DEFAULT '',
  `ROUTINE_CATALOG` varchar(512) NOT NULL DEFAULT '',
  `ROUTINE_SCHEMA` varchar(64) NOT NULL DEFAULT '',
  `ROUTINE_NAME` varchar(64) NOT NULL DEFAULT '',
  `ROUTINE_TYPE` varchar(9) NOT NULL DEFAULT '',
  `DATA_TYPE` varchar(64) NOT NULL DEFAULT '',
  `CHARACTER_MAXIMUM_LENGTH` int(21) DEFAULT NULL,
  `CHARACTER_OCTET_LENGTH` int(21) DEFAULT NULL,
  `NUMERIC_PRECISION` int(21) DEFAULT NULL,
  `NUMERIC_SCALE` int(21) DEFAULT NULL,
  `DATETIME_PRECISION` bigint(21) unsigned DEFAULT NULL,
  `CHARACTER_SET_NAME` varchar(64) DEFAULT NULL,
  `COLLATION_NAME` varchar(64) DEFAULT NULL,
  `DTD_IDENTIFIER` longtext,
  `ROUTINE_BODY` varchar(8) NOT NULL DEFAULT '',
  `ROUTINE_DEFINITION` longtext,
  `EXTERNAL_NAME` varchar(64) DEFAULT NULL,
  `EXTERNAL_LANGUAGE` varchar(64) DEFAULT NULL,
  `PARAMETER_STYLE` varchar(8) NOT NULL DEFAULT '',
  `IS_DETERMINISTIC` varchar(3) NOT NULL DEFAULT '',
  `SQL_DATA_ACCESS` varchar(64) NOT NULL DEFAULT '',
  `SQL_PATH` varchar(64) DEFAULT NULL,
  `SECURITY_TYPE` varchar(7) NOT NULL DEFAULT '',
  `CREATED` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `LAST_ALTERED` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `SQL_MODE` varchar(8192) NOT NULL DEFAULT '',
  `ROUTINE_COMMENT` longtext NOT NULL,
  `DEFINER` varchar(189) NOT NULL DEFAULT '',
  `CHARACTER_SET_CLIENT` varchar(32) NOT NULL DEFAULT '',
  `COLLATION_CONNECTION` varchar(32) NOT NULL DEFAULT '',
  `DATABASE_COLLATION` varchar(32) NOT NULL DEFAULT ''
)  DEFAULT CHARSET=utf8
SELECT * FROM information_schema.columns
WHERE table_schema = 'information_schema'
  AND table_name   = 'routines'
ORDER BY ordinal_position;
TABLE_CATALOG	def
TABLE_SCHEMA	information_schema
TABLE_NAME	ROUTINES
COLUMN_NAME	SPECIFIC_NAME
ORDINAL_POSITION	1
COLUMN_DEFAULT	
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	utf8
COLLATION_NAME	utf8_general_ci
COLUMN_TYPE	varchar(64)
COLUMN_KEY	
EXTRA	
PRIVILEGES	#
COLUMN_COMMENT	
TABLE_CATALOG	def
TABLE_SCHEMA	information_schema
TABLE_NAME	ROUTINES
COLUMN_NAME	ROUTINE_CATALOG
ORDINAL_POSITION	2
COLUMN_DEFAULT	
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	utf8
COLLATION_NAME	utf8_general_ci
COLUMN_TYPE	varchar(512)
COLUMN_KEY	
EXTRA	
PRIVILEGES	#
COLUMN_COMMENT	
TABLE_CATALOG	def
TABLE_SCHEMA	information_schema
TABLE_NAME	ROUTINES
COLUMN_NAME	ROUTINE_SCHEMA
ORDINAL_POSITION	3
COLUMN_DEFAULT	
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	utf8
COLLATION_NAME	utf8_general_ci
COLUMN_TYPE	varchar(64)
COLUMN_KEY	
EXTRA	
PRIVILEGES	#
COLUMN_COMMENT	
TABLE_CATALOG	def
TABLE_SCHEMA	information_schema
TABLE_NAME	ROUTINES
COLUMN_NAME	ROUTINE_NAME
ORDINAL_POSITION	4
COLUMN_DEFAULT	
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	utf8
COLLATION_NAME	utf8_general_ci
COLUMN_TYPE	varchar(64)
COLUMN_KEY	
EXTRA	
PRIVILEGES	#
COLUMN_COMMENT	
TABLE_CATALOG	def
TABLE_SCHEMA	information_schema
TABLE_NAME	ROUTINES
COLUMN_NAME	ROUTINE_TYPE
ORDINAL_POSITION	5
COLUMN_DEFAULT	
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	utf8
COLLATION_NAME	utf8_general_ci
COLUMN_TYPE	varchar(9)
COLUMN_KEY	
EXTRA	
PRIVILEGES	#
COLUMN_COMMENT	
TABLE_CATALOG	def
TABLE_SCHEMA	information_schema
TABLE_NAME	ROUTINES
COLUMN_NAME	DATA_TYPE
ORDINAL_POSITION	6
COLUMN_DEFAULT	
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	utf8
COLLATION_NAME	utf8_general_ci
COLUMN_TYPE	varchar(64)
COLUMN_KEY	
EXTRA	
PRIVILEGES	#
COLUMN_COMMENT	
TABLE_CATALOG	def
TABLE_SCHEMA	information_schema
TABLE_NAME	ROUTINES
COLUMN_NAME	CHARACTER_MAXIMUM_LENGTH
ORDINAL_POSITION	7
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	
TABLE_CATALOG	def
TABLE_SCHEMA	information_schema
TABLE_NAME	ROUTINES
COLUMN_NAME	CHARACTER_OCTET_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	
TABLE_CATALOG	def
TABLE_SCHEMA	information_schema
TABLE_NAME	ROUTINES
COLUMN_NAME	NUMERIC_PRECISION
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	
TABLE_CATALOG	def
TABLE_SCHEMA	information_schema
TABLE_NAME	ROUTINES
COLUMN_NAME	NUMERIC_SCALE
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	
TABLE_CATALOG	def
TABLE_SCHEMA	information_schema
TABLE_NAME	ROUTINES
COLUMN_NAME	DATETIME_PRECISION
ORDINAL_POSITION	11
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	
TABLE_CATALOG	def
TABLE_SCHEMA	information_schema
TABLE_NAME	ROUTINES
COLUMN_NAME	CHARACTER_SET_NAME
ORDINAL_POSITION	12
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	utf8
COLLATION_NAME	utf8_general_ci
COLUMN_TYPE	varchar(64)
COLUMN_KEY	
EXTRA	
PRIVILEGES	#
COLUMN_COMMENT	
TABLE_CATALOG	def
TABLE_SCHEMA	information_schema
TABLE_NAME	ROUTINES
COLUMN_NAME	COLLATION_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	utf8
COLLATION_NAME	utf8_general_ci
COLUMN_TYPE	varchar(64)
COLUMN_KEY	
EXTRA	
PRIVILEGES	#
COLUMN_COMMENT	
TABLE_CATALOG	def
TABLE_SCHEMA	information_schema
TABLE_NAME	ROUTINES
COLUMN_NAME	DTD_IDENTIFIER
ORDINAL_POSITION	14
COLUMN_DEFAULT	NULL
IS_NULLABLE	YES
DATA_TYPE	longtext
CHARACTER_MAXIMUM_LENGTH	4294967295
CHARACTER_OCTET_LENGTH	4294967295
NUMERIC_PRECISION	NULL
NUMERIC_SCALE	NULL
DATETIME_PRECISION	NULL
CHARACTER_SET_NAME	utf8
COLLATION_NAME	utf8_general_ci
COLUMN_TYPE	longtext
COLUMN_KEY	
EXTRA	
PRIVILEGES	#
COLUMN_COMMENT	
TABLE_CATALOG	def
TABLE_SCHEMA	information_schema
TABLE_NAME	ROUTINES
COLUMN_NAME	ROUTINE_BODY
ORDINAL_POSITION	15
COLUMN_DEFAULT	
IS_NULLABLE	NO
DATA_TYPE	varchar
CHARACTER_MAXIMUM_LENGTH	8
CHARACTER_OCTET_LENGTH	24
NUMERIC_PRECISION	NULL
NUMERIC_SCALE	NULL
DATETIME_PRECISION	NULL
CHARACTER_SET_NAME	utf8
COLLATION_NAME	utf8_general_ci
COLUMN_TYPE	varchar(8)
COLUMN_KEY	
EXTRA	
PRIVILEGES	#
COLUMN_COMMENT	
TABLE_CATALOG	def
TABLE_SCHEMA	information_schema
TABLE_NAME	ROUTINES
COLUMN_NAME	ROUTINE_DEFINITION
ORDINAL_POSITION	16
COLUMN_DEFAULT	NULL
IS_NULLABLE	YES
DATA_TYPE	longtext
CHARACTER_MAXIMUM_LENGTH	4294967295
CHARACTER_OCTET_LENGTH	4294967295
NUMERIC_PRECISION	NULL
NUMERIC_SCALE	NULL
DATETIME_PRECISION	NULL
CHARACTER_SET_NAME	utf8
COLLATION_NAME	utf8_general_ci
COLUMN_TYPE	longtext
COLUMN_KEY	
EXTRA	
PRIVILEGES	#
COLUMN_COMMENT	
TABLE_CATALOG	def
TABLE_SCHEMA	information_schema
TABLE_NAME	ROUTINES
COLUMN_NAME	EXTERNAL_NAME
ORDINAL_POSITION	17
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	utf8
COLLATION_NAME	utf8_general_ci
COLUMN_TYPE	varchar(64)
COLUMN_KEY	
EXTRA	
PRIVILEGES	#
COLUMN_COMMENT	
TABLE_CATALOG	def
TABLE_SCHEMA	information_schema
TABLE_NAME	ROUTINES
COLUMN_NAME	EXTERNAL_LANGUAGE
ORDINAL_POSITION	18
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	utf8
COLLATION_NAME	utf8_general_ci
COLUMN_TYPE	varchar(64)
COLUMN_KEY	
EXTRA	
PRIVILEGES	#
COLUMN_COMMENT	
TABLE_CATALOG	def
TABLE_SCHEMA	information_schema
TABLE_NAME	ROUTINES
COLUMN_NAME	PARAMETER_STYLE
ORDINAL_POSITION	19
COLUMN_DEFAULT	
IS_NULLABLE	NO
DATA_TYPE	varchar
CHARACTER_MAXIMUM_LENGTH	8
CHARACTER_OCTET_LENGTH	24
NUMERIC_PRECISION	NULL
NUMERIC_SCALE	NULL
DATETIME_PRECISION	NULL
CHARACTER_SET_NAME	utf8
COLLATION_NAME	utf8_general_ci
COLUMN_TYPE	varchar(8)
COLUMN_KEY	
EXTRA	
PRIVILEGES	#
COLUMN_COMMENT	
TABLE_CATALOG	def
TABLE_SCHEMA	information_schema
TABLE_NAME	ROUTINES
COLUMN_NAME	IS_DETERMINISTIC
ORDINAL_POSITION	20
COLUMN_DEFAULT	
IS_NULLABLE	NO
DATA_TYPE	varchar
CHARACTER_MAXIMUM_LENGTH	3
CHARACTER_OCTET_LENGTH	9
NUMERIC_PRECISION	NULL
NUMERIC_SCALE	NULL
DATETIME_PRECISION	NULL
CHARACTER_SET_NAME	utf8
COLLATION_NAME	utf8_general_ci
COLUMN_TYPE	varchar(3)
COLUMN_KEY	
EXTRA	
PRIVILEGES	#
COLUMN_COMMENT	
TABLE_CATALOG	def
TABLE_SCHEMA	information_schema
TABLE_NAME	ROUTINES
COLUMN_NAME	SQL_DATA_ACCESS
ORDINAL_POSITION	21
COLUMN_DEFAULT	
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	utf8
COLLATION_NAME	utf8_general_ci
COLUMN_TYPE	varchar(64)
COLUMN_KEY	
EXTRA	
PRIVILEGES	#
COLUMN_COMMENT	
TABLE_CATALOG	def
TABLE_SCHEMA	information_schema
TABLE_NAME	ROUTINES
COLUMN_NAME	SQL_PATH
ORDINAL_POSITION	22
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	utf8
COLLATION_NAME	utf8_general_ci
COLUMN_TYPE	varchar(64)
COLUMN_KEY	
EXTRA	
PRIVILEGES	#
COLUMN_COMMENT	
TABLE_CATALOG	def
TABLE_SCHEMA	information_schema
TABLE_NAME	ROUTINES
COLUMN_NAME	SECURITY_TYPE
ORDINAL_POSITION	23
COLUMN_DEFAULT	
IS_NULLABLE	NO
DATA_TYPE	varchar
CHARACTER_MAXIMUM_LENGTH	7
CHARACTER_OCTET_LENGTH	21
NUMERIC_PRECISION	NULL
NUMERIC_SCALE	NULL
DATETIME_PRECISION	NULL
CHARACTER_SET_NAME	utf8
COLLATION_NAME	utf8_general_ci
COLUMN_TYPE	varchar(7)
COLUMN_KEY	
EXTRA	
PRIVILEGES	#
COLUMN_COMMENT	
TABLE_CATALOG	def
TABLE_SCHEMA	information_schema
TABLE_NAME	ROUTINES
COLUMN_NAME	CREATED
ORDINAL_POSITION	24
COLUMN_DEFAULT	0000-00-00 00:00:00
IS_NULLABLE	NO
DATA_TYPE	datetime
CHARACTER_MAXIMUM_LENGTH	NULL
CHARACTER_OCTET_LENGTH	NULL
NUMERIC_PRECISION	NULL
NUMERIC_SCALE	NULL
DATETIME_PRECISION	0
CHARACTER_SET_NAME	NULL
COLLATION_NAME	NULL
COLUMN_TYPE	datetime
COLUMN_KEY	
EXTRA	
PRIVILEGES	#
COLUMN_COMMENT	
TABLE_CATALOG	def
TABLE_SCHEMA	information_schema
TABLE_NAME	ROUTINES
COLUMN_NAME	LAST_ALTERED
ORDINAL_POSITION	25
COLUMN_DEFAULT	0000-00-00 00:00:00
IS_NULLABLE	NO
DATA_TYPE	datetime
CHARACTER_MAXIMUM_LENGTH	NULL
CHARACTER_OCTET_LENGTH	NULL
NUMERIC_PRECISION	NULL
NUMERIC_SCALE	NULL
DATETIME_PRECISION	0
CHARACTER_SET_NAME	NULL
COLLATION_NAME	NULL
COLUMN_TYPE	datetime
COLUMN_KEY	
EXTRA	
PRIVILEGES	#
COLUMN_COMMENT	
TABLE_CATALOG	def
TABLE_SCHEMA	information_schema
TABLE_NAME	ROUTINES
COLUMN_NAME	SQL_MODE
ORDINAL_POSITION	26
COLUMN_DEFAULT	
IS_NULLABLE	NO
DATA_TYPE	varchar
CHARACTER_MAXIMUM_LENGTH	8192
CHARACTER_OCTET_LENGTH	24576
NUMERIC_PRECISION	NULL
NUMERIC_SCALE	NULL
DATETIME_PRECISION	NULL
CHARACTER_SET_NAME	utf8
COLLATION_NAME	utf8_general_ci
COLUMN_TYPE	varchar(8192)
COLUMN_KEY	
EXTRA	
PRIVILEGES	#
COLUMN_COMMENT	
TABLE_CATALOG	def
TABLE_SCHEMA	information_schema
TABLE_NAME	ROUTINES
COLUMN_NAME	ROUTINE_COMMENT
ORDINAL_POSITION	27
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	utf8
COLLATION_NAME	utf8_general_ci
COLUMN_TYPE	longtext
COLUMN_KEY	
EXTRA	
PRIVILEGES	#
COLUMN_COMMENT	
TABLE_CATALOG	def
TABLE_SCHEMA	information_schema
TABLE_NAME	ROUTINES
COLUMN_NAME	DEFINER
ORDINAL_POSITION	28
COLUMN_DEFAULT	
IS_NULLABLE	NO
DATA_TYPE	varchar
CHARACTER_MAXIMUM_LENGTH	189
CHARACTER_OCTET_LENGTH	567
NUMERIC_PRECISION	NULL
NUMERIC_SCALE	NULL
DATETIME_PRECISION	NULL
CHARACTER_SET_NAME	utf8
COLLATION_NAME	utf8_general_ci
COLUMN_TYPE	varchar(189)
COLUMN_KEY	
EXTRA	
PRIVILEGES	#
COLUMN_COMMENT	
TABLE_CATALOG	def
TABLE_SCHEMA	information_schema
TABLE_NAME	ROUTINES
COLUMN_NAME	CHARACTER_SET_CLIENT
ORDINAL_POSITION	29
COLUMN_DEFAULT	
IS_NULLABLE	NO
DATA_TYPE	varchar
CHARACTER_MAXIMUM_LENGTH	32
CHARACTER_OCTET_LENGTH	96
NUMERIC_PRECISION	NULL
NUMERIC_SCALE	NULL
DATETIME_PRECISION	NULL
CHARACTER_SET_NAME	utf8
COLLATION_NAME	utf8_general_ci
COLUMN_TYPE	varchar(32)
COLUMN_KEY	
EXTRA	
PRIVILEGES	#
COLUMN_COMMENT	
TABLE_CATALOG	def
TABLE_SCHEMA	information_schema
TABLE_NAME	ROUTINES
COLUMN_NAME	COLLATION_CONNECTION
ORDINAL_POSITION	30
COLUMN_DEFAULT	
IS_NULLABLE	NO
DATA_TYPE	varchar
CHARACTER_MAXIMUM_LENGTH	32
CHARACTER_OCTET_LENGTH	96
NUMERIC_PRECISION	NULL
NUMERIC_SCALE	NULL
DATETIME_PRECISION	NULL
CHARACTER_SET_NAME	utf8
COLLATION_NAME	utf8_general_ci
COLUMN_TYPE	varchar(32)
COLUMN_KEY	
EXTRA	
PRIVILEGES	#
COLUMN_COMMENT	
TABLE_CATALOG	def
TABLE_SCHEMA	information_schema
TABLE_NAME	ROUTINES
COLUMN_NAME	DATABASE_COLLATION
ORDINAL_POSITION	31
COLUMN_DEFAULT	
IS_NULLABLE	NO
DATA_TYPE	varchar
CHARACTER_MAXIMUM_LENGTH	32
CHARACTER_OCTET_LENGTH	96
NUMERIC_PRECISION	NULL
NUMERIC_SCALE	NULL
DATETIME_PRECISION	NULL
CHARACTER_SET_NAME	utf8
COLLATION_NAME	utf8_general_ci
COLUMN_TYPE	varchar(32)
COLUMN_KEY	
EXTRA	
PRIVILEGES	#
COLUMN_COMMENT	
DESCRIBE INFORMATION_SCHEMA.ROUTINES;
Field	Type	Null	Key	Default	Extra
SPECIFIC_NAME	varchar(64)	NO			
ROUTINE_CATALOG	varchar(512)	NO			
ROUTINE_SCHEMA	varchar(64)	NO			
ROUTINE_NAME	varchar(64)	NO			
ROUTINE_TYPE	varchar(9)	NO			
DATA_TYPE	varchar(64)	NO			
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	YES		NULL	
ROUTINE_BODY	varchar(8)	NO			
ROUTINE_DEFINITION	longtext	YES		NULL	
EXTERNAL_NAME	varchar(64)	YES		NULL	
EXTERNAL_LANGUAGE	varchar(64)	YES		NULL	
PARAMETER_STYLE	varchar(8)	NO			
IS_DETERMINISTIC	varchar(3)	NO			
SQL_DATA_ACCESS	varchar(64)	NO			
SQL_PATH	varchar(64)	YES		NULL	
SECURITY_TYPE	varchar(7)	NO			
CREATED	datetime	NO		0000-00-00 00:00:00	
LAST_ALTERED	datetime	NO		0000-00-00 00:00:00	
SQL_MODE	varchar(8192)	NO			
ROUTINE_COMMENT	longtext	NO		NULL	
DEFINER	varchar(189)	NO			
CHARACTER_SET_CLIENT	varchar(32)	NO			
COLLATION_CONNECTION	varchar(32)	NO			
DATABASE_COLLATION	varchar(32)	NO			
# ========== routines.2 ==========
DROP DATABASE IF EXISTS i_s_routines_test;
CREATE DATABASE i_s_routines_test;
USE i_s_routines_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.ROUTINES
WHERE ROUTINE_SCHEMA = 'i_s_routines_test' AND ROUTINE_NAME = 'test_func1';
SPECIFIC_NAME	ROUTINE_CATALOG	ROUTINE_SCHEMA	ROUTINE_NAME	ROUTINE_TYPE	DATA_TYPE	CHARACTER_MAXIMUM_LENGTH	CHARACTER_OCTET_LENGTH	NUMERIC_PRECISION	NUMERIC_SCALE	DATETIME_PRECISION	CHARACTER_SET_NAME	COLLATION_NAME	DTD_IDENTIFIER	ROUTINE_BODY	ROUTINE_DEFINITION	EXTERNAL_NAME	EXTERNAL_LANGUAGE	PARAMETER_STYLE	IS_DETERMINISTIC	SQL_DATA_ACCESS	SQL_PATH	SECURITY_TYPE	CREATED	LAST_ALTERED	SQL_MODE	ROUTINE_COMMENT	DEFINER	CHARACTER_SET_CLIENT	COLLATION_CONNECTION	DATABASE_COLLATION
# ========== routines.3 ==========
DROP DATABASE IF EXISTS i_s_routines_test;
CREATE DATABASE i_s_routines_test;
USE i_s_routines_test;
CREATE FUNCTION test_func1 (s char(20)) RETURNS CHAR(50)
RETURN CONCAT('Hello, ',s,'!');
SELECT * FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_SCHEMA = 'i_s_routines_test' AND ROUTINE_NAME = 'test_func1';
SPECIFIC_NAME	ROUTINE_CATALOG	ROUTINE_SCHEMA	ROUTINE_NAME	ROUTINE_TYPE	DATA_TYPE	CHARACTER_MAXIMUM_LENGTH	CHARACTER_OCTET_LENGTH	NUMERIC_PRECISION	NUMERIC_SCALE	DATETIME_PRECISION	CHARACTER_SET_NAME	COLLATION_NAME	DTD_IDENTIFIER	ROUTINE_BODY	ROUTINE_DEFINITION	EXTERNAL_NAME	EXTERNAL_LANGUAGE	PARAMETER_STYLE	IS_DETERMINISTIC	SQL_DATA_ACCESS	SQL_PATH	SECURITY_TYPE	CREATED	LAST_ALTERED	SQL_MODE	ROUTINE_COMMENT	DEFINER	CHARACTER_SET_CLIENT	COLLATION_CONNECTION	DATABASE_COLLATION
test_func1	def	i_s_routines_test	test_func1	FUNCTION	char	50	50	NULL	NULL	NULL	latin1	latin1_swedish_ci	char(50)	SQL	RETURN CONCAT('Hello, ',s,'!')	NULL	NULL	SQL	NO	CONTAINS SQL	NULL	DEFINER	<created>	<modified>			root@localhost	latin1	latin1_swedish_ci	latin1_swedish_ci
DROP FUNCTION test_func1;
SELECT * FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_SCHEMA = 'i_s_routines_test' AND ROUTINE_NAME = 'test_func1';
SPECIFIC_NAME	ROUTINE_CATALOG	ROUTINE_SCHEMA	ROUTINE_NAME	ROUTINE_TYPE	DATA_TYPE	CHARACTER_MAXIMUM_LENGTH	CHARACTER_OCTET_LENGTH	NUMERIC_PRECISION	NUMERIC_SCALE	DATETIME_PRECISION	CHARACTER_SET_NAME	COLLATION_NAME	DTD_IDENTIFIER	ROUTINE_BODY	ROUTINE_DEFINITION	EXTERNAL_NAME	EXTERNAL_LANGUAGE	PARAMETER_STYLE	IS_DETERMINISTIC	SQL_DATA_ACCESS	SQL_PATH	SECURITY_TYPE	CREATED	LAST_ALTERED	SQL_MODE	ROUTINE_COMMENT	DEFINER	CHARACTER_SET_CLIENT	COLLATION_CONNECTION	DATABASE_COLLATION
# ========== routines.4 ==========
DROP DATABASE IF EXISTS i_s_routines_test;
CREATE DATABASE i_s_routines_test;
USE i_s_routines_test;
CREATE PROCEDURE testproc (OUT param1 INT)
BEGIN
SELECT 2+2 as param1;
END;
//
SELECT * FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_SCHEMA = 'i_s_routines_test' AND ROUTINE_NAME = 'testproc';
SPECIFIC_NAME	ROUTINE_CATALOG	ROUTINE_SCHEMA	ROUTINE_NAME	ROUTINE_TYPE	DATA_TYPE	CHARACTER_MAXIMUM_LENGTH	CHARACTER_OCTET_LENGTH	NUMERIC_PRECISION	NUMERIC_SCALE	DATETIME_PRECISION	CHARACTER_SET_NAME	COLLATION_NAME	DTD_IDENTIFIER	ROUTINE_BODY	ROUTINE_DEFINITION	EXTERNAL_NAME	EXTERNAL_LANGUAGE	PARAMETER_STYLE	IS_DETERMINISTIC	SQL_DATA_ACCESS	SQL_PATH	SECURITY_TYPE	CREATED	LAST_ALTERED	SQL_MODE	ROUTINE_COMMENT	DEFINER	CHARACTER_SET_CLIENT	COLLATION_CONNECTION	DATABASE_COLLATION
testproc	def	i_s_routines_test	testproc	PROCEDURE		NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	SQL	BEGIN
SELECT 2+2 as param1;
END	NULL	NULL	SQL	NO	CONTAINS SQL	NULL	DEFINER	<created>	<modified>			root@localhost	latin1	latin1_swedish_ci	latin1_swedish_ci
# ========== routines.5 ==========
DROP DATABASE IF EXISTS i_s_routines_test;
CREATE DATABASE i_s_routines_test;
USE i_s_routines_test;
CREATE FUNCTION test_func1 (s char(20)) RETURNS CHAR(50)
RETURN CONCAT('Hello, ',s,'!');
SELECT * FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_SCHEMA = 'i_s_routines_test' AND ROUTINE_NAME = 'test_func1';
SPECIFIC_NAME	ROUTINE_CATALOG	ROUTINE_SCHEMA	ROUTINE_NAME	ROUTINE_TYPE	DATA_TYPE	CHARACTER_MAXIMUM_LENGTH	CHARACTER_OCTET_LENGTH	NUMERIC_PRECISION	NUMERIC_SCALE	DATETIME_PRECISION	CHARACTER_SET_NAME	COLLATION_NAME	DTD_IDENTIFIER	ROUTINE_BODY	ROUTINE_DEFINITION	EXTERNAL_NAME	EXTERNAL_LANGUAGE	PARAMETER_STYLE	IS_DETERMINISTIC	SQL_DATA_ACCESS	SQL_PATH	SECURITY_TYPE	CREATED	LAST_ALTERED	SQL_MODE	ROUTINE_COMMENT	DEFINER	CHARACTER_SET_CLIENT	COLLATION_CONNECTION	DATABASE_COLLATION
test_func1	def	i_s_routines_test	test_func1	FUNCTION	char	50	50	NULL	NULL	NULL	latin1	latin1_swedish_ci	char(50)	SQL	RETURN CONCAT('Hello, ',s,'!')	NULL	NULL	SQL	NO	CONTAINS SQL	NULL	DEFINER	<created>	<modified>			root@localhost	latin1	latin1_swedish_ci	latin1_swedish_ci
# ========== routines.6 ==========
DROP DATABASE IF EXISTS i_s_routines_test;
CREATE DATABASE i_s_routines_test;
USE i_s_routines_test;
CREATE FUNCTION test_func2 (s int) RETURNS INT RETURN s*2;
SELECT * FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_SCHEMA = 'i_s_routines_test' AND ROUTINE_NAME = 'test_func2';
SPECIFIC_NAME	ROUTINE_CATALOG	ROUTINE_SCHEMA	ROUTINE_NAME	ROUTINE_TYPE	DATA_TYPE	CHARACTER_MAXIMUM_LENGTH	CHARACTER_OCTET_LENGTH	NUMERIC_PRECISION	NUMERIC_SCALE	DATETIME_PRECISION	CHARACTER_SET_NAME	COLLATION_NAME	DTD_IDENTIFIER	ROUTINE_BODY	ROUTINE_DEFINITION	EXTERNAL_NAME	EXTERNAL_LANGUAGE	PARAMETER_STYLE	IS_DETERMINISTIC	SQL_DATA_ACCESS	SQL_PATH	SECURITY_TYPE	CREATED	LAST_ALTERED	SQL_MODE	ROUTINE_COMMENT	DEFINER	CHARACTER_SET_CLIENT	COLLATION_CONNECTION	DATABASE_COLLATION
test_func2	def	i_s_routines_test	test_func2	FUNCTION	int	NULL	NULL	10	0	NULL	NULL	NULL	int(11)	SQL	RETURN s*2	NULL	NULL	SQL	NO	CONTAINS SQL	NULL	DEFINER	<created>	<modified>			root@localhost	latin1	latin1_swedish_ci	latin1_swedish_ci
# ========== routines.7 ==========
DROP DATABASE IF EXISTS i_s_routines_test;
CREATE DATABASE i_s_routines_test;
USE i_s_routines_test;
CREATE FUNCTION test_func5 (s date) RETURNS TIMESTAMP
RETURN CURRENT_TIMESTAMP;
SELECT * FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_SCHEMA = 'i_s_routines_test' AND ROUTINE_NAME = 'test_func5';
SPECIFIC_NAME	ROUTINE_CATALOG	ROUTINE_SCHEMA	ROUTINE_NAME	ROUTINE_TYPE	DATA_TYPE	CHARACTER_MAXIMUM_LENGTH	CHARACTER_OCTET_LENGTH	NUMERIC_PRECISION	NUMERIC_SCALE	DATETIME_PRECISION	CHARACTER_SET_NAME	COLLATION_NAME	DTD_IDENTIFIER	ROUTINE_BODY	ROUTINE_DEFINITION	EXTERNAL_NAME	EXTERNAL_LANGUAGE	PARAMETER_STYLE	IS_DETERMINISTIC	SQL_DATA_ACCESS	SQL_PATH	SECURITY_TYPE	CREATED	LAST_ALTERED	SQL_MODE	ROUTINE_COMMENT	DEFINER	CHARACTER_SET_CLIENT	COLLATION_CONNECTION	DATABASE_COLLATION
test_func5	def	i_s_routines_test	test_func5	FUNCTION	timestamp	NULL	NULL	NULL	NULL	0	NULL	NULL	timestamp	SQL	RETURN CURRENT_TIMESTAMP	NULL	NULL	SQL	NO	CONTAINS SQL	NULL	DEFINER	<created>	<modified>			root@localhost	latin1	latin1_swedish_ci	latin1_swedish_ci
# ========== routines.8 ==========
DROP DATABASE IF EXISTS i_s_routines_test;
CREATE DATABASE i_s_routines_test;
USE i_s_routines_test;
CREATE FUNCTION test_func5 (s date) RETURNS TIMESTAMP
RETURN CURRENT_TIMESTAMP;
SELECT * FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_SCHEMA = 'i_s_routines_test' AND ROUTINE_NAME = 'test_func5';
SPECIFIC_NAME	ROUTINE_CATALOG	ROUTINE_SCHEMA	ROUTINE_NAME	ROUTINE_TYPE	DATA_TYPE	CHARACTER_MAXIMUM_LENGTH	CHARACTER_OCTET_LENGTH	NUMERIC_PRECISION	NUMERIC_SCALE	DATETIME_PRECISION	CHARACTER_SET_NAME	COLLATION_NAME	DTD_IDENTIFIER	ROUTINE_BODY	ROUTINE_DEFINITION	EXTERNAL_NAME	EXTERNAL_LANGUAGE	PARAMETER_STYLE	IS_DETERMINISTIC	SQL_DATA_ACCESS	SQL_PATH	SECURITY_TYPE	CREATED	LAST_ALTERED	SQL_MODE	ROUTINE_COMMENT	DEFINER	CHARACTER_SET_CLIENT	COLLATION_CONNECTION	DATABASE_COLLATION
test_func5	def	i_s_routines_test	test_func5	FUNCTION	timestamp	NULL	NULL	NULL	NULL	0	NULL	NULL	timestamp	SQL	RETURN CURRENT_TIMESTAMP	NULL	NULL	SQL	NO	CONTAINS SQL	NULL	DEFINER	<created>	<modified>			root@localhost	latin1	latin1_swedish_ci	latin1_swedish_ci
ALTER FUNCTION test_func5 COMMENT 'new comment added';
SELECT * FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_SCHEMA = 'i_s_routines_test' AND ROUTINE_NAME = 'test_func5';
SPECIFIC_NAME	ROUTINE_CATALOG	ROUTINE_SCHEMA	ROUTINE_NAME	ROUTINE_TYPE	DATA_TYPE	CHARACTER_MAXIMUM_LENGTH	CHARACTER_OCTET_LENGTH	NUMERIC_PRECISION	NUMERIC_SCALE	DATETIME_PRECISION	CHARACTER_SET_NAME	COLLATION_NAME	DTD_IDENTIFIER	ROUTINE_BODY	ROUTINE_DEFINITION	EXTERNAL_NAME	EXTERNAL_LANGUAGE	PARAMETER_STYLE	IS_DETERMINISTIC	SQL_DATA_ACCESS	SQL_PATH	SECURITY_TYPE	CREATED	LAST_ALTERED	SQL_MODE	ROUTINE_COMMENT	DEFINER	CHARACTER_SET_CLIENT	COLLATION_CONNECTION	DATABASE_COLLATION
test_func5	def	i_s_routines_test	test_func5	FUNCTION	timestamp	NULL	NULL	NULL	NULL	0	NULL	NULL	timestamp	SQL	RETURN CURRENT_TIMESTAMP	NULL	NULL	SQL	NO	CONTAINS SQL	NULL	DEFINER	<created>	<modified>		new comment added	root@localhost	latin1	latin1_swedish_ci	latin1_swedish_ci
# ========== routines.9 ==========
DROP DATABASE IF EXISTS i_s_routines_test;
CREATE DATABASE i_s_routines_test CHARACTER SET  utf8;
USE i_s_routines_test;
CREATE FUNCTION test_func5 (s CHAR(20)) RETURNS VARCHAR(30)
RETURN CONCAT('XYZ, ' ,s);
SELECT * FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_SCHEMA = 'i_s_routines_test' AND ROUTINE_NAME = 'test_func5';
SPECIFIC_NAME	ROUTINE_CATALOG	ROUTINE_SCHEMA	ROUTINE_NAME	ROUTINE_TYPE	DATA_TYPE	CHARACTER_MAXIMUM_LENGTH	CHARACTER_OCTET_LENGTH	NUMERIC_PRECISION	NUMERIC_SCALE	DATETIME_PRECISION	CHARACTER_SET_NAME	COLLATION_NAME	DTD_IDENTIFIER	ROUTINE_BODY	ROUTINE_DEFINITION	EXTERNAL_NAME	EXTERNAL_LANGUAGE	PARAMETER_STYLE	IS_DETERMINISTIC	SQL_DATA_ACCESS	SQL_PATH	SECURITY_TYPE	CREATED	LAST_ALTERED	SQL_MODE	ROUTINE_COMMENT	DEFINER	CHARACTER_SET_CLIENT	COLLATION_CONNECTION	DATABASE_COLLATION
test_func5	def	i_s_routines_test	test_func5	FUNCTION	varchar	30	90	NULL	NULL	NULL	utf8	utf8_general_ci	varchar(30)	SQL	RETURN CONCAT('XYZ, ' ,s)	NULL	NULL	SQL	NO	CONTAINS SQL	NULL	DEFINER	<created>	<modified>			root@localhost	latin1	latin1_swedish_ci	utf8_general_ci
DROP DATABASE i_s_routines_test;