MySQL Bugs: #25184: ANSI delimited identifier syntax ignored in ansi mode

Bug #25184	ANSI delimited identifier syntax ignored in ansi mode
Submitted:	19 Dec 2006 18:53	Modified:	20 Dec 2006 18:17
Reporter:	Colm McHugh	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: General	Severity:	S3 (Non-critical)
Version:	5.0.34-BK, 5.0.27	OS:	Linux (Linux, win32)
Assigned to:	Marc ALFF	CPU Architecture:	Any
Tags:	ansi, ANSI_QUOTES, delimited identifier syntax

Description:
If you start the server in ANSI mode and issue a command with ANSI delimited identifier syntax (such as the table name in the command: CREATE TABLE "foo" (c1 int)), it will be rejected but it should be accepted.

How to repeat:
mysql> SET GLOBAL TRANSACTION ISOLATION LEVEL SERIALIZABLE; 
Query OK, 0 rows affected (0.02 sec) 

mysql> SET GLOBAL sql_mode = 'ANSI'; 
Query OK, 0 rows affected (0.00 sec) 

mysql> SELECT @@global.sql_mode; 
+-------------------------------------------------------------+ 
| @@global.sql_mode | 
+-------------------------------------------------------------+ 
| REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ANSI | 
+-------------------------------------------------------------+ 
1 row in set (0.01 sec) 

mysql> -- This should work:
mysql> create table "foo" (c1 integer ); 
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that 
corresponds to your MySQL server version for the right syntax to use near '"foo" 
(c1 integer )' at line 1 
mysql> create table foo (c1 integer ); 
Query OK, 0 rows affected (0.13 sec) 

Suggested fix:
None. Well, use the special mysql syntax (the ` (backtik) character), but for automated tools that delimit identifiers according to ANSI SQL, they cannot rely on being able to use MySQL 5 as a data source, once functioning applications (using MySQL 4 in ansi mode) are now broken.

Thank you for a bug report. Verified just as described with 5.0.34-BK on Linux:

mysql> select version();
+--------------+
| version()    |
+--------------+
| 5.0.34-debug |
+--------------+
1 row in set (0.01 sec)

mysql> SET GLOBAL sql_mode = 'ANSI';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @@global.sql_mode;
+-------------------------------------------------------------+
| @@global.sql_mode                                           |
+-------------------------------------------------------------+
| REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ANSI |
+-------------------------------------------------------------+
1 row in set (0.01 sec)

mysql> drop table foo;
Query OK, 0 rows affected (0.02 sec)

mysql> create table "foo" (c1 integer );
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near '"foo"
 (c1 integer )' at line 1
mysql> create table foo (c1 integer );
Query OK, 0 rows affected (0.02 sec)

Thanks for the report, but this is not a bug.

As documented in
http://dev.mysql.com/doc/refman/5.0/en/server-sql-mode.html

"
You can change the SQL mode at runtime by using a SET [GLOBAL|SESSION] sql_mode='modes'  statement to set the sql_mode system value. Setting the GLOBAL variable requires the SUPER privilege and affects the operation of all clients that connect from that time on. Setting the SESSION variable affects only the current client. Any client can change its own session sql_mode value at any time.
"

In this case, since the client was connected before SET GLOBAL SQL_MODE='ANSI',
the previous mode is still in effect for the session,
which could be verified with select @@session.sql_mode

If a new client connects, the ANSI mode will be in effect, and the
create table "foo" statement given as example succeed (verified with
MySQL version 5.0.32-debug-log)

To affect the mode in the current session, use SET SESSION SQL_MODE='ANSI',
or in short, SET SQL_MODE='ANSI'

Thanks for the clarification, so basically each client connection must explicitly declare that it wants to operate in ASCII mode. This is not really related, but it would be nice if the MySQL 5 ODBC driver had an "Initial Statement" field (similar to the 3.51 ODBC driver) so that ODBC applications did not have to do anything MySQL specific when connecting to MySQL 5.

Another option is to really start the server in SQL_MODE='ANSI',
if this is needed for all clients.

This can be done by writing the
  SET GLOBAL SQL_MODE='ANSI';
command in a file, and use of the following option :
mysqld --init-file

Any other way to start the server will work as well (custom scripts, ...),
the only critical point is that SET GLOBAL SQL_MODE only takes effect for
clients that connects after that statement is executed.

Alternatively, the sql mode can be set in the server configuration file, e.g.

/etc/my.cnf:
[mysqld]
sql_mode=strict_trans_tables,ansi

(ansi currently does not imply strict_trans_tables).