Bug #33106 Can't distinguish parameters in information_schema.parameters.
Submitted: 10 Dec 2007 10:03 Modified: 15 Mar 2008 19:19
Reporter: Santo Leto Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Information schema Severity:S2 (Serious)
Version:6.0.3-alpha OS:Microsoft Windows (XPSP2)
Assigned to: Sergey Glukhov CPU Architecture:Any
Tags: information_schema.parameters, routine parameters
Triage: D4 (Minor) / R2 (Low) / E2 (Low)

[10 Dec 2007 10:03] Santo Leto
Description:
Can't distinguish parameters in information_schema.parameters if a db contains a proc. and a func. with the same name.

How to repeat:
Test Script:

DROP DATABASE IF EXISTS test_parameters;
CREATE DATABASE test_parameters;
USE test_parameters;

DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `test`(IN `param1` BIGINT(20))
BEGIN
END $$
DELIMITER ;

DELIMITER $$
CREATE DEFINER=`root`@`localhost` FUNCTION `test`(`param1` BIGINT(20)) RETURNS text CHARSET latin1
BEGIN
  RETURN "aa";
END $$
DELIMITER ;

SELECT * FROM `INFORMATION_SCHEMA`.`PARAMETERS` WHERE `SPECIFIC_SCHEMA`='test_parameters' AND `SPECIFIC_NAME`='test'\G

Script Output:

mysql> DROP DATABASE IF EXISTS test_parameters;
Query OK, 0 rows affected (0.08 sec)

mysql> CREATE DATABASE test_parameters;
Query OK, 1 row affected (0.01 sec)

mysql> USE test_parameters;
Database changed
mysql>
mysql> DELIMITER $$
mysql> CREATE DEFINER=`root`@`localhost` PROCEDURE `test`(IN `param1` BIGINT(20)
)
    -> BEGIN
    -> END $$
Query OK, 0 rows affected (0.00 sec)

mysql> DELIMITER ;
mysql>
mysql> DELIMITER $$
mysql> CREATE DEFINER=`root`@`localhost` FUNCTION `test`(`param1` BIGINT(20)) RE
TURNS text CHARSET latin1
    -> BEGIN
    ->   RETURN "aa";
    -> END $$
Query OK, 0 rows affected (0.00 sec)

mysql> DELIMITER ;
mysql>
mysql> SELECT * FROM `INFORMATION_SCHEMA`.`PARAMETERS` WHERE `SPECIFIC_SCHEMA`='
test_parameters' AND `SPECIFIC_NAME`='test'\G
*************************** 1. row ***************************
        SPECIFIC_CATALOG: NULL
         SPECIFIC_SCHEMA: test_parameters
           SPECIFIC_NAME: test
        ORDINAL_POSITION: 0
          PARAMETER_MODE: NULL
          PARAMETER_NAME: NULL
               DATA_TYPE: text
CHARACTER_MAXIMUM_LENGTH: 65535
  CHARACTER_OCTET_LENGTH: 65535
       NUMERIC_PRECISION: NULL
           NUMERIC_SCALE: NULL
      CHARACTER_SET_NAME: latin1
          COLLATION_NAME: latin1_swedish_ci
          DTD_IDENTIFIER: text
*************************** 2. row ***************************
        SPECIFIC_CATALOG: NULL
         SPECIFIC_SCHEMA: test_parameters
           SPECIFIC_NAME: test
        ORDINAL_POSITION: 1
          PARAMETER_MODE: IN
          PARAMETER_NAME: param1
               DATA_TYPE: bigint
CHARACTER_MAXIMUM_LENGTH: NULL
  CHARACTER_OCTET_LENGTH: NULL
       NUMERIC_PRECISION: 19
           NUMERIC_SCALE: 0
      CHARACTER_SET_NAME: NULL
          COLLATION_NAME: NULL
          DTD_IDENTIFIER: bigint(20)
*************************** 3. row ***************************
        SPECIFIC_CATALOG: NULL
         SPECIFIC_SCHEMA: test_parameters
           SPECIFIC_NAME: test
        ORDINAL_POSITION: 1
          PARAMETER_MODE: IN
          PARAMETER_NAME: param1
               DATA_TYPE: bigint
CHARACTER_MAXIMUM_LENGTH: NULL
  CHARACTER_OCTET_LENGTH: NULL
       NUMERIC_PRECISION: 19
           NUMERIC_SCALE: 0
      CHARACTER_SET_NAME: NULL
          COLLATION_NAME: NULL
          DTD_IDENTIFIER: bigint(20)
3 rows in set (0.02 sec)

Can't tell which is which!

Suggested fix:
Add a `Routine_Type` field in information_schema.parameters thus you can query:

select * from information_schema.parameters
where specific_schema='test_parameters'
and specific_name='test'
and routine_type='procedure';

select * from information_schema.parameters
where specific_schema='test_parameters'
and specific_name='test'
and routine_type='function';
[10 Dec 2007 11:19] Sveta Smirnova
According to SQL standard this should be considered as "Not a Bug":

----<START QUOTE>----
5.34 PARAMETERS view 
Function 
Identify the SQL parameters of SQL-invoked routines defined in this catalog that are accessible to a given user 
or role. 
Definition 

CREATEVIEW PARAMETERS AS 
SELECT SPECIFIC_CATALOG, SPECIFIC_SCHEMA, SPECIFIC_NAME, 
           P1.ORDINAL_POSITION, PARAMETER_MODE, 
           P1.IS_RESULT, P1.AS_LOCATOR, PARAMETER_NAME, 
           FROM_SQL_SPECIFIC_CATALOG, FROM_SQL_SPECIFIC_SCHEMA, FROM_SQL_SPECIFIC_NAME, 
           TO_SQL_SPECIFIC_CATALOG, TO_SQL_SPECIFIC_SCHEMA, TO_SQL_SPECIFIC_NAME, 
           DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, CHARACTER_OCTET_LENGTH, 
           D1.CHARACTER_SET_CATALOG, D1.CHARACTER_SET_SCHEMA, D1.CHARACTER_SET_NAME, 
           D1.COLLATION_CATALOG, D1.COLLATION_SCHEMA, D1.COLLATION_NAME, 
           NUMERIC_PRECISION, NUMERIC_PRECISION_RADIX, NUMERIC_SCALE, 
           DATETIME_PRECISION,INTERVAL_TYPE, INTERVAL_PRECISION, 
           D1.USER_DEFINED_TYPE_CATALOGAS UDT_CATALOG, 
           D1.USER_DEFINED_TYPE_SCHEMAAS UDT_SCHEMA, 
           D1.USER_DEFINED_TYPE_NAMEAS UDT_NAME, 
           D1.SCOPE_CATALOG, D1.SCOPE_SCHEMA, D1.SCOPE_NAME, 
           D1.MAXIMUM_CARDINALITY, D1.DTD_IDENTIFIER 
FROM ( DEFINITION_SCHEMA.PARAMETERS P1 
LEFTJOIN 
           DEFINITION_SCHEMA.DATA_TYPE_DESCRIPTOR D1 
ON ( SPECIFIC_CATALOG, SPECIFIC_SCHEMA, SPECIFIC_NAME, 
                'ROUTINE', P1.DTD_IDENTIFIER ) 
            = ( OBJECT_CATALOG, OBJECT_SCHEMA, OBJECT_NAME, 
                OBJECT_TYPE, D1.DTD_IDENTIFIER ) ) 
JOIN 
         DEFINITION_SCHEMA.ROUTINES R1 
USING ( SPECIFIC_CATALOG, SPECIFIC_SCHEMA, SPECIFIC_NAME ) 
WHERE ( ( ( MODULE_CATALOG, MODULE_SCHEMA, MODULE_NAME ) IS NULL 
AND 
              ( SPECIFIC_CATALOG, SPECIFIC_SCHEMA, SPECIFIC_NAME ) IN 
              ( SELECT SPECIFIC_CATALOG, SPECIFIC_SCHEMA, SPECIFIC_NAME 
FROM DEFINITION_SCHEMA.ROUTINE_PRIVILEGES 
WHERE ( GRANTEE IN 
                        ( 'PUBLIC', CURRENT_USER ) 
OR 
                        GRANTEE IN 
                        ( SELECT ROLE_NAME 
FROM ENABLED_ROLES ) ) ) ) ) 
AND SPECIFIC_CATALOG 
        = ( SELECT CATALOG_NAME 
FROM INFORMATION_SCHEMA_CATALOG_NAME ); 
GRANTSELECTON TABLE PARAMETERS 

----<END QUOTE>----

Although distinguish parameters is not possible.
[10 Dec 2007 11:32] Godofredo Miguel Solorzano
Thank you for the bug report.

mysql> SELECT * FROM `INFORMATION_SCHEMA`.`PARAMETERS` WHERE `SPECIFIC_SCHEMA`='test_parameters'
    -> AND `SPECIFIC_NAME`='test'\G
*************************** 1. row ***************************
        SPECIFIC_CATALOG: NULL
         SPECIFIC_SCHEMA: test_parameters
           SPECIFIC_NAME: test
        ORDINAL_POSITION: 0
          PARAMETER_MODE: NULL
          PARAMETER_NAME: NULL
               DATA_TYPE: text
CHARACTER_MAXIMUM_LENGTH: 65535
  CHARACTER_OCTET_LENGTH: 65535
       NUMERIC_PRECISION: NULL
           NUMERIC_SCALE: NULL
      CHARACTER_SET_NAME: latin1
          COLLATION_NAME: latin1_swedish_ci
          DTD_IDENTIFIER: text
*************************** 2. row ***************************
        SPECIFIC_CATALOG: NULL
         SPECIFIC_SCHEMA: test_parameters
           SPECIFIC_NAME: test
        ORDINAL_POSITION: 1
          PARAMETER_MODE: IN
          PARAMETER_NAME: param1
               DATA_TYPE: bigint
CHARACTER_MAXIMUM_LENGTH: NULL
  CHARACTER_OCTET_LENGTH: NULL
       NUMERIC_PRECISION: 19
           NUMERIC_SCALE: 0
      CHARACTER_SET_NAME: NULL
          COLLATION_NAME: NULL
          DTD_IDENTIFIER: bigint(20)
*************************** 3. row ***************************
        SPECIFIC_CATALOG: NULL
         SPECIFIC_SCHEMA: test_parameters
           SPECIFIC_NAME: test
        ORDINAL_POSITION: 1
          PARAMETER_MODE: IN
          PARAMETER_NAME: param1
               DATA_TYPE: bigint
CHARACTER_MAXIMUM_LENGTH: NULL
  CHARACTER_OCTET_LENGTH: NULL
       NUMERIC_PRECISION: 19
           NUMERIC_SCALE: 0
      CHARACTER_SET_NAME: NULL
          COLLATION_NAME: NULL
          DTD_IDENTIFIER: bigint(20)
3 rows in set (0.00 sec)

mysql> show variables like "%version%";
+-------------------------+---------------------+
| Variable_name           | Value               |
+-------------------------+---------------------+
| protocol_version        | 10                  |
| version                 | 6.0.5-alpha-nt      |
| version_comment         | Source distribution |
| version_compile_machine | unknown             |
| version_compile_os      | Win64               |
+-------------------------+---------------------+
5 rows in set (0.00 sec)

mysql>
[17 Dec 2007 7:13] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/40083

ChangeSet@1.2766, 2007-12-17 11:07:57+04:00, gluh@mysql.com +3 -0
  Bug#33106 Can't distinguish parameters in information_schema.parameters.
  added new column 'SPECIFIC_TYPE'
[24 Jan 2008 21:29] Peter Gulutzan
This happened because we decided that SPECIFIC_NAME would be
the name that one uses with the CREATE TABLE or CREATE FUNCTION
statement. In standard SQL that's not the case, SPECIFIC_NAME
would be unique for all routines in the schema, and this problem
would not arise. So the original specification, from me, for
information_schema.parameters, was defective.
Adding a new column at end of the view is correct.

SPECIFIC_TYPE is not a good name. There is already a column in
INFORMATION_SCHEMA.ROUTINES named ROUTINE_TYPE, with possible
values = 'PROCEDURE' or 'FUNCTION'. Please use ROUTINE_TYPE.
[30 Jan 2008 9:45] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/41410

ChangeSet@1.2792, 2008-01-30 13:40:08+04:00, gluh@mysql.com +3 -0
  Bug#33106 Can't distinguish parameters in information_schema.parameters
  added new column 'ROUTINE_TYPE'
[5 Feb 2008 9:38] Alexander Barkov
http://lists.mysql.com/commits/41410 is ok to push.
[22 Feb 2008 12:52] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/42824

ChangeSet@1.2585, 2008-02-22 16:51:30+04:00, gluh@mysql.com +3 -0
  Bug#33106 Can't distinguish parameters in information_schema.parameters
  added new column 'ROUTINE_TYPE'
[13 Mar 2008 19:26] Bugs System
Pushed into 6.0.5-alpha
[15 Mar 2008 19:19] Jon Stephens
Documented feature addition in the 6.0.5 changelog as follows:

        Added a ROUTINE_TYPE column to the
        INFORMATION_SCHEMA.PARAMETERS table, to make it
        possible to distinguish like-named parameters of stored routines and
        stored functions having the same names. See 
        -The INFORMATION_SCHEMA PARAMETERS Table-, for more information.