[MDEV-8373] Zero date can be inserted in strict no-zero mode through CREATE TABLE AS SELECT timestamp_field - Jira

XML

Word

Printable

set sql_mode=default;

drop table if exists t1;

create table t1 (a timestamp);

insert into t1 values (0);

set sql_mode='TRADITIONAL';

drop table if exists t2;

create table t2 as select * from t1;

The last statement should fail on error, because zero date is not allowed by sql_mode.

Note, if I change data time from TIMESTAMP to DATE or DATETIME, it correctly returns the error:

ERROR 1292 (22007): Incorrect datetime value: '0000-00-00 00:00:00' for column 'a' at row 1