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

[19 Nov 2007 23:01] James Davis
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.
[1 Nov 2009 10:46] Valeriy Kravchuk
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)
[2 Dec 2009 0:00] Bugs System
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".

Portions of this website are copyright © 2001, 2002 The PHP Group

Page generated in 0.051 sec. using MySQL 8.4.7-u6-cloud

Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.