mirror of
https://github.com/MariaDB/server.git
synced 2025-10-31 02:46:29 +01:00
350 lines
14 KiB
Text
350 lines
14 KiB
Text
#------------------------------------------------------------------------------
|
||
# 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).
|
||
################################################################################
|
||
set sql_mode="";
|
||
set sql_mode="";
|
||
--source include/default_charset.inc
|
||
|
||
-- echo # ========== routines.1 ==========
|
||
USE INFORMATION_SCHEMA;
|
||
--replace_result ENGINE=MyISAM "" ENGINE=MARIA "" ENGINE=Aria "" " 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
|
||
DROP DATABASE IF EXISTS i_s_routines_test;
|
||
--enable_warnings
|
||
|
||
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
|
||
DROP DATABASE IF EXISTS i_s_routines_test;
|
||
--enable_warnings
|
||
|
||
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
|
||
DROP DATABASE IF EXISTS i_s_routines_test;
|
||
--enable_warnings
|
||
|
||
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
|
||
DROP DATABASE IF EXISTS i_s_routines_test;
|
||
--enable_warnings
|
||
|
||
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
|
||
DROP DATABASE IF EXISTS i_s_routines_test;
|
||
--enable_warnings
|
||
|
||
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
|
||
DROP DATABASE IF EXISTS i_s_routines_test;
|
||
--enable_warnings
|
||
|
||
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
|
||
DROP DATABASE IF EXISTS i_s_routines_test;
|
||
--enable_warnings
|
||
|
||
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
|
||
DROP DATABASE IF EXISTS i_s_routines_test;
|
||
--enable_warnings
|
||
|
||
|
||
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';
|
||
|
||
# final clean up
|
||
DROP DATABASE i_s_routines_test;
|
||
|
||
--echo #
|
||
--echo # MDEV-20609 Full table scan in INFORMATION_SCHEMA.PARAMETERS/ROUTINES
|
||
--echo #
|
||
--disable_warnings
|
||
DROP DATABASE IF EXISTS i_s_routines_test;
|
||
--enable_warnings
|
||
|
||
CREATE DATABASE i_s_routines_test;
|
||
USE i_s_routines_test;
|
||
|
||
CREATE FUNCTION test_func5 (s CHAR(20)) RETURNS VARCHAR(30)
|
||
RETURN CONCAT('XYZ, ' ,s);
|
||
|
||
|
||
--let count_routines = `select count(*) from information_schema.routines`
|
||
|
||
--echo #
|
||
--echo # We cannot use the index due to missing condition on SPECIFIC_SCHEMA,
|
||
--echo # but we will use ROUTINE_NAME for filtering records from mysql.proc
|
||
FLUSH STATUS;
|
||
--disable_cursor_protocol
|
||
--disable_ps2_protocol
|
||
--replace_column 24 <created> 25 <modified>
|
||
query_vertical SELECT * FROM INFORMATION_SCHEMA.ROUTINES
|
||
WHERE ROUTINE_NAME = 'test_func5';
|
||
--enable_ps2_protocol
|
||
--enable_cursor_protocol
|
||
--replace_result $count_routines count_routines
|
||
SHOW STATUS LIKE 'handler_read%next';
|
||
|
||
--echo #
|
||
--echo # We cannot use the index due to CONCAT(), and filtering by ROUTINE_NAME
|
||
--echo # does not work either since ROUTINE_NAME = 'not_existing_proc'. See
|
||
--echo # the difference in counters in comparison to the previous test
|
||
--disable_cursor_protocol
|
||
--disable_ps2_protocol
|
||
FLUSH STATUS;
|
||
query_vertical SELECT * FROM INFORMATION_SCHEMA.ROUTINES
|
||
WHERE CONCAT(ROUTINE_SCHEMA) = 'i_s_routines_test'
|
||
AND ROUTINE_NAME = 'not_existing_proc';
|
||
--replace_result $count_routines count_routines
|
||
SHOW STATUS LIKE 'handler_read%next';
|
||
|
||
--echo #
|
||
--echo # Now the index must be used
|
||
FLUSH STATUS;
|
||
--replace_column 24 <created> 25 <modified>
|
||
query_vertical SELECT * FROM INFORMATION_SCHEMA.ROUTINES
|
||
WHERE ROUTINE_SCHEMA = 'i_s_routines_test'
|
||
AND ROUTINE_NAME = 'test_func5';
|
||
SHOW STATUS LIKE 'handler_read%next';
|
||
|
||
--echo #
|
||
--echo # Using the first key part of the index
|
||
FLUSH STATUS;
|
||
--replace_column 24 <created> 25 <modified>
|
||
query_vertical SELECT * FROM INFORMATION_SCHEMA.ROUTINES
|
||
WHERE ROUTINE_SCHEMA = 'i_s_routines_test';
|
||
SHOW STATUS LIKE 'handler_read%next';
|
||
|
||
--echo #
|
||
--echo # Test non-latin letters in procedure name
|
||
SET NAMES koi8r;
|
||
CREATE PROCEDURE `процедурка`(a INT) SELECT a;
|
||
--echo #
|
||
--echo # The index must be used
|
||
FLUSH STATUS;
|
||
--replace_column 24 <created> 25 <modified>
|
||
query_vertical SELECT * FROM INFORMATION_SCHEMA.ROUTINES
|
||
WHERE ROUTINE_SCHEMA = 'i_s_routines_test'
|
||
AND ROUTINE_NAME = 'процедурка';
|
||
SHOW STATUS LIKE 'handler_read%next';
|
||
--enable_ps2_protocol
|
||
--enable_cursor_protocol
|
||
|
||
--echo #
|
||
--echo # Test SHOW PROCEDURE STATUS. It's impossible to use the index here
|
||
--echo # so don't check Handler_read counters, only the results correctness
|
||
--replace_column 5 <modified> 6 <created>
|
||
query_vertical SHOW FUNCTION STATUS LIKE 'test_func5';
|
||
--replace_column 5 <modified> 6 <created>
|
||
query_vertical SHOW FUNCTION STATUS LIKE 'test_%';
|
||
--replace_column 5 <modified> 6 <created>
|
||
query_vertical SHOW FUNCTION STATUS LIKE '%func%';
|
||
--replace_column 5 <modified> 6 <created>
|
||
query_vertical SHOW FUNCTION STATUS LIKE 'test';
|
||
--replace_column 5 <modified> 6 <created>
|
||
query_vertical SHOW PROCEDURE STATUS LIKE 'процедурка';
|
||
--replace_column 5 <modified> 6 <created>
|
||
query_vertical SHOW PROCEDURE STATUS LIKE '%оцедурка';
|
||
--replace_column 5 <modified> 6 <created>
|
||
query_vertical SHOW PROCEDURE STATUS LIKE '%оцедур%';
|
||
--replace_column 5 <modified> 6 <created>
|
||
query_vertical SHOW PROCEDURE STATUS LIKE 'такой_нет';
|
||
|
||
# Cleanup
|
||
DROP DATABASE i_s_routines_test;
|
||
USE test;
|