Bug #14601 MySQL 5.0.15 with --ansi mode allows non-SQL92 operators (!, !=, etc.)

• Submitted: 3 Nov 2005 11:37
• Modified: 3 Nov 2005 12:11
• Reporter: Jari Aalto
• Status: Verified
• Category: MySQL Server: Parser
• Severity: S4 (Feature request)
• Version: 5.0.15
• OS: Windows (Windows 2k)
• CPU Architecture: Any

[3 Nov 2005 11:37] Jari Aalto

Description:
The --ansi option should not allow non-SQL standard conformant operations, like these:

  !
  !=
  &&
  XOR
  
Now it is possible to make queries that do not work in other databases.

How to repeat:
Start server with --ansi option. Following non-standard SQL query succeeds on
a imaginery table:

select * 
from table
where column != 0
;

Suggested fix:
Please modify --ansi to check SQL statement better against strict SQL92 or SQL99 compliance.

[3 Nov 2005 12:11] Valeriy Kravchuk

Thank you for problem report. Yes, ANSI mode does not prevent using '!=':

mysql> desc test;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| c1    | int(11) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
1 row in set (0.08 sec)

mysql> set sql_mode=ANSI;
Query OK, 0 rows affected (0.01 sec)

mysql> select * from test where c1 != 0;
Empty set (0.06 sec)

But is not a bug. ANSI mode is defined as follows (http://dev.mysql.com/doc/refman/5.0/en/server-sql-mode.html):

"ANSI

Change syntax and behavior to be more conformant to standard SQL."

"More conformant", not totally... Moreover, MySQL tend to conform to a much newer SQL 2003 Standard.

So, I think, what you are asking about is a feature request that should be checked by developers.