MySQL Bugs: #88650: Virtual stored timestamp field use current time instead of NULL

Bug #88650	Virtual stored timestamp field use current time instead of NULL
Submitted:	25 Nov 2017 0:18	Modified:	29 Nov 2017 14:01
Reporter:	gz —	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S3 (Non-critical)
Version:	5.7.20	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	NOW, null, stored, timestamp

Description:
Virtual stored timestamp field always not null and uses current time if base value is NULL.

How to repeat:
CREATE TABLE `test` (
	`id`           INT UNSIGNED NOT NULL AUTO_INCREMENT,
	`data`         JSON         NULL     DEFAULT NULL,
	`dt_datetime`  DATETIME     AS (JSON_UNQUOTE(`data`->"$.dt")) STORED,
	`dt_timestamp` TIMESTAMP    AS (JSON_UNQUOTE(`data`->"$.dt")) STORED,
	PRIMARY KEY (`id`)
);

INSERT INTO `test` (`data`)
VALUES ('{}');

SELECT * FROM `test`;

+----+------+-------------+---------------------+
| id | data | dt_datetime | dt_timestamp        |
+----+------+-------------+---------------------+
|  1 | {}   | NULL        | 2017-11-25 03:06:54 |
+----+------+-------------+---------------------+
1 row in set (0.00 sec)

Suggested fix:
Use datetime instead of timestamp in this case.

Hello!

Thank you for the report and test case.
Imho, this behavior is controlled by the variable explicit_defaults_for_timestamp i.e this system variable determines whether the server enables certain nonstandard behaviors for default values and NULL-value handling in TIMESTAMP columns. By default, explicit_defaults_for_timestamp is disabled, which enables the nonstandard behaviors. Quoting from manual - "TIMESTAMP columns not explicitly declared with the NULL attribute are automatically declared with the NOT NULL attribute. Assigning such a column a value of NULL is permitted and sets the column to the current timestamp." explained here in details https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_explicit_defau...

===
mysql> show variables like 'explicit%';
+---------------------------------+-------+
| Variable_name                   | Value |
+---------------------------------+-------+
| explicit_defaults_for_timestamp | OFF   |
+---------------------------------+-------+
1 row in set (0.01 sec)

mysql> CREATE TABLE `test` (
    -> `id`           INT UNSIGNED NOT NULL AUTO_INCREMENT,
    -> `data`         JSON         NULL     DEFAULT NULL,
    -> `dt_datetime`  DATETIME     AS (JSON_UNQUOTE(`data`->"$.dt")) STORED,
    -> `dt_timestamp` TIMESTAMP    AS (JSON_UNQUOTE(`data`->"$.dt")) STORED,
    -> PRIMARY KEY (`id`)
    -> );
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> INSERT INTO `test` (`data`)
    -> VALUES ('{}');
Query OK, 1 row affected (0.01 sec)

mysql>
mysql> SELECT * FROM `test`;
+----+------+-------------+---------------------+
| id | data | dt_datetime | dt_timestamp        |
+----+------+-------------+---------------------+
|  1 | {}   | NULL        | 2017-11-29 11:40:34 |
+----+------+-------------+---------------------+
1 row in set (0.00 sec)

mysql> drop table test;
Query OK, 0 rows affected (0.01 sec)

When explicit_defaults_for_timestamp is enabled, the server disables the nonstandard behaviors and handles TIMESTAMP columns as i.e TIMESTAMP columns not explicitly declared with the NOT NULL attribute are automatically declared with the NULL attribute and permit NULL values. Assigning such a column a value of NULL sets it to NULL, not the current timestamp.

mysql> set explicit_defaults_for_timestamp=ON;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE `test` (
    -> `id`           INT UNSIGNED NOT NULL AUTO_INCREMENT,
    -> `data`         JSON         NULL     DEFAULT NULL,
    -> `dt_datetime`  DATETIME     AS (JSON_UNQUOTE(`data`->"$.dt")) STORED,
    -> `dt_timestamp` TIMESTAMP    AS (JSON_UNQUOTE(`data`->"$.dt")) STORED,
    -> PRIMARY KEY (`id`)
    -> );
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> INSERT INTO `test` (`data`)
    -> VALUES ('{}');
Query OK, 1 row affected (0.00 sec)

mysql>
mysql> SELECT * FROM `test`;
+----+------+-------------+--------------+
| id | data | dt_datetime | dt_timestamp |
+----+------+-------------+--------------+
|  1 | {}   | NULL        | NULL         |
+----+------+-------------+--------------+
1 row in set (0.00 sec)

Thanks,
Umesh

Hello, Umesh.

> Imho, this behavior is controlled by the variable explicit_defaults_for_timestamp i.e this system variable determines whether the server enables certain nonstandard behaviors for default values and NULL-value handling in TIMESTAMP columns. By default, explicit_defaults_for_timestamp is disabled, which enables the nonstandard behaviors.

You are right.

Not a bug, "explicit_defaults_for_timestamp = on" fixed this behavour.

Thank you for help and information!