MySQL Bugs: #32508: inability to obtain param list for Functions, especially where there are none

Bug #32508	inability to obtain param list for Functions, especially where there are none
Submitted:	19 Nov 2007 23:01	Modified:	1 Dec 2009 10:46
Reporter:	James Davis	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server: Information schema	Severity:	S3 (Non-critical)
Version:	5.1.22-rc-community	OS:	Windows (W2K and W2003 Servers)
Assigned to:		CPU Architecture:	Any
Tags:	cast, FUNCTION, param_list

Description:
In order to obtain information on MySQL Functions, in particular, to determine what a function's parameters are, you must query the information_schema.ROUTINES table with a join to the MYSQL.PROC using the SPECIFIC_NAME fields and the information you are looking for is contained in a blob field called PARAM_LIST.  That information cannot be read as a text field.  You must use a CAST(param_list as char) to read the field, but even that results in an unexpected result if no parameters were specified: the result, then, is a single unprintable character while it should either be a null or empty.

Issues:  Why must a cast be used to obtain contents of that field and why does it return a single character instead of null or an empty string when no parameter is specified.

How to repeat:
Here is a simply demonstration and test case.

Create a simple Function with:

DELIMITER //
CREATE DEFINER=`root`@`localhost` FUNCTION `NowToStr`() RETURNS varchar(14) CHARSET latin1 NO SQL COMMENT 'Returns 14 digit string of now()'
BEGIN
   return  date_format(now(),'%Y%m%d%H%i%s');
END;//
DELIMITER ;

Query the system to find the parameter for the function:

SELECT cast(param_list as char) FROM INFORMATION_SCHEMA.ROUTINES i INNER JOIN MYSQL.PROC m ON i.routine_name=m.name and i.routine_schema=m.db WHERE i.ROUTINE_TYPE='FUNCTION' AND i.ROUTINE_SCHEMA='your db name' AND m.name='NowToStr'

The return will be a single character.

Please, try to repeat with a newer version, 5.1.40, and inform about the results. Look:

77-52-222-60:5.1 openxs$ bin/mysql -uroot -pmysql test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.41-debug Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> DELIMITER //
mysql> CREATE DEFINER=`root`@`localhost` FUNCTION `NowToStr`() RETURNS varchar(14) CHARSET latin1
    -> NO SQL COMMENT 'Returns 14 digit string of now()'
    -> BEGIN
    ->    return  date_format(now(),'%Y%m%d%H%i%s');
    -> END;//
Query OK, 0 rows affected (0.39 sec)

mysql> DELIMITER ;

mysql> SELECT cast(param_list as char) FROM INFORMATION_SCHEMA.ROUTINES i INNER JOIN MYSQL.PROC m ON i.routine_name=m.name and i.routine_schema=m.db WHERE i.ROUTINE_TYPE='FUNCTION' AND i.ROUTINE_SCHEMA='test' AND m.name='NowToStr';
+--------------------------+
| cast(param_list as char) |
+--------------------------+
|                          |
+--------------------------+
1 row in set (0.00 sec)

mysql> SELECT length(cast(param_list as char)) FROM INFORMATION_SCHEMA.ROUTINES i INNER JOIN MYSQL.PROC m ON i.routine_name=m.name and i.routine_schema=m.db WHERE i.ROUTINE_TYPE='FUNCTION' AND i.ROUTINE_SCHEMA='test' AND m.name='NowToStr';
+----------------------------------+
| length(cast(param_list as char)) |
+----------------------------------+
|                                0 |
+----------------------------------+
1 row in set (0.01 sec)

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".