[#MDEV-19611] INPLACE ALTER does not fail on bad implicit default value

In all below scripts we use ALTER for a table that:

is not empty
adds a new DATE NOT NULL field with no implicit default, so '0000-00-00' is implied as a default
set sql_mode in the way to disallow zero dates

Such ALTER should fail, because new fields in all existing rows should be assigned to '0000-00-00', which is not allowed.

It fails with MyISAM:

DROP TABLE IF EXISTS t1;

CREATE TABLE t1 (a DATE) ENGINE=MyISAM;

INSERT INTO t1 VALUES (now());

SET sql_mode='STRICT_ALL_TABLES,STRICT_TRANS_TABLES,NO_ZERO_DATE';

ALTER TABLE t1 ADD b DATE NOT NULL;

ERROR 1292 (22007): Incorrect date value: '0000-00-00' for column `test`.`t1`.`b` at row 1

It fails with INNODB, using COPY:

DROP TABLE IF EXISTS t1;

CREATE TABLE t1 (a DATE) ENGINE=INNODB;

INSERT INTO t1 VALUES (now());

SET sql_mode='STRICT_ALL_TABLES,STRICT_TRANS_TABLES,NO_ZERO_DATE';

ALTER TABLE t1 ALGORITHM=COPY, ADD b DATE NOT NULL;

It does not fail with INNODB, using INPLACE:

DROP TABLE IF EXISTS t1;

CREATE TABLE t1 (a DATE) ENGINE=INNODB;

INSERT INTO t1 VALUES (now());

SET sql_mode='STRICT_ALL_TABLES,STRICT_TRANS_TABLES,NO_ZERO_DATE';

ALTER TABLE t1 ALGORITHM=INPLACE, ADD b DATE NOT NULL;

Query OK, 0 rows affected (0.007 sec)

Records: 0  Duplicates: 0  Warnings: 0

Notice, it silently performed the ALTER without any warnings.

[MDEV-19611] INPLACE ALTER does not fail on bad implicit default value Created: 2019-05-28 Updated: 2021-08-23 Resolved: 2020-04-28
Status:	Closed
Project:	MariaDB Server
Component/s:	Storage Engine - InnoDB
Affects Version/s:	10.3, 10.4, 10.5
Fix Version/s:	10.3.23, 10.4.13, 10.5.3