Bug #121307 CTAS through temp table VIEW resets TIMESTAMP DEFAULT

• Submitted: 17 Sep 11:06
• Reporter: Laurynas Biveinis (OCA)
• Status: Open
• Category: MySQL Server: DDL
• Severity: S3 (Non-critical)
• Version: 8.4.11, 9.7.2, 26.7.0
• OS: Any
• CPU Architecture: Any

[17 Sep 11:06] Laurynas Biveinis

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;