Bug #96049 IGNORE clause which doesn't override strict mode
Submitted: 1 Jul 2019 14:09 Modified: 2 Jul 2019 5:42
Reporter: Dmitry Lenev Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: DML Severity:S4 (Feature request)
Version:8.0 OS:Any
Assigned to: CPU Architecture:Any

[1 Jul 2019 14:09] Dmitry Lenev
Description:
When used in INSERT and other DML statements IGNORE clause allows to ignore both duplicate key/foreign key errors and data conversion errors.

In cases when duplicate/foreign key errors are ignored by INSERT IGNORE the
offending row is not inserted into table and thus no inconsistency occurs.

In cases when data conversion errors are ignored the value should be adjusted
to some valid value. But this doesn't happen for datetime values with zero dates. Also problems/inconsistencies are still possible then problematic value is actually based on values in other colums, i.e. when functional indexes or generated columns are used (see bug #95997 for example).

I think it would be nice to have version of IGNORE that would allow to ignore only duplicate key/foreign key errors and not data conversions errors/override strict mode.

How to repeat:
create table t1 (dt datetime);
insert into t1 values ('2001-01-00 00:00:00');
#ERROR 22007: Incorrect datetime value: '2001-01-00 00:00:00' for column 'dt' at row 1
insert ignore into t1 values ('2001-01-00 00:00:00');
#Warnings:
#Warning        1264    Out of range value for column 'dt' at row 1
# Observe inconsitent data
select * from t1;
# dt
# 0000-00-00 00:00:00
[2 Jul 2019 5:42] MySQL Verification Team
Hello Dmitry,

Thank you for the feature request.

regards,
Umesh