Bug #121307 CTAS through temp table VIEW resets TIMESTAMP DEFAULT

Submitted:	17 Sep 11:06
Reporter:	Laurynas Biveinis (OCA)	Email Updates:
Status:	Open	Impact on me:	None
Category:	MySQL Server: DDL	Severity:	S3 (Non-critical)
Version:	8.4.11, 9.7.2, 26.7.0	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
CREATE TABLE t1 (ts TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP);

# Works
CREATE TABLE t2 AS SELECT ts FROM t1;

# Works
CREATE ALGORITHM=MERGE VIEW v1 AS SELECT ts FROM t1;
CREATE TABLE t3 AS SELECT ts FROM v1;

# Fails
CREATE ALGORITHM=TEMPTABLE VIEW v2 AS SELECT ts FROM t1;
CREATE TABLE t4 AS SELECT ts FROM v4;

Query: 'CREATE TABLE t4 AS SELECT ts FROM v1'
Returned error: ER_INVALID_DEFAULT(1067) (42000): Invalid default value for 'ts'
Warnings from just before the error:
Error 1292 Incorrect datetime value: '0000-00-00 00:00:00' for column 'ts' at row 1

How to repeat:
CREATE TABLE t1 (ts TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP);

# Works
CREATE TABLE t2 AS SELECT ts FROM t1;

# Works
CREATE ALGORITHM=MERGE VIEW v1 AS SELECT ts FROM t1;
CREATE TABLE t3 AS SELECT ts FROM v1;

# Fails
CREATE ALGORITHM=TEMPTABLE VIEW v2 AS SELECT ts FROM t1;
CREATE TABLE t4 AS SELECT ts FROM v4;

Page generated in 0.047 sec. using MySQL 8.4.7-u6-cloud

Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.