Bug #68472 TIMESTAMP logic remains partly non-standard with explicit_defaults_for_timestamp
Submitted: 23 Feb 2013 15:45 Modified: 7 Mar 2013 16:25
Reporter: Elena Stepanova Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Data Types Severity:S3 (Non-critical)
Version:5.6.10 OS:Any
Assigned to: CPU Architecture:Any

[23 Feb 2013 15:45] Elena Stepanova
Description:
All descriptions of explicit_defaults_for_timestamp seem to suggest that when the option is ON, the behavior of TIMESTAMP columns in regard to NULLability, defaults and automatic initialization should be the same as for other types. In fact, it's not quite the same. 
If I define a TIMESTAMP column as NOT NULL (without a default value) and then attempt to explicitly insert NULL into it, it still ends up with the current timestamp, while for other types the statement would be rejected. 

Output of the test case from 'How to repeat' section (make sure to run it with --mysqld=--explicit-defaults-for-timestamp=1):

mysql> CREATE TABLE t1 (c TIMESTAMP NOT NULL) ENGINE=InnoDB;
Query OK, 0 rows affected (0.04 sec)

mysql> SHOW COLUMNS IN t1;
+-------+-----------+------+-----+---------+-------+
| Field | Type      | Null | Key | Default | Extra |
+-------+-----------+------+-----+---------+-------+
| c     | timestamp | NO   |     | NULL    |       |
+-------+-----------+------+-----+---------+-------+
1 row in set (0.00 sec)

mysql> SHOW CREATE TABLE t1;
+-------+-------------------------------------------------------------------------------------+
| Table | Create Table                                                                        |
+-------+-------------------------------------------------------------------------------------+
| t1    | CREATE TABLE `t1` (
  `c` timestamp NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+-------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> INSERT INTO t1 (c) VALUES (NULL);
Query OK, 1 row affected (0.01 sec)

mysql> SELECT * FROM t1;
+---------------------+
| c                   |
+---------------------+
| 2013-02-23 19:41:41 |
+---------------------+
1 row in set (0.00 sec)

Compare with the same test for DATETIME:

mysql> CREATE TABLE t1 (c DATETIME NOT NULL) ENGINE=InnoDB;
Query OK, 0 rows affected (0.04 sec)

mysql> SHOW COLUMNS IN t1;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| c     | datetime | NO   |     | NULL    |       |
+-------+----------+------+-----+---------+-------+
1 row in set (0.01 sec)

mysql> SHOW CREATE TABLE t1;
+-------+------------------------------------------------------------------------------------+
| Table | Create Table                                                                       |
+-------+------------------------------------------------------------------------------------+
| t1    | CREATE TABLE `t1` (
  `c` datetime NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> INSERT INTO t1 (c) VALUES (NULL);
ERROR 1048 (23000): Column 'c' cannot be null
mysql> SELECT * FROM t1;
Empty set (0.00 sec)
 

How to repeat:
# Run with 
# --mysqld=--explicit-defaults-for-timestamp=1

CREATE TABLE t1 (c TIMESTAMP NOT NULL) ENGINE=InnoDB;
SHOW COLUMNS IN t1;
SHOW CREATE TABLE t1;
INSERT INTO t1 (c) VALUES (NULL);
SELECT * FROM t1;
DROP TABLE t1;

Suggested fix:
I checked the following manual pages

http://dev.mysql.com/doc/refman/5.6/en/server-system-variables.html#sysvar_explicit_defaul...
http://dev.mysql.com/doc/refman/5.6/en/timestamp-initialization.html
http://dev.mysql.com/doc/refman/5.6/en/upgrading-from-previous-series.html

and none of them helped me to understand whether it's intentional deviation from the standard behavior or an oversight. 
So, at the very least, please document it somewhere; although I suppose that the idea was to make TIMESTAMPs standard, then I guess the logic needs to be fixed.
[25 Feb 2013 9:42] Erlend Dahl
Thank you for the bug report. Verified as described on recent 5.6.
[7 Mar 2013 16:25] Paul Dubois
Noted in 5.6.11, 5.7.1 changelogs.

With explicit_defaults_for_timestamp enabled, inserting NULL into a
TIMESTAMP NOT NULL column inserted the current timestamp. Now
inserting NULL produces an error, as it does for other NOT NULL data
types.
[12 Mar 2013 13:42] Paul Dubois
Revised changelog entry:

With explicit_defaults_for_timestamp enabled, inserting NULL into a
TIMESTAMP NOT NULL column now produces an error (as it already did
for other NOT NULL data types), instead of inserting the current
timestamp.