MySQL Bugs: #37604: ANSI does not include NO_BACKSLASH

Bug #37604	ANSI does not include NO_BACKSLASH_ESCAPES
Submitted:	24 Jun 2008 11:52	Modified:	25 Jun 2008 7:06
Reporter:	d di (Basic Quality Contributor)	Email Updates:
Status:	Won't fix	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.0.51a	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	qc

Description:
The MySQL manual says:

"NO_BACKSLASH_ESCAPES enables an SQL standard compatibility mode, where backslash is not considered a special character."

But switching to standards mode does not seem to enable it:

> SET sql_mode='ANSI';
> SELECT @@sql_mode;
-------
REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ANSI
-------
> SELECT '\\';
-------
\
-------

How to repeat:
SET sql_mode='ANSI'

Suggested fix:
Make 'ANSI' include 'NO_BACKSLASH_ESCAPES' if indeed there are no mentions of using backslash as an escape character inside literal strings in SQL:2003.

Thank you for a problem report. As current meaning of ANSI is clearly described in the manual Id consider this a feature request. Do you agree?

No, definitely not.

'ANSI' is supposed to mean ANSI-compliant mode.

This includes NO_BACKSLASH_ESCAPES (and whatever else MySQL Server might be doing that's in violation of the standard).

Perhaps version 'ANSI' if you want to retain backwards compatibility with older meanings?

'ANSI'  - MySQL ANSI compatibility level 1
'ANSI1' -           - || -
'ANSI2' - MySQL ANSI compatibility level 2 (includes NO_BACKSLASH_ESCAPES)
'ANSI3' - MySQL ANSI compatibility level 3 (future)

I agree with a request for some other mode that will be ANSI + NO_BACKSLASH_ESCAPES.

By the way, I had NOT found any reference to backslash in SQL 2003 Standard draft, so it is surely not a bug.

> By the way, I had NOT found any reference to backslash
> in SQL 2003 Standard draft, so it is surely not a bug.

If you do NOT find any reference, then it IS a bug.
(Since mysql DOES escape in standards mode when it is NOT in the reference.)

In practical terms,

> SET sql_mode='ANSI'
> SELECT '\\'
-----
\
-----

^ BUG !!

(Since I can't change the 'defect class', I'll just up the severity instead.  Severity S4 is "feature request", dunno how changing it affects 'defect class'?)

No, this is not a bug. As standard does not care about backslash explicitely, MySQL is free to define some specific behaviour to it. And it is defined in http://dev.mysql.com/doc/refman/5.0/en/string-syntax.html:

"Within a string, certain sequences have special meaning. Each of these sequences begins with a backslash (“\”), known as the escape character. MySQL recognizes the following escape sequences:
..."

NO_BACKSLASH_ESCAPES is designed to switch off that special meaning.

If that was true, then MySQL would be free to assign the special meaning "bogus character" to every character from A to Z in the English alphabet.  Inserting would store nothing; selecting would always yield empty fields, and according to your stance, this would be perfectly in accordance with the SQL standard ;-).

Thank you for taking the time to write to us, but this is not a bug. Please double-check the documentation available at http://dev.mysql.com/doc/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

The MySQL Reference Manual says (http://dev.mysql.com/doc/refman/6.0/en/server-sql-mode.html) : 
"ANSI
This mode changes syntax and behavior to
conform more closely to standard SQL."
Note : It says "more closely" rather than "exactly"
because there is no intention to suggest
that --ansi is a magic switch to make all
behavior standard.

Later on the same page, you'll read
"ANSI
Equivalent to REAL_AS_FLOAT, PIPES_AS_CONCAT,
ANSI_QUOTES, IGNORE_SPACE."

After discussing it with Peter Gulutzan we don't think it's a good idea to introduce incompatible changes to this mode.

I don't think anyone has asked for that.
Just add ANSI2, a newer and better ANSI support mode, as suggested.