Description:
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:
> CREATE TABLE `t2` (
`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.
Description: 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: > CREATE TABLE `t2` ( `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.