| Bug #2564 | SHOW CREATE inconsistent W.R.T ANSI_QUOTES | ||
|---|---|---|---|
| Submitted: | 29 Jan 2004 16:29 | Modified: | 9 Jun 2004 15:42 |
| Reporter: | Paul DuBois | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server | Severity: | S3 (Non-critical) |
| Version: | 5.0 | OS: | |
| Assigned to: | Per-Erik Martin | CPU Architecture: | Any |
[29 Jan 2004 23:53]
Sergei Golubchik
Pem, I fixed SHOW CREATE DATABASE, could you take care of sp part of the bug ? The code should use append_identifier() function from sql_show.cc for all identifiers - it will ensure proper quoting.
[8 Jun 2004 18:56]
Per-Erik Martin
Not only identifiers are not quoted correctly, some other parts needs to be quoted as well (e.g. the comment). See also BUG#3229.
[9 Jun 2004 15:42]
Per-Erik Martin
Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.
If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information
about accessing the source trees is available at
http://www.mysql.com/doc/en/Installing_source_tree.html
Additional info:
Stored procedures and functions cannot be quoted according to the current
sql_mode setting. Instead we quote according to the sql_mode at creation
time (which is store with the procedure and used for parsing), and show the
sql_mode in the SHOW CREATE output.
Description: SHOW CREATE statements are inconsistent in the output they produce with respect to the ANSI_QUOTES setting. With ANSI_QUOTES disabled, SHOW CREATE {TABLE, DATABASE, and FUNCTION|PROCEDURE} all quote names using backticks: mysql> SHOW CREATE TABLE t; +------- +---------------------------------------------------------------------------------- ----+ | Table | Create Table | +------- +---------------------------------------------------------------------------------- ----+ | t | CREATE TABLE `t` ( `i` int(11) default NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 | +------- +---------------------------------------------------------------------------------- ----+ 1 row in set (0.01 sec) mysql> SHOW CREATE DATABASE test; +----------+-----------------------------------------------------------------+ | Database | Create Database | +----------+-----------------------------------------------------------------+ | test | CREATE DATABASE `test` /*!40100 DEFAULT CHARACTER SET latin1 */ | +----------+-----------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> SHOW CREATE FUNCTION f; +----------+--------------------------------------------+ | Function | Create Function | +----------+--------------------------------------------+ | f | CREATE FUNCTION `f`() RETURNS int return 1 | +----------+--------------------------------------------+ 1 row in set (0.00 sec) With ANSI_QUOTES enabled, SHOW CREATE TABLE quotes names with double quotes, but SHOW CREATE DATABASE and SHOW CREATE FUNCTION|PROCEDURE continue to use backticks: mysql> SET @@sql_mode='ANSI_QUOTES'; Query OK, 0 rows affected (0.00 sec) mysql> SHOW CREATE TABLE t; +------- +---------------------------------------------------------------------------------- ----+ | Table | Create Table | +------- +---------------------------------------------------------------------------------- ----+ | t | CREATE TABLE "t" ( "i" int(11) default NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 | +------- +---------------------------------------------------------------------------------- ----+ 1 row in set (0.00 sec) mysql> SHOW CREATE DATABASE test; +----------+-----------------------------------------------------------------+ | Database | Create Database | +----------+-----------------------------------------------------------------+ | test | CREATE DATABASE `test` /*!40100 DEFAULT CHARACTER SET latin1 */ | +----------+-----------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> SHOW CREATE FUNCTION f; +----------+--------------------------------------------+ | Function | Create Function | +----------+--------------------------------------------+ | f | CREATE FUNCTION `f`() RETURNS int return 1 | +----------+--------------------------------------------+ 1 row in set (0.00 sec) How to repeat: See above.