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