| 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: | |
| 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 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.
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';