Status: Open (View Workflow)
Affects Version/s: 10.5.8
Fix Version/s: None
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:
Now the following ALTER TABLE will fail, despite that it does not touch column 'a'.
This is especially apparent in versioned tables. Both these situations will cause error code 1067:
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.