Details
-
Type:
Bug
-
Status: Stalled
-
Priority:
Minor
-
Resolution: Unresolved
-
Affects Version/s: 5.5.36-galera, 5.3.12, 10.0.12
-
Fix Version/s: 10.0
-
Component/s: None
-
Labels:None
Description
SHOW CREATE FUNCTION displays correctly escaped double quotes and backslashes in string literals.
I_S.ROUTINES.ROUTINE_BODY displays unescaped values, which looks wrong.
This SQL script demonstrates the problem:
DROP FUNCTION IF EXISTS f1; CREATE FUNCTION f1() RETURNS VARCHAR(1) RETURN 'a\x\t\\t''t'; SHOW CREATE FUNCTION f1; SELECT ROUTINE_DEFINITION FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME='f1';
The output from SHOW is:
+----------+----------+----------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+ | Function | sql_mode | Create Function | character_set_client | collation_connection | Database Collation | +----------+----------+----------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+ | f1 | | CREATE DEFINER=`root`@`localhost` FUNCTION `f1`() RETURNS varchar(1) CHARSET latin1 RETURN 'a\x\t\\t''t' | utf8 | utf8_general_ci | latin1_swedish_ci | +----------+----------+----------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
The output from SELECT is:
+--------------------+ | ROUTINE_DEFINITION | +--------------------+ | RETURN 'ax \t't' | +--------------------+
ROUTINE_DEFINITION should also display escaped values.
Gliffy Diagrams
Attachments
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
Right, the purpose of I_S.ROUTINES is not to give a valid SQL code.
But anyway, it should escape, for user readability.
Note, among other dangerous characters, '\0' is also not escaped!
That means the client application cannot use usual C string
routines, like printf, for the column I_S.ROUTINES.ROUTINE_DEFINITION.
Although it's probably not very important, but still, this IS a bug.
A similar problem happens with binary strings.
This script:
DROP FUNCTION IF EXISTS f1; CREATE FUNCTION f1() RETURNS VARBINARY(10) RETURN '\t\0\t'; SHOW CREATE FUNCTION f1; SELECT ROUTINE_DEFINITION FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME='f1';
returns:
+--------------------+ | ROUTINE_DEFINITION | +--------------------+ | RETURN ' ' | +--------------------+
This is expected behavior. I_S.ROUTINES table shows the routine definition in the UTF8 character set. It is generally impossible to represent a definition of the arbitrary routine in the UTF8. Thus the purpose of the I_S.ROUTINES.ROUTINE_DEFINITION column is to provide a user-readable representation of the routine definition, but it may not always be used to recreate the routine and won't necessarily be valid SQL.
For the latter one should use SHOW CREATE FUNCTION. But its output won't necessarily be in any specific character set.