Bug #94550 generated columns referring to current_timestamp fail
Submitted: 4 Mar 2019 16:18 Modified: 4 Mar 2019 18:47
Reporter: Mario Beck Email Updates:
Status: Verified Impact on me:
Category:MySQL Server: DML Severity:S3 (Non-critical)
Version:8.0.15 OS:Oracle Linux (OL7)
Assigned to: CPU Architecture:x86
Tags: current_timestamp, generated columns, timestamp

[4 Mar 2019 16:18] Mario Beck
I have a generated column that is calculated from a timestamp column, which in turn defaults to current_timestamp.
If the generated column is STORED and defined NOT NULL, I can not insert rows.

How to repeat:
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `content` varchar(42) DEFAULT NULL,
  `bucket` tinyint(4) GENERATED ALWAYS AS ((floor((to_seconds(`created_at`) / 10)) % 3)) STORED NOT NULL);

> INSERT INTO t2 (content) VALUES ("taraaaa");

ERROR: 1048 (23000): Column 'bucket' cannot be null

> INSERT INTO t2 (created_at, content) VALUES (NULL, "taraaaa");
Query OK, 1 row affected (0.0062 sec)

(Before this test I have set explicit_defaults_for_timestamp to OFF. But this is deprecated!)

If the generated column is not STORED or not "NOT NULL", it works fine. But in my case the generated column will be part of the primary key, so I need STORED NOT NULL.

Suggested fix:
No idea. But it should work correctly.
[4 Mar 2019 18:47] MySQL Verification Team
Thank you for the bug report.