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