MySQL Bugs: #105829: DEFAULT(timestamp_col) is different from DEFAULT for timestamp columns (NULL)

Bug #105829	DEFAULT(timestamp_col) is different from DEFAULT for timestamp columns (NULL)
Submitted:	8 Dec 2021 10:52	Modified:	8 Dec 2021 16:10
Reporter:	Mattias Jonsson	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S3 (Non-critical)
Version:	8.0.27	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
Using 'DEFAULT(timestamp_column)' for timestamp/datetime is not consistent with other column types
I would expect that for INSERT/UPDATE/SELECT etc that it would treat DEFAULT(column_name) the same as DEFAULT in:
INSERT INTO t VALUES (DEFAULT),(DEFAULT(column))

How to repeat:
create table t (a int primary key, b timestamp default now(), c int default 10);
insert into t values (1,default,default),(2,default(b),default(c));
select *, default(b), default(c) from t;

Results in:
+---+---------------------+------+------------+------------+
| a | b                   | c    | default(b) | default(c) |
+---+---------------------+------+------------+------------+
| 1 | 2021-12-08 11:38:49 |   10 | NULL       |         10 |
| 2 | NULL                |   10 | NULL       |         10 |
+---+---------------------+------+------------+------------+
2 rows in set (0,00 sec)

I would expect INSERT to use the default value instead of NULL. Same with SELECT DEFAULT(b), just like it does with an int column.

Suggested fix:
Make DEFAULT(column_of_type_timestamp_or_datetime) behaves just like DEFAULT(column_of_other_type).

Hi Mr. Jonsson,

Thank you for your bug report.

However, this is not a bug.

You are quite right that DEFAULT with braces should return defined value, but that is not valid for TIMESTAMP / DATETIME domains, as explained in our Reference Manual, section 11.2.5.

Not a bug.

Thanks for the explanation, but I cannot see any references about DEFAULT(timestamp_col) in the manual section 11.2.5.

But I could find a part that may be appropriate in 11.6:
"However, the use of DEFAULT(col_name) to specify the default value for a named column is permitted only for columns that have a literal default value, not for columns that have an expression default value."

Which is clear with this example:
create table t (a int primary key, b int default (3 + 2));

insert into t (a) values (1);
mysql> select default(b) from t;
ERROR 3773 (HY000): DEFAULT function cannot be used with default value expressions

But then I would expect an error when doing it with 'b timestamp default current_timestamp' too, and not silently insert NULL for the second row in the 'How to repeat' section!