[MDEV-30115] Unspecified and inconsistent behavior of CHECK constraint requiring cast Created: 2022-11-28  Updated: 2023-11-28

Status: Confirmed
Project: MariaDB Server
Component/s: Data Manipulation - Insert
Affects Version/s: 10.3, 10.4, 10.5, 10.6, 10.7, 10.8, 10.9, 10.10, 10.11
Fix Version/s: 10.4, 10.5, 10.6

Type: Bug Priority: Major
Reporter: Elena Stepanova Assignee: Oleksandr Byelkin
Resolution: Unresolved Votes: 0
Labels: None


 Description   

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:

10.6 05cd10b74c

 
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.

 Comments   
Comment by Sergei Golubchik [ 2022-12-02 ]

I think that violating a constraint is shouldn't be done lightly, not on IGNORE. So it's a bug. The row should be ignored, with CHECK CONSTRAINT just as it is with UNIQUE CONSTRAINT

Comment by Elena Stepanova [ 2023-07-03 ]

Here is an even stranger representation of (hopefully) the same root cause.
Same table, but instead of INSERT IGNORE, we do two identical INSERTs in a row.

create or replace table t (a int, check (a!='')) engine=MyISAM;
 
insert into t values (1);
 
insert into t values (1);
 
show warnings;
 
select * from t;
 
alter table t force;

The first INSERT fails with ER_TRUNCATED_WRONG_VALUE (ERROR 1292 (22007): Truncated incorrect DECIMAL value: '').
But the second, identical, INSERT succeeds:

10.4 f5dceafd

 
MariaDB [test]> create or replace table t (a int, check (a!='')) engine=MyISAM;
 
Query OK, 0 rows affected (0.030 sec)
 
 
 
MariaDB [test]> insert into t values (1);
 
ERROR 1292 (22007): Truncated incorrect DECIMAL value: ''
 
MariaDB [test]> insert into t values (1);
 
Query OK, 1 row affected (0.000 sec)
 
 
 
MariaDB [test]> show warnings;
 
Empty set (0.000 sec)
 
 
 
MariaDB [test]> select * from t;
 
+------+
 
| a    |
 
+------+
 
|    1 |
 
+------+
 
1 row in set (0.000 sec)

and the outcome is the same as in the description, an invalid table.
Now it cannot be just a documentation issue, obviously something is wrong here.

Generated at Thu Feb 08 10:13:46 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.