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:Windows (XPSP2)
Assigned to: Sergei Glukhov CPU Architecture:Any
Tags: information_schema.parameters, routine parameters

[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] MySQL Verification Team
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.

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

Page generated in 0.063 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.