MySQL Bugs: #115424: Table created by the CREATE TABLE statement does not match the table definition

Bug #115424	Table created by the CREATE TABLE statement does not match the table definition
Submitted:	25 Jun 7:55	Modified:	25 Jun 10:17
Reporter:	Xizhe Zhang (OCA)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: DDL	Severity:	S3 (Non-critical)
Version:	8.0	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	CREATE TABLE, timestamp

Description:
When sql_mode is 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION', the first timestamp field's definition will be different from the definition in the CREATE TABLE statement.

How to repeat:
set sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';

CREATE TABLE `t1` (
`time1` timestamp,
`time2` timestamp,
`time3` timestamp
) ENGINE=InnoDB;

show create table t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `time1` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `time2` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `time3` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3
1 row in set (0.00 sec)

You can see that the definition of the field time1 has become:
`time1` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
which I think is not correct.

I found this behavior documented, it's not a bug.
https://dev.mysql.com/doc/refman/8.0/en/data-type-defaults.html

Hi Mr. Zhang,

Thank you very much for your bug report.

We agree with you.

This is truly documented behaviour and it is not a bug.

Thanks again.