Unspecified and inconsistent behavior of CHECK constraint requiring cast

Quite possibly it's just a documentation issue (it must be at least a documentation issue, as I can't find anything in the KB that would shed any light on it; it is not surprising as the INSERT IGNORE information in the KB is scarce even in regard to the legacy logic). However, I can't request a documentation change because I don't know what the expected behavior for CHECK constraints is.

The test case is degenerate, but obviously there are more realistic paths which lead to the same problem.

create or replace table t (a int, check (a!='')) engine=MyISAM;

insert ignore into t values (1);

show warnings;

select * from t;

alter table t force;

"Normally" INSERT IGNORE does one of two things: either it converts the inserted values into closest valid ones (e.g. with data type mismatch), or it skips the rows which cannot be inserted (e.g. with unique constraints).

But in the case above, it doesn't do either of that. Instead, it converts the literal in the check constraint to double, applies the check (with a warning) and inserts the value, which leaves the table contents invalid for further operations:

MariaDB [test]> insert ignore into t values (1);

Query OK, 1 row affected, 1 warning (0.001 sec)

MariaDB [test]> show warnings;

+---------+------+---------------------------------------+

| Level   | Code | Message                               |

+---------+------+---------------------------------------+

| Warning | 1292 | Truncated incorrect DECIMAL value: '' |

+---------+------+---------------------------------------+

1 row in set (0.000 sec)

MariaDB [test]> alter table t force;

ERROR 1292 (22007): Truncated incorrect DECIMAL value: ''

This behavior is not unique. The same would have occurred if check_constraint_checks=0 was used for INSERT instead of IGNORE, and that is documented. So, it's possible that in case of CHECK constraints INSERT IGNORE behaves this way by design.