WL#9687: Change default for explicit_defaults_for_timestamp to ON

In MySQL 5.6 an option for explicit_defaults_for_timestamp was introduced (and 
deprecated) with the intention of suggesting users to change their habits - 
WL#6292.

In most cases though, users just ignore the warnings from 
explicit_defaults_for_timestamp.

It makes more sense to change the default to TRUE, with users able to revert back 
to the FALSE behavior for one major version.  This means removal in 9.0.

Related future work:
* Remove explicit-defaults-for-timestamp: command line option and global 
  variable
* Remove explicit_defaults_for_timestamp: session variable

User Documentation
==================

* https://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-2.html
*
https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_explicit_defaults_for_timestamp
* https://dev.mysql.com/doc/refman/8.0/en/timestamp-initialization.html

* Functional requirements

FR1. Default value of variable 'explicit_defaults_for_timestamp' at server 
     start should be : 1

FR2. Replication shall not break - neither same-version or
     cross-version, and not in chained topologies.  In other words,
     when user does provide a value for
     explicit_defaults_for_timestamp, table definitions that depend on
     explicit_defaults_for_timestamp shall be the same on all nodes in
     the topology.

     There is a framework already which preserves
     explicit_defaults_for_timestamp throughout the replication
     topology.  Therefore, this is expected to work without any code
     change.  The following should be tested to cover all cases:

     - 8.0 -> 8.0
     - 5.7 -> 8.0 -> 8.0
     - 8.0 -> 5.7 -> 5.7 [not officially supported, but works unless
       there is a bug in the existing framework]

Introduction
------------

explicit_defaults_for_timestamp is a global dynamic variables introduced in
scope of WL#6292 to make the behavior of timestamp standard. The default value
of the variable is FALSE. This worklog will change the default value of 
explicit_defaults_for_timestamp to TRUE. Test cases will be changed to execute
with the new default behavior.

Abbreviation : EDFT : explict_defaults_for_timestamp


2. Statements affected by explicit_defaults_for_timestamp
---------------------------------------------------------
Though the above pages mentions the differences in behavior with different
values of explicit_defaults_for_timestamp, we will highlight the difference in
behavior with respect to statements and discuss the effect of change in default
behavior with respect to version replication.


2.1. DDL - CREATE statements :
-----------------------------

i> One timestamp column with default sql_mode:

  CREATE TABLE t21 (a timestamp);
  INSERT INTO t21 VALUES(); 
  INSERT INTO t21 VALUES(NULL);

  Case : explicit_defaults_for_timestamp= FALSE
         NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP is 
         assigned to the column by default. Above statements passes, inserts 
         current_timestamp.

  Case : explicit_defaults_for_timestamp= TRUE 
         column gets 'default NULL'. NULL is inserted into the table.


ii> Two timestamp columns with default sql_mode:

    CREATE TABLE t22 (a TIMESTAMP  , b TIMESTAMP);

    Case : explicit_defaults_for_timestamp= FALSE
           Statements fails with error - Invalid default value for 'b'
           Statement passes if NO_ZERO_DATE sql_mode is relaxed.

    Case : explicit_defaults_for_timestamp= TRUE 
           Statement passes, creates table with 'default NULL' for both 
           timestamp columns.

iii> Explicit 'default NULL' (behavior is independent of sql_mode).

     CREATE TABLE t50(a TIMESTAMP DEFAULT NULL);

     Case : explicit_defaults_for_timestamp= FALSE
            Statements fails with error - Invalid default value for 'a'

     Case : explicit_defaults_for_timestamp= TRUE 
            Statement passes, creates table with 'default NULL' for timestamp 
            column.
 

2.2.DDL - ALTER statement :
--------------------------

ALTER will follow the same patters as CREATE statements.
For example with default sql_mode(STRICT + NO_ZERO_DATE):


  CREATE TABLE t51(a TIMESTAMP);
  ALTER TABLE t51 ADD COLUMN b TIMESTAMP;

  Case : explicit_defaults_for_timestamp= FALSE
         ALTER fails with error : Invalid default value for 'b'
     
  Case : explicit_defaults_for_timestamp= TRUE 
         ALTER passes.
  
2.3.DML - INSERT / UPDATEs :
---------------------------

While table is created only once, DML can keep on happening.
It is possible to create same table structure irrespective of the value of 
explicit_defaults_for_timestamp.
Even in this case, INSERT will depend on the value of
explicit_defaults_for_timestamp.
Consider the case :

i> SQL mode= default

  CREATE TABLE `t100` (`a` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON 
                                     UPDATE CURRENT_TIMESTAMP);
  INSERT INTO t100 VALUES(NULL);

  Case : explicit_defaults_for_timestamp= FALSE
         INSERT passes, enters current timestamp to the table.

     
  Case : explicit_defaults_for_timestamp= TRUE 
         INSERT fails with error :  Column 'a' cannot be null

  The case is similar with other DML statements ( UPDATE t100 SET a=NULL; )

  ii> sql_mode=''

  SET sql_mode='';
  CREATE TABLE `t100` (`a` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON 
                                     UPDATE CURRENT_TIMESTAMP);
  INSERT INTO t100 VALUES(NULL), (NULL);

  Case : explicit_defaults_for_timestamp= FALSE
         Inserts current timestamp values for both rows.

     
  Case : explicit_defaults_for_timestamp= TRUE 
         Inserts '0000-00-00 00:00:00' for both rows.
  

3 Effect on Replication :
-------------------------

3.1. Replication between two new servers or cross version replication should 
     work provided that both have the same value of 
     --explicit-defaults-for-timestamp start up parameter.

3.2. Effect on replication with two servers having different value of
     explicit-defaults-for-timestamp :

     There are 2 cases to consider here :
     i>  Master : EDFT = 0            Slave  : EDFT= 1
     ii> Master : EDFT = 1            Slave  : EDFT= 0

    Replication should not break in both the cases.

4. Deprecation Warning :
------------------------

Whenever the value of explicit_defaults_for_timestamp is changed from TRUE to
FALSE, user should get the deprecation warning :

'explicit_defaults_for_timestamp' is deprecated and will be removed in a future
release.

5. Fix of test cases:
----------------------

The test cases have been fixed to work with new default behavior.

Any test specific explanation will be explicitly mentioned.
The fix mostly uses one of the following reasons. 
i>   Record new default value
ii>  Record non promotion of timestamp columns.
iii> Timestamp columns are now nullable by default and no auto promotion 
     happens. Record test output.
iv>  Test tried to insert ZERO date in NO_ZERO_DATE mode. This is not allowed 
     for timestamp column by default.
v>   Fix sql mode to test the intended behavior of the test and record result 
     file.
vi>  Error scenario to check table creation with 2 timestamp column fails with 
     NO_ZERO_DATE. Deprecated behavior. Remove the test scenario.
vii> Extra warning is there when trying to insert NULL to TIMESTAMP NOT NULL 
     column. Value inserted in the table changed from current_timestamp value to 
     '0000-00-00 00:00:00'. Recorded the result file.