Bug #6903 Procedures, functions and views not quoted correctly when using ANSI_QUOTES

• Submitted: 1 Dec 2004 1:26
• Modified: 25 Jul 2005 19:37
• Reporter: Michael G. Zinner
• Status: Closed
• Category: MySQL Server
• Severity: S3 (Non-critical)
• Version: 5.0.1
• OS: Windows (WinXP)
• Assigned to: Jim Winstead
• CPU Architecture: Any

[1 Dec 2004 1:26] Michael G. Zinner

Description:
When executing a SHOW CREATE PROCEDURE the procedure's name is always quoted in back tick, regardless to what the sql_mode is set.

This is also true for SHOW CREATE FUNCTION and SHOW CREATE VIEW.

How to repeat:
mysql> show variables like 'sql_mode';
+---------------+-------------+
| Variable_name | Value       |
+---------------+-------------+
| sql_mode      | ANSI_QUOTES |
+---------------+-------------+
1 row in set (0.00 sec)

mysql> show create procedure sp1;
+-----------+----------+----------------------------------------------------------------------+
| Procedure | sql_mode | Create Procedure                                                     |
+-----------+----------+----------------------------------------------------------------------+
| sp1       |          | CREATE PROCEDURE `test`.`sp1`()
begin
  select * from product;
end |
+-----------+----------+----------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> show create table nulltest;
+----------+--------------------------------------------------------------------------------------------------------+
| Table    | Create Table                                                                                           |
+----------+--------------------------------------------------------------------------------------------------------+
| nulltest | CREATE TABLE "nulltest" (
  "nulltest" varchar(25) default NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+----------+--------------------------------------------------------------------------------------------------------+

Suggested fix:
Consider (session) sql_mode when building the CREATE PROCEDURE / FUNCTION / VIEW statement.

[1 Dec 2004 14:18] MySQL Verification Team

Verified with 5.0.2-alpha-debug-log

[3 Feb 2005 2:55] Jim Winstead

I believe the behavior for PROCEDURE and FUNCTION is intentional. The create statement is always returned according to the SQL_MODE that is also returned in the SHOW statement.

SHOW CREATE VIEW is misbehaving, and I'm checking with Sanja to see what he thinks should be done to fix it. The fix may be a behavior similar to that of SHOW CREATE PROCEDURE and FUNCTION.

[17 Feb 2005 20:07] Jim Winstead

With the patch, SHOW CREATE VIEW will pay attention to SQL_MODE (whether ANSI_QUOTES or otherwise). But SHOW CREATE PROCEDURE and FUNCTION are not able to, which is why they include an SQL_MODE column.

[24 Feb 2005 10:08] Alexander Barkov

Ok to push.

[1 Jun 2005 19:02] 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/internals/25475

[15 Jun 2005 23:27] 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/internals/26042

[28 Jun 2005 20:46] Magnus Blåudd

Ok to push.

[6 Jul 2005 1:23] Jim Winstead

Fixed in 5.0.9.

[25 Jul 2005 19:37] Mike Hillyer

Documented in 5.0.9 changelog:

<listitem><para><literal>SHOW CREATE VIEW</literal> did not take the <literal>ANSI MODE</literal> into account when quoting identifiers. (Bug #6903)</para></listitem>