MySQL Bugs: #86019: explicit_defaults_for_timestamp and timestamp NOT NULL DEFAULT CURRENT

Bug #86019	explicit_defaults_for_timestamp and timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
Submitted:	20 Apr 2017 11:44	Modified:	14 Jun 2017 13:35
Reporter:	Søren Thing Andersen	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S3 (Non-critical)
Version:	5.6.35	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
https://dev.mysql.com/doc/refman/5.6/en/timestamp-initialization.html describes how NULL can be used to set/update a timestamp to the current time.

However setting the server variable explicit_defaults_for_timestamp=1 breaks this behaviour.

With explicit_defaults_for_timestamp=0:
It works as described.
Issuing the commands below yields:
mysql> SELECT @@explicit_defaults_for_timestamp, @@sql_mode;
+-----------------------------------+------------------------+
| @@explicit_defaults_for_timestamp | @@sql_mode             |
+-----------------------------------+------------------------+
|                                 0 | NO_ENGINE_SUBSTITUTION |
+-----------------------------------+------------------------+
1 row in set (0,00 sec)

mysql> CREATE TABLE t1(c1 timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP);
Query OK, 0 rows affected (0,03 sec)

mysql> INSERT INTO t1 VALUES (); -- Works as expected
Query OK, 1 row affected (0,01 sec)

mysql> INSERT INTO t1 VALUES (NULL); -- Fails for explicit_defaults_for_timestamp=1
Query OK, 1 row affected (0,00 sec)

mysql> SELECT * FROM t1;
+---------------------+
| c1                  |
+---------------------+
| 2017-04-20 13:34:42 |
| 2017-04-20 13:34:43 |
+---------------------+
2 rows in set (0,01 sec)

mysql> UPDATE t1 SET c1=NULL;
Query OK, 0 rows affected (0,00 sec)
Rows matched: 2  Changed: 0  Warnings: 0

mysql> SELECT * FROM t1;
+---------------------+
| c1                  |
+---------------------+
| 2017-04-20 13:34:44 |
| 2017-04-20 13:34:44 |
+---------------------+
2 rows in set (0,00 sec)

With explicit_defaults_for_timestamp=1:
An INSERT statement fails with ERROR 1048 (23000): Column 'c1' cannot be null.
An UPDATE statements sets the value to '0000-00-00 00:00:00'.
Issuing the commands below yields:
mysql> SELECT @@explicit_defaults_for_timestamp, @@sql_mode;
+-----------------------------------+------------------------+
| @@explicit_defaults_for_timestamp | @@sql_mode             |
+-----------------------------------+------------------------+
|                                 1 | NO_ENGINE_SUBSTITUTION |
+-----------------------------------+------------------------+
1 row in set (0.00 sec)

mysql> CREATE TABLE t1(c1 timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP);
Query OK, 0 rows affected (0.04 sec)

mysql> INSERT INTO t1 VALUES (); -- Works as expected
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO t1 VALUES (NULL); -- Fails for explicit_defaults_for_timestamp=1
ERROR 1048 (23000): Column 'c1' cannot be null
mysql> SELECT * FROM t1;
+---------------------+
| c1                  |
+---------------------+
| 2017-04-20 11:39:45 |
+---------------------+
1 row in set (0.00 sec)

mysql> UPDATE t1 SET c1=NULL;
Query OK, 1 row affected, 1 warning (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 1

mysql> SELECT * FROM t1;
+---------------------+
| c1                  |
+---------------------+
| 0000-00-00 00:00:00 |
+---------------------+
1 row in set (0.00 sec)

How to repeat:
SELECT @@explicit_defaults_for_timestamp, @@sql_mode;
CREATE TABLE t1(c1 timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP);
INSERT INTO t1 VALUES (); -- Works as expected
INSERT INTO t1 VALUES (NULL); -- Fails for explicit_defaults_for_timestamp=1
SELECT * FROM t1;
UPDATE t1 SET c1=NULL; -- Sets value to 0000-00-00 00:00:00 and issues warning.
SELECT * FROM t1;

Suggested fix:
Allow using NULL to set current timestamp for columns that specify "NOT NULL" as described at https://dev.mysql.com/doc/refman/5.6/en/timestamp-initialization.html.

Version corrected, 5.6.36 => 5.6.35

HI!

This is not a bug. As our manual nicely puts it out:

As indicated by the warning, to turn off the nonstandard behaviors, enable the new
explicit_defaults_for_timestamp [501] system variable at server startup. With this
variable enabled, the server handles TIMESTAMP as follows instead:
• TIMESTAMP columns not explicitly declared as NOT NULL permit NULL values. Setting such a
column to NULL sets it to NULL, not the current timestamp.

Your timestamp is explicitly set as NOT NULL.

Dear Sinisa Milivojevic,

I respectfully disagree.

I am aware of the new explicit_defaults_for_timestamp variable, and agree that it is good to require explicit defaults.

> > TIMESTAMP columns not explicitly declared as NOT NULL permit NULL values.
> > Setting such a column to NULL sets it to NULL, not the current timestamp.
> 
> Your timestamp is explicitly set as NOT NULL.

Yes, exactly. And the documentation you cite only explains what happens when the column is NOT explicitly declared as NOT NULL.

There is no reason that the behaviour for explicit NOT NULL columns cannot be kept as described in 
https://dev.mysql.com/doc/refman/5.6/en/timestamp-initialization.html:
> ... and assigning NULL assigns the current timestamp.

I urge you to reconsider this as a bug.
The flag explicit_defaults_for_timestamp should only change the column defaults.
It should NOT change the behaviour for INSERTs and UPDATEs once the NULLability and defaults have been resolved.

If you keep the position that it is not a bug, please update https://dev.mysql.com/doc/refman/5.6/en/timestamp-initialization.html to explicitly state, that "assigning NULL assigns the current timestamp" is not possible with explicit_defaults_for_timestamp enabled.

Best regards,
Søren Thing

Actually, I agree. Documentation could be more explicit and clear on this issue.

Verified as documentation bug.

Posted by developer:
 
https://dev.mysql.com/doc/refman/5.6/en/timestamp-initialization.html updated (see end of section):

If the explicit_defaults_for_timestamp system variable is enabled, TIMESTAMP columns permit NULL values only if declared with the NULL attribute. Also, TIMESTAMP columns do not permit assigning NULL to assign the current timestamp, whether declared with the NULL or NOT NULL attribute. To assign the current timestamp, set the column to CURRENT_TIMESTAMP or a synonym such as NOW().