WL#8596: Turn STRICT_MODE submodes ON by Default

Affects: Server-Prototype Only — Status: Complete

WL#7467 removed ERROR_FOR_DIVISION_BY_ZERO, NO_ZERO_DATE, NO_ZERO_IN_DATE SQL
MODES and make their functionality part of STRICT MODE. The plan was to simplify
the SQL MODES which are dependent on STRICT MODE.

As a result, more checks got added to STRICT mode. For 5.6 users, the statements
which used to pass in STRICT mode now fails. Reference: BUG#75439
This worklog aims to revert the semantic change to strict mode done by WL#7467.

We want to remove ERROR_FOR_DIVISION_BY_ZERO, NO_ZERO_DATE, NO_ZERO_IN_DATE SQL
modes in the future. In this reference, this worklog will
- Revert semantic change to strict mode
- Make NO_ZERO_DATE, NO_ZERO_IN_DATE and ERROR_FOR_DIVISION_BY_ZERO sql modes 
  default
- Add appropriate warning message in 5.7.

User Documentation

* https://dev.mysql.com/doc/refman/5.7/en/sql-mode.html#sqlmode_no_zero_date
* https://dev.mysql.com/doc/refman/5.7/en/sql-mode.html#sqlmode_no_zero_in_date
*
https://dev.mysql.com/doc/refman/5.7/en/sql-mode.html#sqlmode_error_for_division_by_zero
* https://dev.mysql.com/doc/refman/5.7/en/sql-mode.html#sql-mode-strict

Functional Requirements
------------------------

F1. NO_ZERO_DATE, NO_ZERO_IN_DATE, ERROR_FOR_DIVISION_BY_ZERO mode
added to the default set (compiled-in) of the sql_mode global variable.

F2. If any of the NO_ZERO_DATE, NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO
or strict mode is used but not all four, server will report warning.

F3. If ERROR_FOR_DIVISION_BY_ZERO is set without STRICT mode and a statement
causes division by zero, the server will give warning and complete the statement.

F4. If ERROR_FOR_DIVISION_BY_ZERO is set with STRICT mode and a statement causes
division by zero, the server will abort the statement with error.

F5. If NO_ZERO_DATE is set without STRICT mode and a statement inserts a zero
date, the server will give warning and complete the statement.

F6. If NO_ZERO_DATE is set with STRICT mode and a statement inserts a zero date,
the server will abort the statement with error.

F7. If NO_ZERO_IN_DATE is set without STRICT mode  and a statement inserts date 
containing zero, the server will give warning and complete the statement.

F8. If NO_ZERO_IN_DATE is set with STRICT mode  and a statement inserts date 
containing zero, the server will abort the statement with error.

This worklog will revert changes done by WL#7467, and bring back NO_ZERO_DATE,
NO_ZERO_IN_DATE, ERROR_FOR_DIVISION_BY_ZERO sql mode functionalities.
These modes will be added to set of default sql modes.

WL#7467 changed strict mode by merging NO_ZERO_DATE, NO_ZERO_IN_DATE and
ERROR_FOR_DIVISION_BY_ZERO sql modes to strict mode. This added more checks to
strict mode. Statements which used to pass in mysql-5.6 with warning in strict
mode, started to fail in mysql-5.7. Statements which pass on mysql-5.6 master
fails on mysql-5.7 slave.

In mysql-5.6 in NO_ZERO_DATE sql mode, statements which inserts '0000-00-00'
date value gives warning. NO_ZERO_DATE + strict mode converts this warning to
error. This worklog will make mysql-5.7 mimic same behaviour. Same applies to
NO_ZERO_IN_DATE and ERROR_FOR_DIVISION_BY_ZERO sql modes.

Change in default SQL mode
--------------------------
Before this worklog, set of default sql modes consisted of 
      ONLY_FULL_GROUP_BY, STRICT_TRANS_TABLES, 
      NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

This worklog will add NO_ZERO_DATE, NO_ZERO_IN_DATE and
ERROR_FOR_DIVISION_BY_ZERO sql modesto set of default sql modes, making default
set as
      ONLY_FULL_GROUP_BY, STRICT_TRANS_TABLES, NO_ZERO_IN_DATE, NO_ZERO_DATE, 
      ERROR_FOR_DIVISION_BY_ZERO, NO_AUTO_CREATE_USER, NO_ENGINE_SUBSTITUTION

Warning message
----------------
Warning message when one or more of the NO_ZERO_DATE, NO_ZERO_IN_DATE,
ERROR_FOR_DIVISION_BY_ZERO or strict mode is SET but not all four.

'NO_ZERO_DATE', 'NO_ZERO_IN_DATE' and 'ERROR_FOR_DIVISION_BY_ZERO' sql modes
should be used with strict mode. They will be merged with strict mode in a
future release.

If sql_mode is changed by user using SET command, user will get warning message.
If sql_mode is changed at startup using server configuration option --sql_mode
or from .cnf file, warning message will be logged in error log.


mysql-5.6 already gives a deprecation warning for 'NO_ZERO_DATE',
'NO_ZERO_IN_DATE' and 'ERROR_FOR_DIVISION_BY_ZERO' sql modes from WL#7467.
The point of the new warning is that it more accurately reports the
configuration we would like to deprecated. So that it will be easier to remove
these submodes again later.


Changes in Test cases:
-----------------------
WL#7467 had removed and modified test cases dealing with NO_ZERO_IN_DATE,
NO_ZERO_DATE and ERROR_FOR_DIVISION_BY_ZERO. These tests will be added back to
tests 
 - i_main.bug16078943.test   : Tests DDL and DMLS with ZERO date
 - i_main.bug16078943_1.test : Tests DDL and DMLS with ZERO date
 - main.alter_table.test     : Tests DDL with ZERO date
 - main.create.test          : Tests DDL with ZERO date
 - main.insert.test          : Tests DML for ERROR_FOR_DIVISION_BY_ZERO
 - main.strict.test          : Tests DML for ZERO in date, ZERO date
 - main.type_date.test       : Tests DML for ZERO in date, ZERO date
 - main.type_datetime.test   : Tests DML for ZERO in date, ZERO date
 - main.type_temporal_fraction.test  : Tests DML for ZERO in date
 - main.type_timestamp.test  : Tests DDL for ZERO in date, ZERO date
 - main.type_timestamp_explicit.test  : Tests DDL for ZERO in date, ZERO date

Changes in unit tests field_date-t.cc and field_datetime-t.cc will be reverted.