Bug #44636 sql_mode ORACLE should be more restrictive and contain more options
Submitted: 4 May 2009 9:29 Modified: 5 May 2009 21:05
Reporter: Roland Bouman Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: General Severity:S3 (Non-critical)
Version:all OS:Any
Assigned to: CPU Architecture:Any
Triage: Triaged: D5 (Feature request)

[4 May 2009 9:29] Roland Bouman
Description:
The ORACLE sql_mode is currently defined as being equivalent to:

PIPES_AS_CONCAT, ANSI_QUOTES, IGNORE_SPACE, NO_KEY_OPTIONS, NO_TABLE_OPTIONS, NO_FIELD_OPTIONS, NO_AUTO_CREATE_USER

(http://dev.mysql.com/doc/refman/5.0/en/server-sql-mode.html#sqlmode_oracle)

This is nice but a better fit can be made by also including 

STRICT_ALL_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_BACKSLASH_ESCAPES,NO_ENGINE_SUBSTITUTION,NO_ZERO_DATE,NO_ZERO_IN_DATE'

The how to repeat section proves why ERROR_FOR_DIVISION_BY_ZERO,NO_BACKSLASH_ESCAPES,NO_ENGINE_SUBSTITUTION,NO_ZERO_IN_DATE should be in there.  

Instead of STRICT_ALL_TABLES one could also argue that STRICT_TRANS_TABLES should be included; however it is clear that at least some strict mode should b e enforced for ORACLE sql_mode. The rationale for including NO_ENGINE_SUBSTITUTION is that if I'd want MySQL to behave more like Oracle, I am probably an InnoDB user, and have the default storage engine set to InnoDB. If I normally don't use ENGINE clauses in my CREATE TABLE statements, NO_ENGINE_SUBSTITUTION will keep me from shooting myself in the foot in case something went wrong starting InnoDB.

Technically the ORACLE mode should also include ONLY_FULL_GROUP_BY but this is not advisable because this feature contains a bug (see http://forge.mysql.com/worklog/task.php?id=2489)

How to repeat:
In SQL*plus I had this little talk with Oracle 8.1.7

SQL> select 1/0 from dual;
select 1/0 from dual
        *
ERROR at line 1:
ORA-01476: divisor is equal to zero

(This implies the sql_mode should include ERROR_FOR_DIVISION_BY_ZERO)

SQL> select '\'' from dual;
ERROR:
ORA-01756: quoted string not properly terminated

(This implies the sql_mode should include NO_BACKSLASH_ESCAPES)

SQL> select to_date('0000-00-00', 'YYYY-MM-DD') from dual;
select to_date('0000-00-00', 'YYYY-MM-DD') from dual
               *
ERROR at line 1:
ORA-01843: not a valid month

(This implies the sql_mode should include NO_ZERO_DATE)

SQL> ed
Wrote file afiedt.buf

  1* select to_date('2009-01-00', 'YYYY-MM-DD') from dual
SQL> /
select to_date('2009-01-00', 'YYYY-MM-DD') from dual
               *
ERROR at line 1:
ORA-01847: day of month must be between 1 and last day of month

(This implies the sql_mode should include NO_ZERO_IN_DATE)

The results are identical for Oracle 10G express.

Suggested fix:
Please add suggested options to the ORACLE sql_mode
[4 May 2009 10:01] Valeriy Kravchuk
Thank you for the problem report.
[5 May 2009 21:05] Roland Bouman
Hi Omer,

just curious why this was marked as a feature request. The SQL*Plus session results clearly displays 3 instances of Oracle behaviour that are not included in the ORACLE sql_mode. 

It may not be a serious bug, as there exists a workaround, but surely the current ORACLE sql_mode is not correct? (or not as correct as it should be)
[27 Feb 2012 11:33] gaetano giunta
Another parameter that should be added to Oracle mode is ONLY_FULL_GROUP_BY.
Afaik it should also be added to postrges mode...