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

[19 Dec 2006 18:53] Colm McHugh
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.
[19 Dec 2006 18:59] Valeriy Kravchuk
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)
[19 Dec 2006 23:12] Marc Alff
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'
[20 Dec 2006 18:17] Colm McHugh
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.
[20 Dec 2006 18:50] Marc Alff
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.
[20 Dec 2006 19:17] Konstantin Osipov
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).