The sql_mode variable NO_ZERO_DATE specifies to reject any INSERT and UPDATE queries where a date value is submitted as '0000-00-00'. Though if a new column is added to a table, then the value '0000-00-00' is still inserted for all rows. It is a session variable and not an attribute of the table column, which causes problems if the sql mode is entered after a table has been created. For example:

1:

CREATE TABLE abc (a DATE NOT NULL DEFAULT '0000-00-00');

Now the following ALTER TABLE will fail, despite that it does not touch column 'a'.

SET SESSION sql_mode='NO_ZERO_DATE';

ALTER TABLE abc ADD b INT NOT NULL;

This is especially apparent in versioned tables. Both these situations will cause error code 1067:

2:

SET SESSION sql_mode='NO_ZERO_DATE';

CREATE TABLE abc (a INT NOT NULL) WITH SYSTEM VERSIONING;

3:

CREATE TABLE abc (a INT NOT NULL) WITH SYSTEM VERSIONING;

SET SESSION system_versioning_alter_history=KEEP, SESSION sql_mode='NO_ZERO_DATE';

ALTER TABLE abc ADD b INT NOT NULL;

Given that '0000-00-00' values can still occur in a table, despite that NO_ZERO_DATE may at one point in time become configured, I think that a column's default value should not be validated against this restriction, especially when a table alteration does not even affect that column. Perhaps an even better solution would be to make NO_ZERO_DATE an attribute of the column, rather than a session variable.