information_schema_routines.test

#------------------------------------------------------------------------------
# i_s_routines.test
# .test file for MySQL regression suite
# Purpose:  To test the presence, structure, and behavior
#                    of INFORMATION_SCHEMA.ROUTINES
# Author:  pcrews
# Last modified:  2007-12-04
#------------------------------------------------------------------------------

################################################################################
# Testcase routines.1: Ensure that the INFORMATION_SCHEMA.ROUTINES
#                   table has the following columns, in the following order:
#
#                   SPECIFIC_NAME (shows the name of an accessible stored
#                          procedure, or routine),
#                   ROUTINE_CATALOG (always shows NULL),
#                   ROUTINE_SCHEMA (shows the database, or schema, in which
#                          the routine resides),
#                   ROUTINE_NAME (shows the same stored procedure name),
#                   ROUTINE_TYPE (shows whether the stored procedure is a
#                          procedure or a function),
#                   DATA_TYPE (new column as of 6.0)
#                   CHARACTER_MAXIMUM_LENGTH  (new column as of 6.0)
#                   CHARACTER_OCTET_LENGTH  (new column as of 6.0)
#                   NUMERIC_PRECISION  (new column as of 6.0)
#                   NUMERIC_SCALE  (new column as of 6.0)
#                   CHARACTER_SET_NAME  (new column as of 6.0)
#                   COLLATION_NAME  (new column as of 6.0)
#                   DTD_IDENTIFIER (shows, for a function, the complete
#                          data type definition of the value the function will
#                          return; otherwise NULL),
#                   ROUTINE_BODY (shows the language in which the stored
#                          procedure is written; currently always SQL),
#                   ROUTINE_DEFINITION (shows as much of the routine body as
#                          is possible in the allotted space),
#                   EXTERNAL_NAME (always shows NULL),
#                   EXTERNAL_LANGUAGE (always shows NULL),
#                   PARAMETER_STYLE (shows the routine's parameter style;
#                          always SQL),
#                   IS_DETERMINISTIC (shows whether the routine is
#                          deterministic),
#                   SQL_DATA_ACCESS (shows the routine's defined
#                          sql-data-access clause value),
#                   SQL_PATH (always shows NULL),
#                   SECURITY_TYPE (shows whether the routine's defined
#                          security_type is 'definer' or 'invoker'),
#                   CREATED (shows the timestamp of the time the routine was
#                          created),
#                   LAST_ALTERED (shows the timestamp of the time the routine
#                          was last altered),
#                   SQL_MODE (shows the sql_mode setting at the time the
#                          routine was created),
#                   ROUTINE_COMMENT (shows the comment, if any, defined for
#                          the routine; otherwise NULL),
#                   DEFINER (shows the user who created the routine).
################################################################################
-- echo # ========== routines.1 ==========
USE INFORMATION_SCHEMA;
--replace_result ENGINE=MyISAM "" ENGINE=MARIA "" " PAGE_CHECKSUM=1" "" " PAGE_CHECKSUM=0" ""
SHOW CREATE TABLE INFORMATION_SCHEMA.ROUTINES;

# embedded server does not display privileges
--replace_column 19 #
query_vertical SELECT * FROM information_schema.columns
WHERE table_schema = 'information_schema'
  AND table_name   = 'routines'
ORDER BY ordinal_position;

DESCRIBE INFORMATION_SCHEMA.ROUTINES;

###############################################################################
# Testcase routines.2:  Unsuccessful stored procedure CREATE will not populate
#                     I_S.ROUTINES view
###############################################################################
-- echo # ========== routines.2 ==========
--disable_warnings ONCE
DROP DATABASE IF EXISTS i_s_routines_test;

CREATE DATABASE i_s_routines_test;
USE i_s_routines_test;

# Missing closing ')' character at the end of 's char(20) in func declaration
--error ER_PARSE_ERROR
CREATE FUNCTION test_func1 (s char(20) RETURNS CHAR(50)
RETURN CONCAT('Hello', ,s,'!');
--replace_column 24 <created> 25 <modified>
SELECT * FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_SCHEMA = 'i_s_routines_test' AND ROUTINE_NAME = 'test_func1';

###############################################################################
# Testcase routines.3:  DROP FUNCTION - Verify DROP of a stored procedure
#                                     removes I_S.PARAMETERS data for that
#                                     function / procedure
###############################################################################
-- echo # ========== routines.3 ==========
--disable_warnings ONCE
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,'!');
--replace_column 24 <created> 25 <modified>
SELECT * FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_SCHEMA = 'i_s_routines_test' AND ROUTINE_NAME = 'test_func1';
DROP FUNCTION test_func1;
--replace_column 24 <created> 25 <modified>
SELECT * FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_SCHEMA = 'i_s_routines_test' AND ROUTINE_NAME = 'test_func1';


################################################################################
# Testcase routines.4:  Verify that the new columns from WL#2822 are NULL for a
#                    stored procedure
################################################################################
-- echo # ========== routines.4 ==========
--disable_warnings ONCE
DROP DATABASE IF EXISTS i_s_routines_test;

CREATE DATABASE i_s_routines_test;
USE i_s_routines_test;

delimiter //;
CREATE PROCEDURE testproc (OUT param1 INT)
  BEGIN
   SELECT 2+2 as param1;
  END;
//
delimiter ;//
--replace_column 24 <created> 25 <modified>
SELECT * FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_SCHEMA = 'i_s_routines_test' AND ROUTINE_NAME = 'testproc';


################################################################################
# Testcase routines.5:  Verify that the new columns from WL#2822 are populated
#                    for a stored function, that the NUMERIC columns
#                    are not populated when the function returns non-numeric
#                    data, and that the CHARACTER columns are populated
#                    for CHAR functions
################################################################################
-- echo # ========== routines.5 ==========
--disable_warnings ONCE
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,'!');
--replace_column 24 <created> 25 <modified>
SELECT * FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_SCHEMA = 'i_s_routines_test' AND ROUTINE_NAME = 'test_func1';



################################################################################
# Testcase routines.6:  Verify that the new columns from WL#2822 are populated
#                    for a stored function, that the CHARACTER columns
#                    are not populated when the function returns numeric
#                    data, and that the NUMERIC columns are populated
#                    for numeric functions
################################################################################
-- echo # ========== routines.6 ==========
--disable_warnings ONCE
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;
--replace_column 24 <created> 25 <modified>
SELECT * FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_SCHEMA = 'i_s_routines_test' AND ROUTINE_NAME = 'test_func2';

################################################################################
# Testcase routines.7:  Verify that the new columns from WL#2822 are populated
#                    for a stored function, that the CHARACTER and NUMERIC
#                    columns are not populated when the function returns date
#                    or time data
################################################################################
-- echo # ========== routines.7 ==========
--disable_warnings ONCE
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;
--replace_column 24 <created> 25 <modified>
SELECT * FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_SCHEMA = 'i_s_routines_test' AND ROUTINE_NAME = 'test_func5';

###############################################################################
# Testcase routines.8:  ALTER FUNCTION
#                     Quick check to ensure ALTER properly updates
#                     I_S.ROUTINES.COMMENT
###############################################################################
-- echo # ========== routines.8 ==========
--disable_warnings ONCE
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;
--replace_column 24 <created> 25 <modified>
SELECT * FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_SCHEMA = 'i_s_routines_test' AND ROUTINE_NAME = 'test_func5';
ALTER FUNCTION test_func5 COMMENT 'new comment added';
--replace_column 24 <created> 25 <modified>
SELECT * FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_SCHEMA = 'i_s_routines_test' AND ROUTINE_NAME = 'test_func5';

###############################################################################
# Testcase routines.9:  MULTI-BYTE CHAR SETS
#                     Verify that CHAR_MAX_LENGTH and CHAR_OCTET_LENGTH
#                     differ as expected for multi-byte char sets
#                     Normally both values are equal
###############################################################################
-- echo # ========== routines.9 ==========
--disable_warnings ONCE
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);
--replace_column 24 <created> 25 <modified>
SELECT * FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_SCHEMA = 'i_s_routines_test' AND ROUTINE_NAME = 'test_func5';

###############################################################################
# Testcase Routine.10: Test case to test DATETIME_PRECISION in 
# information_schema.routines table.
###############################################################################
-- echo # ========== routines.10 ==========
--disable_warnings
DROP DATABASE IF EXISTS i_s_routines_test;
--enable_warnings

CREATE DATABASE i_s_routines_test;
USE i_s_routines_test;

delimiter \\;
CREATE PROCEDURE test_proc1 (OUT p_p1 timestamp) BEGIN SELECT 1 ; END; \\
delimiter ;\\

CREATE FUNCTION test_func_t (f_p1 DATETIME) RETURNS TIME RETURN NULL;
CREATE FUNCTION test_func_dt (f_p1 DATETIME) RETURNS DATETIME RETURN NULL;
CREATE FUNCTION test_func_ts (f_p1 DATETIME) RETURNS TIMESTAMP RETURN NULL;

CREATE FUNCTION test_func_t0 (f_p1 DATETIME) RETURNS TIME(0) RETURN NULL;
CREATE FUNCTION test_func_dt0 (f_p1 DATETIME) RETURNS DATETIME(0) RETURN NULL;
CREATE FUNCTION test_func_ts0 (f_p1 DATETIME) RETURNS TIMESTAMP(0) RETURN NULL;

CREATE FUNCTION test_func_t1 (f_p1 DATETIME) RETURNS TIME(1) RETURN NULL;
CREATE FUNCTION test_func_dt1 (f_p1 DATETIME) RETURNS DATETIME(1) RETURN NULL;
CREATE FUNCTION test_func_ts1 (f_p1 DATETIME) RETURNS TIMESTAMP(1) RETURN NULL;

CREATE FUNCTION test_func_t2 (f_p1 DATETIME) RETURNS TIME(2) RETURN NULL;
CREATE FUNCTION test_func_dt2 (f_p1 DATETIME) RETURNS DATETIME(2) RETURN NULL;
CREATE FUNCTION test_func_ts2 (f_p1 DATETIME) RETURNS TIMESTAMP(2) RETURN NULL;

CREATE FUNCTION test_func_t3 (f_p1 DATETIME) RETURNS TIME(3) RETURN NULL;
CREATE FUNCTION test_func_dt3 (f_p1 DATETIME) RETURNS DATETIME(3) RETURN NULL;
CREATE FUNCTION test_func_ts3 (f_p1 DATETIME) RETURNS TIMESTAMP(3) RETURN NULL;

CREATE FUNCTION test_func_t4 (f_p1 DATETIME) RETURNS TIME(4) RETURN NULL;
CREATE FUNCTION test_func_dt4 (f_p1 DATETIME) RETURNS DATETIME(4) RETURN NULL;
CREATE FUNCTION test_func_ts4 (f_p1 DATETIME) RETURNS TIMESTAMP(4) RETURN NULL;

CREATE FUNCTION test_func_t5 (f_p1 DATETIME) RETURNS TIME(5) RETURN NULL;
CREATE FUNCTION test_func_dt5 (f_p1 DATETIME) RETURNS DATETIME(5) RETURN NULL;
CREATE FUNCTION test_func_ts5 (f_p1 DATETIME) RETURNS TIMESTAMP(5) RETURN NULL;

CREATE FUNCTION test_func_t6 (f_p1 DATETIME) RETURNS TIME(6) RETURN NULL;
CREATE FUNCTION test_func_dt6 (f_p1 DATETIME) RETURNS DATETIME(6) RETURN NULL;
CREATE FUNCTION test_func_ts6 (f_p1 DATETIME) RETURNS TIMESTAMP(6) RETURN NULL;

# Here return type for test_proc1 is not defined so DATETIME_PRECISION for this
# would be NULL. But return type for test_func1 is TIME, so DATETIME_PRECISION
# would be 0.
SELECT ROUTINE_NAME, ROUTINE_TYPE, DATA_TYPE, DATETIME_PRECISION from 
INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA='i_s_routines_test';


# final clean up
DROP DATABASE i_s_routines_test;