275 lines
13 KiB
Plaintext
275 lines
13 KiB
Plaintext
# Result differences depending on FS case sensitivity.
|
|
if (!$require_case_insensitive_file_system)
|
|
{
|
|
--source include/have_case_sensitive_file_system.inc
|
|
}
|
|
|
|
#------------------------------------------------------------------------------
|
|
# i_s_parameters.test
|
|
# .test file for MySQL regression suite
|
|
# Purpose: To test the presence, structure, and behavior
|
|
# of INFORMATION_SCHEMA.PARAMETERS
|
|
# Author: pcrews
|
|
# Last modified: 2007-12-03
|
|
#------------------------------------------------------------------------------
|
|
|
|
###############################################################################
|
|
# Testcase parameters.1: Verify INFORMATION_SCHEMA.PARAMETERS view has the
|
|
# following structure:
|
|
# SPECIFIC_CATALOG NULL
|
|
# SPECIFIC_SCHEMA routine's database
|
|
# SPECIFIC_NAME routine's name
|
|
# ORDINAL_POSITION first stored routine parameter is 1,
|
|
# always 0 for stored function RETURN
|
|
# PARAMETER_MODE 'IN' or 'OUT' or 'INOUT'
|
|
# PARAMETER_NAME the parameter's name
|
|
# DATA_TYPE same as for COLUMNS
|
|
# CHARACTER_MAXIMUM_LENGTH same as for COLUMNS
|
|
# CHARACTER_OCTET_LENGTH same as for COLUMNS
|
|
# CHARACTER_SET_NAME same as for COLUMNS
|
|
# COLLATION_NAME same as for COLUMNS
|
|
# NUMERIC_PRECISION same as for COLUMNS
|
|
# NUMERIC_SCALE same as for COLUMNS
|
|
# DTD_IDENTIFIER same as for PARAMETERS
|
|
###############################################################################
|
|
-- echo # ========== parameters.1 ==========
|
|
USE INFORMATION_SCHEMA;
|
|
--replace_result ENGINE=MyISAM "" ENGINE=MARIA "" " PAGE_CHECKSUM=1" "" " PAGE_CHECKSUM=0" ""
|
|
--replace_result InnoDB TMP_TABLE_ENGINE MyISAM TMP_TABLE_ENGINE
|
|
SHOW CREATE TABLE INFORMATION_SCHEMA.PARAMETERS;
|
|
|
|
# The below query results in two difference collations like
|
|
# 'utf8_tolower_ci' or 'utf8_bin' depending on l_c_t_n for
|
|
# 'mysql.schemata.name' column. In order to get consistent
|
|
# results across platforms, we replace the results below.
|
|
--replace_result utf8_tolower_ci utf8_bin
|
|
query_vertical SELECT * FROM information_schema.columns
|
|
WHERE table_schema = 'information_schema'
|
|
AND table_name = 'PARAMETERS'
|
|
ORDER BY ordinal_position;
|
|
|
|
DESCRIBE INFORMATION_SCHEMA.PARAMETERS;
|
|
|
|
###############################################################################
|
|
# Testcase parameters.2: Unsuccessful stored procedure CREATE will not populate
|
|
# I_S.PARAMETERS view
|
|
###############################################################################
|
|
-- echo # ========== parameters.2 ==========
|
|
CREATE DATABASE i_s_parameters_test;
|
|
USE i_s_parameters_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,'!');
|
|
SELECT * FROM INFORMATION_SCHEMA.PARAMETERS
|
|
WHERE SPECIFIC_SCHEMA = 'i_s_parameters_test' AND SPECIFIC_NAME = 'test_func1';
|
|
|
|
###############################################################################
|
|
# Testcase parameters.3: DROP FUNCTION - Verify DROP of a stored procedure
|
|
# removes I_S.PARAMETERS data for that
|
|
# function / procedure
|
|
###############################################################################
|
|
-- echo # ========== 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';
|
|
DROP FUNCTION test_func1;
|
|
SELECT * FROM INFORMATION_SCHEMA.PARAMETERS
|
|
WHERE SPECIFIC_SCHEMA = 'i_s_parameters_test' AND SPECIFIC_NAME = 'test_func1';
|
|
|
|
###############################################################################
|
|
# Testcase parameters.4: CREATE PROCEDURE - IN
|
|
###############################################################################
|
|
-- echo # ========== parameters.4 ==========
|
|
DROP DATABASE IF EXISTS i_s_parameters_test;
|
|
|
|
CREATE DATABASE i_s_parameters_test;
|
|
USE i_s_parameters_test;
|
|
|
|
delimiter //;
|
|
CREATE PROCEDURE testproc (OUT param1 INT)
|
|
BEGIN
|
|
SELECT 2+2 as param1;
|
|
END;
|
|
//
|
|
delimiter ;//
|
|
SELECT * FROM INFORMATION_SCHEMA.PARAMETERS
|
|
WHERE SPECIFIC_SCHEMA = 'i_s_parameters_test' AND SPECIFIC_NAME = 'testproc';
|
|
|
|
|
|
###############################################################################
|
|
# Testcase parameters.5: CREATE PROCEDURE - INOUT
|
|
###############################################################################
|
|
-- echo # ========== 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';
|
|
|
|
###############################################################################
|
|
# Testcase parameters.6: CREATE PROCEDURE - OUT
|
|
###############################################################################
|
|
-- echo # ========== 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';
|
|
|
|
###############################################################################
|
|
# Testcase parameters.7: CREATE FUNCTION - ORDINAL POSITION
|
|
# Verify proper behavior for several aspects here
|
|
# 3 rows should be created -- 1 for each IN parameter
|
|
# 1 for the RETURNS param
|
|
# ORDINAL POSITION values should be 0 for RETURNS
|
|
# 1 and 2 for IN parameters
|
|
# PARAM NAME and MODE should = NULL for RETURNS parm
|
|
###############################################################################
|
|
-- echo # ========== 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';
|
|
|
|
###############################################################################
|
|
# Testcase parameters.8: CREATE FUNCTION - CHAR parameters
|
|
# Verify CHAR related columns are populated for such a
|
|
# parameter -- NUMERIC columns should be NULL
|
|
###############################################################################
|
|
-- echo # ========== 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';
|
|
|
|
###############################################################################
|
|
# Testcase parameters.9: CREATE FUNCTION - NUMERIC parameters
|
|
# Verify NUMERIC related columns are populated for such
|
|
# parameter -- CHAR columns should be NULL
|
|
###############################################################################
|
|
-- echo # ========== 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';
|
|
|
|
###############################################################################
|
|
# Testcase parameters.10: CREATE FUNCTION - DATE
|
|
# Verify NUMERIC and CHAR related columns are NULL
|
|
###############################################################################
|
|
-- echo # ========== 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';
|
|
|
|
###############################################################################
|
|
# Testcase parameters.11: ALTER FUNCTION
|
|
# Quick check to ensure ALTER doesn't affect this view
|
|
# Should have no effect -- comment visible in ROUTINES
|
|
# tested in i_s_routines.test
|
|
###############################################################################
|
|
-- echo # ========== 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';
|
|
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';
|
|
|
|
###############################################################################
|
|
# Testcase parameters.12: 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 # ========== 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';
|
|
|
|
###############################################################################
|
|
# Testcase parameters.13: Test case to test DATETIME_PRECISION in
|
|
# information_schema.parameters table.
|
|
###############################################################################
|
|
-- echo # ========== parameters.13 ==========
|
|
--disable_warnings
|
|
DROP DATABASE IF EXISTS i_s_parameters_test;
|
|
--enable_warnings
|
|
|
|
CREATE DATABASE i_s_parameters_test;
|
|
USE i_s_parameters_test;
|
|
|
|
delimiter \\;
|
|
CREATE PROCEDURE test_proc (OUT p_p1 datetime,OUT p_p2 time,OUT p_p3 timestamp) BEGIN SELECT 1 ; END; \\
|
|
CREATE PROCEDURE test_proc0 (OUT p_p1 datetime(0),OUT p_p2 time(0),OUT p_p3 timestamp(0)) BEGIN SELECT 1 ; END; \\
|
|
CREATE PROCEDURE test_proc1 (OUT p_p1 datetime(1),OUT p_p2 time(1),OUT p_p3 timestamp(1)) BEGIN SELECT 1 ; END; \\
|
|
CREATE PROCEDURE test_proc2 (OUT p_p1 datetime(2),OUT p_p2 time(2),OUT p_p3 timestamp(2)) BEGIN SELECT 1 ; END; \\
|
|
CREATE PROCEDURE test_proc3 (OUT p_p1 datetime(3),OUT p_p2 time(3),OUT p_p3 timestamp(3)) BEGIN SELECT 1 ; END; \\
|
|
CREATE PROCEDURE test_proc4 (OUT p_p1 datetime(4),OUT p_p2 time(4),OUT p_p3 timestamp(4)) BEGIN SELECT 1 ; END; \\
|
|
CREATE PROCEDURE test_proc5 (OUT p_p1 datetime(5),OUT p_p2 time(5),OUT p_p3 timestamp(5)) BEGIN SELECT 1 ; END; \\
|
|
CREATE PROCEDURE test_proc6 (OUT p_p1 datetime(6),OUT p_p2 time(6),OUT p_p3 timestamp(6)) BEGIN SELECT 1 ; END; \\
|
|
delimiter ;\\
|
|
|
|
CREATE FUNCTION test_func (f_p1 DATETIME, f_p2 TIMESTAMP) RETURNS TIME RETURN NULL;
|
|
CREATE FUNCTION test_func0 (f_p1 DATETIME(0), f_p2 TIMESTAMP(0)) RETURNS TIME(0) RETURN NULL;
|
|
CREATE FUNCTION test_func1 (f_p1 DATETIME(1), f_p2 TIMESTAMP(1)) RETURNS TIME(1) RETURN NULL;
|
|
CREATE FUNCTION test_func2 (f_p1 DATETIME(2), f_p2 TIMESTAMP(2)) RETURNS TIME(2) RETURN NULL;
|
|
CREATE FUNCTION test_func3 (f_p1 DATETIME(3), f_p2 TIMESTAMP(3)) RETURNS TIME(3) RETURN NULL;
|
|
CREATE FUNCTION test_func4 (f_p1 DATETIME(4), f_p2 TIMESTAMP(4)) RETURNS TIME(4) RETURN NULL;
|
|
CREATE FUNCTION test_func5 (f_p1 DATETIME(5), f_p2 TIMESTAMP(5)) RETURNS TIME(5) RETURN NULL;
|
|
CREATE FUNCTION test_func6 (f_p1 DATETIME(6), f_p2 TIMESTAMP(6)) RETURNS TIME(6) RETURN NULL;
|
|
|
|
SELECT SPECIFIC_SCHEMA, SPECIFIC_NAME, PARAMETER_NAME, DATA_TYPE,
|
|
DATETIME_PRECISION from INFORMATION_SCHEMA.PARAMETERS WHERE
|
|
SPECIFIC_SCHEMA='i_s_parameters_test' ORDER BY SPECIFIC_NAME,PARAMETER_NAME;
|
|
|
|
|
|
# Cleanup
|
|
DROP DATABASE i_s_parameters_test;
|