diff options
Diffstat (limited to 'mysql-test/suite/funcs_1/r/is_routines.result')
| -rw-r--r-- | mysql-test/suite/funcs_1/r/is_routines.result | 722 |
1 files changed, 722 insertions, 0 deletions
diff --git a/mysql-test/suite/funcs_1/r/is_routines.result b/mysql-test/suite/funcs_1/r/is_routines.result new file mode 100644 index 00000000..51477e44 --- /dev/null +++ b/mysql-test/suite/funcs_1/r/is_routines.result @@ -0,0 +1,722 @@ +set sql_mode=""; +SHOW TABLES FROM information_schema LIKE 'ROUTINES'; +Tables_in_information_schema (ROUTINES) +ROUTINES +####################################################################### +# Testcase 3.2.1.1: INFORMATION_SCHEMA tables can be queried via SELECT +####################################################################### +DROP VIEW IF EXISTS test.v1; +DROP PROCEDURE IF EXISTS test.p1; +DROP FUNCTION IF EXISTS test.f1; +CREATE VIEW test.v1 AS SELECT * FROM information_schema.ROUTINES; +CREATE PROCEDURE test.p1() SELECT * FROM information_schema.ROUTINES; +CREATE FUNCTION test.f1() returns BIGINT +BEGIN +DECLARE counter BIGINT DEFAULT NULL; +SELECT COUNT(*) INTO counter FROM information_schema.ROUTINES; +RETURN counter; +END// +# Attention: The printing of the next result sets is disabled. +SELECT * FROM information_schema.ROUTINES; +SELECT * FROM test.v1; +CALL test.p1; +SELECT test.f1(); +DROP VIEW test.v1; +DROP PROCEDURE test.p1; +DROP FUNCTION test.f1; +######################################################################### +# Testcase 3.2.8.1: INFORMATION_SCHEMA.ROUTINES layout +######################################################################### +DESCRIBE information_schema.ROUTINES; +Field Type Null Key Default Extra +SPECIFIC_NAME varchar(64) NO NULL +ROUTINE_CATALOG varchar(512) NO NULL +ROUTINE_SCHEMA varchar(64) NO NULL +ROUTINE_NAME varchar(64) NO NULL +ROUTINE_TYPE varchar(13) NO 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 YES NULL +ROUTINE_BODY varchar(8) NO NULL +ROUTINE_DEFINITION longtext YES NULL +EXTERNAL_NAME varchar(64) YES NULL +EXTERNAL_LANGUAGE varchar(64) YES NULL +PARAMETER_STYLE varchar(8) NO NULL +IS_DETERMINISTIC varchar(3) NO NULL +SQL_DATA_ACCESS varchar(64) NO NULL +SQL_PATH varchar(64) YES NULL +SECURITY_TYPE varchar(7) NO NULL +CREATED datetime NO NULL +LAST_ALTERED datetime NO NULL +SQL_MODE varchar(8192) NO NULL +ROUTINE_COMMENT longtext NO NULL +DEFINER varchar(384) NO NULL +CHARACTER_SET_CLIENT varchar(32) NO NULL +COLLATION_CONNECTION varchar(64) NO NULL +DATABASE_COLLATION varchar(64) NO NULL +SHOW CREATE TABLE information_schema.ROUTINES; +Table Create Table +ROUTINES CREATE TEMPORARY TABLE `ROUTINES` ( + `SPECIFIC_NAME` varchar(64) NOT NULL, + `ROUTINE_CATALOG` varchar(512) NOT NULL, + `ROUTINE_SCHEMA` varchar(64) NOT NULL, + `ROUTINE_NAME` varchar(64) NOT NULL, + `ROUTINE_TYPE` varchar(13) NOT NULL, + `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, + `ROUTINE_BODY` varchar(8) NOT NULL, + `ROUTINE_DEFINITION` longtext, + `EXTERNAL_NAME` varchar(64), + `EXTERNAL_LANGUAGE` varchar(64), + `PARAMETER_STYLE` varchar(8) NOT NULL, + `IS_DETERMINISTIC` varchar(3) NOT NULL, + `SQL_DATA_ACCESS` varchar(64) NOT NULL, + `SQL_PATH` varchar(64), + `SECURITY_TYPE` varchar(7) NOT NULL, + `CREATED` datetime NOT NULL, + `LAST_ALTERED` datetime NOT NULL, + `SQL_MODE` varchar(8192) NOT NULL, + `ROUTINE_COMMENT` longtext NOT NULL, + `DEFINER` varchar(384) NOT NULL, + `CHARACTER_SET_CLIENT` varchar(32) NOT NULL, + `COLLATION_CONNECTION` varchar(64) NOT NULL, + `DATABASE_COLLATION` varchar(64) NOT NULL +) DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci +SHOW COLUMNS FROM information_schema.ROUTINES; +Field Type Null Key Default Extra +SPECIFIC_NAME varchar(64) NO NULL +ROUTINE_CATALOG varchar(512) NO NULL +ROUTINE_SCHEMA varchar(64) NO NULL +ROUTINE_NAME varchar(64) NO NULL +ROUTINE_TYPE varchar(13) NO 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 YES NULL +ROUTINE_BODY varchar(8) NO NULL +ROUTINE_DEFINITION longtext YES NULL +EXTERNAL_NAME varchar(64) YES NULL +EXTERNAL_LANGUAGE varchar(64) YES NULL +PARAMETER_STYLE varchar(8) NO NULL +IS_DETERMINISTIC varchar(3) NO NULL +SQL_DATA_ACCESS varchar(64) NO NULL +SQL_PATH varchar(64) YES NULL +SECURITY_TYPE varchar(7) NO NULL +CREATED datetime NO NULL +LAST_ALTERED datetime NO NULL +SQL_MODE varchar(8192) NO NULL +ROUTINE_COMMENT longtext NO NULL +DEFINER varchar(384) NO NULL +CHARACTER_SET_CLIENT varchar(32) NO NULL +COLLATION_CONNECTION varchar(64) NO NULL +DATABASE_COLLATION varchar(64) NO NULL +USE test; +DROP PROCEDURE IF EXISTS sp_for_routines; +DROP FUNCTION IF EXISTS function_for_routines; +CREATE PROCEDURE sp_for_routines() SELECT 'db_datadict'; +CREATE FUNCTION function_for_routines() RETURNS INT RETURN 0; +SELECT specific_name,routine_catalog,routine_schema,routine_name,routine_type, +routine_body,external_name,external_language,parameter_style,sql_path +FROM information_schema.routines +WHERE routine_schema = 'test' AND +(routine_catalog IS NOT NULL OR external_name IS NOT NULL +OR external_language IS NOT NULL OR sql_path IS NOT NULL +OR routine_body <> 'SQL' OR parameter_style <> 'SQL' + OR specific_name <> routine_name); +specific_name routine_catalog routine_schema routine_name routine_type routine_body external_name external_language parameter_style sql_path +function_for_routines def test function_for_routines FUNCTION SQL NULL NULL SQL NULL +sp_for_routines def test sp_for_routines PROCEDURE SQL NULL NULL SQL NULL +DROP PROCEDURE sp_for_routines; +DROP FUNCTION function_for_routines; +################################################################################ +# Testcase 3.2.8.2 + 3.2.8.3: INFORMATION_SCHEMA.ROUTINES accessible information +################################################################################ +DROP DATABASE IF EXISTS db_datadict; +DROP DATABASE IF EXISTS db_datadict_2; +CREATE DATABASE db_datadict; +USE db_datadict; +CREATE TABLE res_6_408002_1(f1 CHAR(3), f2 TEXT(25), f3 DATE, f4 INT) +ENGINE = <other_engine_type>; +INSERT INTO res_6_408002_1(f1, f2, f3, f4) +VALUES('abc', 'xyz', '1989-11-09', 0815); +DROP PROCEDURE IF EXISTS sp_6_408002_1; +CREATE PROCEDURE sp_6_408002_1() +BEGIN +SELECT * FROM db_datadict.res_6_408002_1; +END// +CREATE DATABASE db_datadict_2; +USE db_datadict_2; +CREATE TABLE res_6_408002_2(f1 CHAR(3), f2 TEXT(25), f3 DATE, f4 INT) +ENGINE = <other_engine_type>; +INSERT INTO res_6_408002_2(f1, f2, f3, f4) +VALUES('abc', 'xyz', '1990-10-03', 4711); +DROP PROCEDURE IF EXISTS sp_6_408002_2; +CREATE PROCEDURE sp_6_408002_2() +BEGIN +SELECT * FROM db_datadict_2.res_6_408002_2; +END// +DROP USER 'testuser1'@'localhost'; +CREATE USER 'testuser1'@'localhost'; +DROP USER 'testuser2'@'localhost'; +CREATE USER 'testuser2'@'localhost'; +DROP USER 'testuser3'@'localhost'; +CREATE USER 'testuser3'@'localhost'; +GRANT SELECT ON db_datadict_2.* TO 'testuser1'@'localhost'; +GRANT EXECUTE ON db_datadict_2.* TO 'testuser1'@'localhost'; +GRANT EXECUTE ON db_datadict.* TO 'testuser1'@'localhost'; +GRANT SELECT ON db_datadict.* TO 'testuser2'@'localhost'; +GRANT EXECUTE ON PROCEDURE db_datadict_2.sp_6_408002_2 +TO 'testuser2'@'localhost'; +GRANT EXECUTE ON db_datadict_2.* TO 'testuser2'@'localhost'; +FLUSH PRIVILEGES; +connect testuser1, localhost, testuser1, , db_datadict; +SELECT * FROM information_schema.routines where routine_schema <> 'sys'; +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 +sp_6_408002_1 def db_datadict sp_6_408002_1 PROCEDURE NULL NULL NULL NULL NULL NULL NULL NULL SQL NULL NULL NULL SQL NO CONTAINS SQL NULL DEFINER YYYY-MM-DD hh:mm:ss YYYY-MM-DD hh:mm:ss root@localhost latin1 latin1_swedish_ci latin1_swedish_ci +sp_6_408002_2 def db_datadict_2 sp_6_408002_2 PROCEDURE NULL NULL NULL NULL NULL NULL NULL NULL SQL NULL NULL NULL SQL NO CONTAINS SQL NULL DEFINER YYYY-MM-DD hh:mm:ss YYYY-MM-DD hh:mm:ss root@localhost latin1 latin1_swedish_ci latin1_swedish_ci +connect testuser2, localhost, testuser2, , db_datadict; +SELECT * FROM information_schema.routines where routine_schema <> 'sys'; +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 +sp_6_408002_2 def db_datadict_2 sp_6_408002_2 PROCEDURE NULL NULL NULL NULL NULL NULL NULL NULL SQL NULL NULL NULL SQL NO CONTAINS SQL NULL DEFINER YYYY-MM-DD hh:mm:ss YYYY-MM-DD hh:mm:ss root@localhost latin1 latin1_swedish_ci latin1_swedish_ci +connect testuser3, localhost, testuser3, , "*NO-ONE*"; +SELECT * FROM information_schema.routines where routine_schema <> 'sys'; +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 +connection default; +disconnect testuser1; +disconnect testuser2; +disconnect testuser3; +DROP USER 'testuser1'@'localhost'; +DROP USER 'testuser2'@'localhost'; +DROP USER 'testuser3'@'localhost'; +USE test; +DROP DATABASE db_datadict; +DROP DATABASE db_datadict_2; +######################################################################### +# 3.2.1.13+3.2.1.14+3.2.1.15: INFORMATION_SCHEMA.ROUTINES modifications +######################################################################### +DROP DATABASE IF EXISTS db_datadict; +CREATE DATABASE db_datadict; +SELECT * FROM information_schema.routines WHERE routine_schema = 'db_datadict'; +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 +USE db_datadict; +CREATE PROCEDURE sp_for_routines() SELECT 'db_datadict'; +CREATE FUNCTION function_for_routines() RETURNS INT RETURN 0; +SELECT * FROM information_schema.routines WHERE routine_schema = 'db_datadict' +ORDER BY routine_name; +SPECIFIC_NAME function_for_routines +ROUTINE_CATALOG def +ROUTINE_SCHEMA db_datadict +ROUTINE_NAME function_for_routines +ROUTINE_TYPE FUNCTION +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_BODY SQL +ROUTINE_DEFINITION RETURN 0 +EXTERNAL_NAME NULL +EXTERNAL_LANGUAGE NULL +PARAMETER_STYLE SQL +IS_DETERMINISTIC NO +SQL_DATA_ACCESS CONTAINS SQL +SQL_PATH NULL +SECURITY_TYPE DEFINER +CREATED <created> +LAST_ALTERED <modified> +SQL_MODE +ROUTINE_COMMENT +DEFINER root@localhost +CHARACTER_SET_CLIENT latin1 +COLLATION_CONNECTION latin1_swedish_ci +DATABASE_COLLATION latin1_swedish_ci +SPECIFIC_NAME sp_for_routines +ROUTINE_CATALOG def +ROUTINE_SCHEMA db_datadict +ROUTINE_NAME sp_for_routines +ROUTINE_TYPE PROCEDURE +DATA_TYPE +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 NULL +ROUTINE_BODY SQL +ROUTINE_DEFINITION SELECT 'db_datadict' +EXTERNAL_NAME NULL +EXTERNAL_LANGUAGE NULL +PARAMETER_STYLE SQL +IS_DETERMINISTIC NO +SQL_DATA_ACCESS CONTAINS SQL +SQL_PATH NULL +SECURITY_TYPE DEFINER +CREATED <created> +LAST_ALTERED <modified> +SQL_MODE +ROUTINE_COMMENT +DEFINER root@localhost +CHARACTER_SET_CLIENT latin1 +COLLATION_CONNECTION latin1_swedish_ci +DATABASE_COLLATION latin1_swedish_ci +ALTER PROCEDURE sp_for_routines SQL SECURITY INVOKER; +ALTER FUNCTION function_for_routines COMMENT 'updated comments'; +SELECT * FROM information_schema.routines WHERE routine_schema = 'db_datadict' +ORDER BY routine_name; +SPECIFIC_NAME function_for_routines +ROUTINE_CATALOG def +ROUTINE_SCHEMA db_datadict +ROUTINE_NAME function_for_routines +ROUTINE_TYPE FUNCTION +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_BODY SQL +ROUTINE_DEFINITION RETURN 0 +EXTERNAL_NAME NULL +EXTERNAL_LANGUAGE NULL +PARAMETER_STYLE SQL +IS_DETERMINISTIC NO +SQL_DATA_ACCESS CONTAINS SQL +SQL_PATH NULL +SECURITY_TYPE DEFINER +CREATED <created> +LAST_ALTERED <modified> +SQL_MODE +ROUTINE_COMMENT updated comments +DEFINER root@localhost +CHARACTER_SET_CLIENT latin1 +COLLATION_CONNECTION latin1_swedish_ci +DATABASE_COLLATION latin1_swedish_ci +SPECIFIC_NAME sp_for_routines +ROUTINE_CATALOG def +ROUTINE_SCHEMA db_datadict +ROUTINE_NAME sp_for_routines +ROUTINE_TYPE PROCEDURE +DATA_TYPE +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 NULL +ROUTINE_BODY SQL +ROUTINE_DEFINITION SELECT 'db_datadict' +EXTERNAL_NAME NULL +EXTERNAL_LANGUAGE NULL +PARAMETER_STYLE SQL +IS_DETERMINISTIC NO +SQL_DATA_ACCESS CONTAINS SQL +SQL_PATH NULL +SECURITY_TYPE INVOKER +CREATED <created> +LAST_ALTERED <modified> +SQL_MODE +ROUTINE_COMMENT +DEFINER root@localhost +CHARACTER_SET_CLIENT latin1 +COLLATION_CONNECTION latin1_swedish_ci +DATABASE_COLLATION latin1_swedish_ci +DROP PROCEDURE sp_for_routines; +DROP FUNCTION function_for_routines; +SELECT * FROM information_schema.routines WHERE routine_schema = 'db_datadict'; +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 +CREATE PROCEDURE sp_for_routines() SELECT 'db_datadict'; +CREATE FUNCTION function_for_routines() RETURNS INT RETURN 0; +SELECT * FROM information_schema.routines WHERE routine_schema = 'db_datadict' +ORDER BY routine_name; +SPECIFIC_NAME function_for_routines +ROUTINE_CATALOG def +ROUTINE_SCHEMA db_datadict +ROUTINE_NAME function_for_routines +ROUTINE_TYPE FUNCTION +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_BODY SQL +ROUTINE_DEFINITION RETURN 0 +EXTERNAL_NAME NULL +EXTERNAL_LANGUAGE NULL +PARAMETER_STYLE SQL +IS_DETERMINISTIC NO +SQL_DATA_ACCESS CONTAINS SQL +SQL_PATH NULL +SECURITY_TYPE DEFINER +CREATED <created> +LAST_ALTERED <modified> +SQL_MODE +ROUTINE_COMMENT +DEFINER root@localhost +CHARACTER_SET_CLIENT latin1 +COLLATION_CONNECTION latin1_swedish_ci +DATABASE_COLLATION latin1_swedish_ci +SPECIFIC_NAME sp_for_routines +ROUTINE_CATALOG def +ROUTINE_SCHEMA db_datadict +ROUTINE_NAME sp_for_routines +ROUTINE_TYPE PROCEDURE +DATA_TYPE +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 NULL +ROUTINE_BODY SQL +ROUTINE_DEFINITION SELECT 'db_datadict' +EXTERNAL_NAME NULL +EXTERNAL_LANGUAGE NULL +PARAMETER_STYLE SQL +IS_DETERMINISTIC NO +SQL_DATA_ACCESS CONTAINS SQL +SQL_PATH NULL +SECURITY_TYPE DEFINER +CREATED <created> +LAST_ALTERED <modified> +SQL_MODE +ROUTINE_COMMENT +DEFINER root@localhost +CHARACTER_SET_CLIENT latin1 +COLLATION_CONNECTION latin1_swedish_ci +DATABASE_COLLATION latin1_swedish_ci +use test; +DROP DATABASE db_datadict; +SELECT * FROM information_schema.routines WHERE routine_schema = 'db_datadict'; +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 +######################################################################### +# 3.2.8.4: INFORMATION_SCHEMA.ROUTINES routine body too big for +# ROUTINE_DEFINITION column +######################################################################### +DROP DATABASE IF EXISTS db_datadict; +CREATE DATABASE db_datadict; +USE db_datadict; +CREATE TABLE db_datadict.res_6_408004_1 +(f1 LONGTEXT , f2 MEDIUMINT , f3 LONGBLOB , f4 REAL , f5 YEAR) +ENGINE = <other_engine_type>; +INSERT INTO db_datadict.res_6_408004_1 +VALUES ('abc', 98765 , 99999999 , 98765, 10); +CREATE TABLE db_datadict.res_6_408004_2 +(f1 LONGTEXT , f2 MEDIUMINT , f3 LONGBLOB , f4 REAL , f5 YEAR) +ENGINE = <other_engine_type>; +INSERT INTO db_datadict.res_6_408004_2 +VALUES ('abc', 98765 , 99999999 , 98765, 10); +# Checking the max. possible length of (currently) 4 GByte is not +# in this environment here. +CREATE PROCEDURE sp_6_408004 () +BEGIN +DECLARE done INTEGER DEFAULt 0; +DECLARE variable_number_1 LONGTEXT; +DECLARE variable_number_2 MEDIUMINT; +DECLARE variable_number_3 LONGBLOB; +DECLARE variable_number_4 REAL; +DECLARE variable_number_5 YEAR; +DECLARE cursor_number_1 CURSOR FOR SELECT * FROM res_6_408004_1 LIMIT 0, 10; +DECLARE cursor_number_2 CURSOR FOR SELECT * FROM res_6_408004_1 LIMIT 0, 10; +DECLARE cursor_number_3 CURSOR FOR SELECT * FROM res_6_408004_1 LIMIT 0, 10; +DECLARE cursor_number_4 CURSOR FOR SELECT * FROM res_6_408004_1 LIMIT 0, 10; +DECLARE cursor_number_5 CURSOR FOR SELECT * FROM res_6_408004_1 LIMIT 0, 10; +DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1; +BEGIN +OPEN cursor_number_1; +WHILE done <> 1 DO +FETCH cursor_number_1 +INTO variable_number_1, variable_number_2, variable_number_3, +variable_number_4, variable_number_5; +IF done <> 0 THEN +INSERT INTO res_6_408004_2 +VALUES (variable_number_1, variable_number_2, variable_number_3, +variable_number_4, variable_number_5); +END IF; +END WHILE; +BEGIN +BEGIN +SET done = 0; +OPEN cursor_number_2; +WHILE done <> 1 DO +FETCH cursor_number_2 +INTO variable_number_1, variable_number_2, variable_number_3, +variable_number_4, variable_number_5; +IF done <> 0 THEN +INSERT INTO res_6_408004_2 +VALUES(variable_number_1, variable_number_2, variable_number_3, +variable_number_4, variable_number_5); +END IF; +END WHILE; +END; +SET done = 0; +OPEN cursor_number_3; +WHILE done <> 1 DO +FETCH cursor_number_3 +INTO variable_number_1, variable_number_2, variable_number_3, +variable_number_4, variable_number_5; +IF done <> 0 THEN +INSERT INTO res_6_408004_2 +VALUES(variable_number_1, variable_number_2, variable_number_3, +variable_number_4, variable_number_5); +END IF; +END WHILE; +END; +END; +BEGIN +SET done = 0; +OPEN cursor_number_4; +WHILE done <> 1 DO +FETCH cursor_number_4 +INTO variable_number_1, variable_number_2, variable_number_3, +variable_number_4, variable_number_5; +IF done <> 0 THEN +INSERT INTO res_6_408004_2 +VALUES (variable_number_1, variable_number_2, variable_number_3, +variable_number_4, variable_number_5); +END IF; +END WHILE; +END; +BEGIN +SET @a='test row'; +SELECT @a; +SELECT @a; +SELECT @a; +END; +BEGIN +SET done = 0; +OPEN cursor_number_5; +WHILE done <> 1 DO +FETCH cursor_number_5 +INTO variable_number_1, variable_number_2, variable_number_3, +variable_number_4, variable_number_5; +IF done <> 0 THEN +INSERT INTO res_6_408004_2 +VALUES (variable_number_1, variable_number_2, variable_number_3, +variable_number_4, variable_number_5); +END IF; +END WHILE; +END; +BEGIN +SET @a='test row'; +SELECT @a; +SELECT @a; +SELECT @a; +END; +END// +CALL db_datadict.sp_6_408004 (); +@a +test row +@a +test row +@a +test row +@a +test row +@a +test row +@a +test row +SELECT * FROM db_datadict.res_6_408004_2; +f1 f2 f3 f4 f5 +abc 98765 99999999 98765 2010 +abc 98765 99999999 98765 2010 +abc 98765 99999999 98765 2010 +abc 98765 99999999 98765 2010 +abc 98765 99999999 98765 2010 +abc 98765 99999999 98765 2010 +SELECT *, LENGTH(routine_definition) FROM information_schema.routines +WHERE routine_schema = 'db_datadict'; +SPECIFIC_NAME sp_6_408004 +ROUTINE_CATALOG def +ROUTINE_SCHEMA db_datadict +ROUTINE_NAME sp_6_408004 +ROUTINE_TYPE PROCEDURE +DATA_TYPE +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 NULL +ROUTINE_BODY SQL +ROUTINE_DEFINITION BEGIN +DECLARE done INTEGER DEFAULt 0; +DECLARE variable_number_1 LONGTEXT; +DECLARE variable_number_2 MEDIUMINT; +DECLARE variable_number_3 LONGBLOB; +DECLARE variable_number_4 REAL; +DECLARE variable_number_5 YEAR; +DECLARE cursor_number_1 CURSOR FOR SELECT * FROM res_6_408004_1 LIMIT 0, 10; +DECLARE cursor_number_2 CURSOR FOR SELECT * FROM res_6_408004_1 LIMIT 0, 10; +DECLARE cursor_number_3 CURSOR FOR SELECT * FROM res_6_408004_1 LIMIT 0, 10; +DECLARE cursor_number_4 CURSOR FOR SELECT * FROM res_6_408004_1 LIMIT 0, 10; +DECLARE cursor_number_5 CURSOR FOR SELECT * FROM res_6_408004_1 LIMIT 0, 10; +DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1; +BEGIN +OPEN cursor_number_1; +WHILE done <> 1 DO +FETCH cursor_number_1 +INTO variable_number_1, variable_number_2, variable_number_3, +variable_number_4, variable_number_5; +IF done <> 0 THEN +INSERT INTO res_6_408004_2 +VALUES (variable_number_1, variable_number_2, variable_number_3, +variable_number_4, variable_number_5); +END IF; +END WHILE; +BEGIN +BEGIN +SET done = 0; +OPEN cursor_number_2; +WHILE done <> 1 DO +FETCH cursor_number_2 +INTO variable_number_1, variable_number_2, variable_number_3, +variable_number_4, variable_number_5; +IF done <> 0 THEN +INSERT INTO res_6_408004_2 +VALUES(variable_number_1, variable_number_2, variable_number_3, +variable_number_4, variable_number_5); +END IF; +END WHILE; +END; +SET done = 0; +OPEN cursor_number_3; +WHILE done <> 1 DO +FETCH cursor_number_3 +INTO variable_number_1, variable_number_2, variable_number_3, +variable_number_4, variable_number_5; +IF done <> 0 THEN +INSERT INTO res_6_408004_2 +VALUES(variable_number_1, variable_number_2, variable_number_3, +variable_number_4, variable_number_5); +END IF; +END WHILE; +END; +END; +BEGIN +SET done = 0; +OPEN cursor_number_4; +WHILE done <> 1 DO +FETCH cursor_number_4 +INTO variable_number_1, variable_number_2, variable_number_3, +variable_number_4, variable_number_5; +IF done <> 0 THEN +INSERT INTO res_6_408004_2 +VALUES (variable_number_1, variable_number_2, variable_number_3, +variable_number_4, variable_number_5); +END IF; +END WHILE; +END; +BEGIN +SET @a='test row'; +SELECT @a; +SELECT @a; +SELECT @a; +END; +BEGIN +SET done = 0; +OPEN cursor_number_5; +WHILE done <> 1 DO +FETCH cursor_number_5 +INTO variable_number_1, variable_number_2, variable_number_3, +variable_number_4, variable_number_5; +IF done <> 0 THEN +INSERT INTO res_6_408004_2 +VALUES (variable_number_1, variable_number_2, variable_number_3, +variable_number_4, variable_number_5); +END IF; +END WHILE; +END; +BEGIN +SET @a='test row'; +SELECT @a; +SELECT @a; +SELECT @a; +END; +END +EXTERNAL_NAME NULL +EXTERNAL_LANGUAGE NULL +PARAMETER_STYLE SQL +IS_DETERMINISTIC NO +SQL_DATA_ACCESS CONTAINS SQL +SQL_PATH NULL +SECURITY_TYPE DEFINER +CREATED YYYY-MM-DD hh:mm:ss +LAST_ALTERED YYYY-MM-DD hh:mm:ss +SQL_MODE +ROUTINE_COMMENT +DEFINER root@localhost +CHARACTER_SET_CLIENT latin1 +COLLATION_CONNECTION latin1_swedish_ci +DATABASE_COLLATION latin1_swedish_ci +LENGTH(routine_definition) 2549 +DROP DATABASE db_datadict; +######################################################################## +# Testcases 3.2.1.3-3.2.1.5 + 3.2.1.8-3.2.1.12: INSERT/UPDATE/DELETE and +# DDL on INFORMATION_SCHEMA table are not supported +######################################################################## +DROP DATABASE IF EXISTS db_datadict; +CREATE DATABASE db_datadict; +USE db_datadict; +CREATE PROCEDURE sp_for_routines() SELECT 'db_datadict'; +USE test; +INSERT INTO information_schema.routines (routine_name, routine_type ) +VALUES ('p2', 'procedure'); +ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema' +UPDATE information_schema.routines SET routine_name = 'p2' +WHERE routine_body = 'sql'; +ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema' +DELETE FROM information_schema.routines ; +ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema' +TRUNCATE information_schema.routines ; +ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema' +CREATE INDEX i7 ON information_schema.routines (routine_name); +ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema' +ALTER TABLE information_schema.routines ADD f1 INT; +ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema' +ALTER TABLE information_schema.routines DISCARD TABLESPACE; +ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema' +DROP TABLE information_schema.routines ; +ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema' +ALTER TABLE information_schema.routines RENAME db_datadict.routines; +ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema' +ALTER TABLE information_schema.routines RENAME information_schema.xroutines; +ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema' +DROP DATABASE db_datadict; |