Description:
A CHECK constraint compares a DATETIME column with 0: CHECK (c = 0). CREATE TABLE stores it as written, (c = 0). ALTER TABLE operations such as ADD INDEX,
ADD COLUMN ... ALGORITHM=INSTANT and FORCE store (c = '0000-00-00 00:00:00') instead.
In the default sql_mode this makes the ALTER fail with ERROR 1292, even on an empty
table. If the ALTER is run under a sql_mode without NO_ZERO_DATE, the table can no
longer be read in the default sql_mode (ERROR 1525). After a server restart it cannot
be dropped either, until sql_mode is changed.
Generated columns and expression DEFAULTs that compare with 0 are rewritten the
same way.
Expected: ALTER TABLE keeps (`c` = 0), as CREATE TABLE does.
How to repeat:
CREATE TABLE t (c DATETIME, d INT, CHECK (c = 0));
SHOW CREATE TABLE t; -- CONSTRAINT `t_chk_1` CHECK ((`c` = 0))
ALTER TABLE t ADD INDEX (d);
-- ERROR 1292 (22007): Incorrect datetime value: '0000-00-00 00:00:00' for column 'c' at row 1
SET SESSION sql_mode = 'STRICT_TRANS_TABLES';
ALTER TABLE t ADD INDEX (d); -- OK
SHOW CREATE TABLE t; -- CONSTRAINT `t_chk_1` CHECK ((`c` = '0000-00-00 00:00:00'))
FLUSH TABLES;
SET SESSION sql_mode = DEFAULT;
SELECT * FROM t; -- ERROR 1525 (HY000): Incorrect DATETIME value: '0000-00-00 00:00:00'
