Description:
Compare t0 and t2 output in SHOW CREATE TABLE below. Both tables effectively carry the default expression and it gets evaluated on inserts, but for t2 it is hidden from SHOW CREATE TABLE output.
CREATE TABLE t1 (d DATETIME NOT NULL DEFAULT (DATE_ADD('2020-01-01', INTERVAL 1 DAY)));
CREATE TABLE t0 AS SELECT d FROM t1;
CREATE VIEW v1 AS SELECT d FROM t1;
CREATE TABLE t2 AS SELECT * FROM v1;
SHOW CREATE TABLE t0;
Table Create Table
t0 CREATE TABLE `t0` (
`d` datetime NOT NULL DEFAULT ((_utf8mb4'2020-01-01' + interval 1 day))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
SHOW CREATE TABLE t2;
Table Create Table
t2 CREATE TABLE `t2` (
`d` datetime NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
INSERT INTO t0 () VALUES ();
INSERT INTO t2 () VALUES ();
SELECT * FROM t0;
d
2020-01-02 00:00:00
SELECT * FROM t2;
d
2020-01-02 00:00:00
How to repeat:
MTR:
CREATE TABLE t1 (d DATETIME NOT NULL DEFAULT (DATE_ADD('2020-01-01', INTERVAL 1 DAY)));
CREATE TABLE t0 AS SELECT d FROM t1;
CREATE VIEW v1 AS SELECT d FROM t1;
CREATE TABLE t2 AS SELECT * FROM v1;
SHOW CREATE TABLE t0;
SHOW CREATE TABLE t2;
INSERT INTO t0 () VALUES ();
INSERT INTO t2 () VALUES ();
SELECT * FROM t0;
SELECT * FROM t2;
DROP TABLE t2;
DROP VIEW v1;
DROP TABLE t0;
Description: Compare t0 and t2 output in SHOW CREATE TABLE below. Both tables effectively carry the default expression and it gets evaluated on inserts, but for t2 it is hidden from SHOW CREATE TABLE output. CREATE TABLE t1 (d DATETIME NOT NULL DEFAULT (DATE_ADD('2020-01-01', INTERVAL 1 DAY))); CREATE TABLE t0 AS SELECT d FROM t1; CREATE VIEW v1 AS SELECT d FROM t1; CREATE TABLE t2 AS SELECT * FROM v1; SHOW CREATE TABLE t0; Table Create Table t0 CREATE TABLE `t0` ( `d` datetime NOT NULL DEFAULT ((_utf8mb4'2020-01-01' + interval 1 day)) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci SHOW CREATE TABLE t2; Table Create Table t2 CREATE TABLE `t2` ( `d` datetime NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci INSERT INTO t0 () VALUES (); INSERT INTO t2 () VALUES (); SELECT * FROM t0; d 2020-01-02 00:00:00 SELECT * FROM t2; d 2020-01-02 00:00:00 How to repeat: MTR: CREATE TABLE t1 (d DATETIME NOT NULL DEFAULT (DATE_ADD('2020-01-01', INTERVAL 1 DAY))); CREATE TABLE t0 AS SELECT d FROM t1; CREATE VIEW v1 AS SELECT d FROM t1; CREATE TABLE t2 AS SELECT * FROM v1; SHOW CREATE TABLE t0; SHOW CREATE TABLE t2; INSERT INTO t0 () VALUES (); INSERT INTO t2 () VALUES (); SELECT * FROM t0; SELECT * FROM t2; DROP TABLE t2; DROP VIEW v1; DROP TABLE t0;