WL#7467: Deprecate (5.6) and remove (5.7) ERROR_FOR_DIVISION_BY_ZERO, NO_ZERO_DATE, NO_ZERO_IN_DATE SQL MODES and make their functionality part of STRICT MODE

Status: In-Documentation   —   Priority: Medium

While defining and reimplementing STRICT MODE, it was observed that having large
number of SQL MODES dependent on STRICT MODE creates confusion among users.
The plan is to simplify the SQL MODES which are dependent on STRICT MODE and
make it part of default STRICT MODE.

NOTE: Here STRICT MODE means STRICT_ALL_TABLES and STRICT_TRANS_TABLES SQL Modes.

This would essentially mean that current behavior of STRICT MODE +
ERROR_FOR_DIVISION_BY_ZERO, NO_ZERO_DATE and NO_ZERO_IN_DATE = proposed behavior
of STRICT MODE. 

See 
http://www.tocker.ca/2013/11/01/proposal-to-simplify-sql-modes.html
for community feedback.

This worklog deals with issuing deprecation warnings to users when these modes
are explicitly set and unset and removing them and making part of default STRICT
MODE in 5.7.
F-1: Behavior with no SQL mode set shall be unchanged.

F-2: If only ERROR_FOR_DIVISION_BY_ZERO is set and a DML statement causes
division by zero, the server currently gives warning and completes the DML 
statement by inserting NULL. After this worklog it shall be possible to get the 
same behavior by using STRICT mode and IGNORE.

F-3: If only NO_ZERO_DATE is set and a DML statement inserts a zero date, the 
server currently gives warning and completes the DML statement by inserting 
0000-00-00. After this worklog it shall be possible to get the same behavior by 
using STRICT mode and IGNORE.

F-4: If only NO_ZERO_IN_DATE is set and a DML statement inserts a date 
containing zero, the server currently gives warning and completes the DML 
statement by inserting 0000-00-00. After this worklog it shall be possible to 
get the same behavior by using STRICT mode and IGNORE.

F-5: If both STRICT mode and ERROR_FOR_DIVISION_BY_ZERO is set and a DML
statement causes division by zero, the server currently gives error if IGNORE is 
not used, and gives warning and inserts NULL if IGNORE is used. After this 
worklog, the same behavior will only require STRICT mode.

F-6: If both STRICT mode and NO_ZERO_DATE is set and a DML statement inserts
a zero date, the server currently gives error if IGNORE is not used, and gives 
warning and inserts 0000-00-00 if IGNORE is used. After this worklog, the same 
behavior will only require STRICT mode.

F-7: If both STRICT mode and NO_ZERO_IN_DATE is set and a DML statement inserts
a date containing zero, the server currently gives error if IGNORE is not used, 
and gives warning and inserts 0000-00-00 if IGNORE is used. After this worklog, 
the same behavior will only require STRICT mode.

F-8: If only STRICT mode is set and a DML statement causes division by zero,
inserts a zero date or a date containing zero, the server currently behaves as
if no SQL mode is set. After this worklog, the statements will fail with error
(= behaves as it currently does with STRICT + ERROR_FOR_DIVISION_BY_ZERO |
NO_ZERO_DATE | NO_ZERO_IN_DATE).

F-9: None of the current behavior (no warning, warning, error; value inserted
if any) shall be impossible to achieve after this worklog. (This is a 
consequence of F-1 to F-8).
I-1: Removed ERROR_FOR_DIVISION_BY_ZERO, NO_ZERO_DATE, NO_ZERO_IN_DATE SQL MODES
and made their functionality part of defaullt STRICT MODE. In 5.7, 
SET sql_mode='ERROR_FOR_DIVISON_BY_ZERO'
SET sql_mode='NO_ZERO_DATE'
SET sql_mode='NO_ZERO_IN_DATE'
shall give error "Variable 'sql_mode' can't be set to the value of '<respective
modes>'".

I-2: Deprecation warning. In 5.6, 
SET sql_mode='ERROR_FOR_DIVISON_BY_ZERO'
SET sql_mode='NO_ZERO_DATE'
SET sql_mode='NO_ZERO_IN_DATE'
shall give deprecation warnings as
warning: "'ERROR_FOR_DIVISON_BY_ZERO' is deprecated and will be removed in a
future release."
warning: "'NO_ZERO_DATE' is deprecated and will be removed in a future release."
warning: "'NO_ZERO_IN_DATE' is deprecated and will be removed in a future release."
respectively.
The functionality shall be unchanged.

NOTES ON LIVE UPGRADE OR UPGRADE USING DUMP/RESTORE:
====================================================
After the implementation of WL#7467, it was observed that SQL_MODE started
mapping to different value of SQL_MODE in triggers. For more infomation, please
look at Bug#18311187.

SQL_MODE for triggers is stored in .TRG files at trigger creation time. If the
user upgraded to the version in which WL#7467 was pushed, the value of SQL_MODES
in .TRG files started mapping to the new values SQL_MODES currently available
which was not desired and might lead to wrong behaviour of triggers after upgrade. 

To solve this upgrade issue, the removed SQL_MODES are again introduced for
backward compatibility during upgrade.

However, The point of the WL still remains. The functionality of NO_ZERO_DATE,
NO_ZERO_IN_DATE and ERROR_FOR_DIVISION_BY_ZERO is merged with functionality of
STRICT MODE and all the functional requirements of the Worklog are still valid.

This can be an added functional requirement that Setting NO_ZERO_DATE,
NO_ZERO_IN_DATE and ERROR_FOR_DIVISION_BY_ZERO in 5.7 will have no effect and
user will get a warning if he tries to explicitly set these modes.

Some notes regarding system tables and SHOW CREATE:
===================================================
For stored routines that were created in 5.6 with NO_ZERO_* modes, after upgrade
to 5.7, these modes will *not* show up in the output of SHOW CREATE
PROC/FUNC/EVENTS/TRIGGERS, and SELECT * FROM information_schema.triggers.
Since upgrade using mysqldump uses SHOW CREATE statements to recreate/restore
the database objects, users will not be affected and we are good here.

Only Select * from mysql.proc and mysql.events table will show the NO_ZERO_*
modes after upgrade for the procs/events that were created in 5.6.

In 5.7, since we cannot use NO_ZERO_* modes, mysql.proc/mysql.events or even
SHOW CREATE will not show any of NO_ZERO_* Modes. 

These modes can be completely removed in 5.8.