MySQL Bugs: #106805: DEFAULT for DATETIME/TIMESTAMP c allows simple expressions rather than ON UPDATE

Bug #106805	DEFAULT for DATETIME/TIMESTAMP c allows simple expressions rather than ON UPDATE
Submitted:	23 Mar 2022 5:06	Modified:	24 Mar 2022 4:30
Reporter:	Владислав Сокол	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: DDL	Severity:	S3 (Non-critical)
Version:		OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	DATETIME TIMESTAMP DEFAULT expression

Description:
While creating autoutilized DATETIME/TIMESTAMP column we may specify simple expression (like "CURRENT_TIMESTAMP + 1 DAY") in ON DEFAULT clause but not in ON UPDATE clause.

This is not mentioned in the Reference Manual (MySQL 8.0 Reference Manual / ...  / Automatic Initialization and Updating for TIMESTAMP and DATETIME, https://dev.mysql.com/doc/refman/8.0/en/timestamp-initialization.html). There is no any explanation or any description of what type of the expression can be used in this clause.

I understand that this usage is too rare. But it is possible, and I think that this must be mentioned in Reference Manual.

How to repeat:
Statements which are can be executed successfully:

CREATE TABLE test (ts DATETIME DEFAULT (CURRENT_TIMESTAMP + INTERVAL 1 DAY));
CREATE TABLE test (ts TIMESTAMP DEFAULT (CURRENT_TIMESTAMP + INTERVAL 1 DAY));

Statements which will fail:

CREATE TABLE test (ts DATETIME ON UPDATE (CURRENT_TIMESTAMP + INTERVAL 1 DAY));
CREATE TABLE test (ts TIMESTAMP ON UPDATE (CURRENT_TIMESTAMP + INTERVAL 1 DAY));

Suggested fix:
not needed

Hello Владислав Сокол,

Thank you for the report and feedback.
IMHO this is duplicate of Bug #103228, please see Bug #103228.

regards,
Umesh

> IMHO this is duplicate of Bug #103228

Do you mean that DEFAULT column option is common attribute which can be applied to any datatype whereas ON UPDATE column option is specific for DATETIME/TIMESTAMP datatype, and it have additional restrictions? Hmm.. from this looking point you're right, it seems. Thanks.