Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-30115

Unspecified behavior of INSERT IGNORE and CHECK constraint

    XMLWordPrintable

    Details

      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.

        Attachments

          Activity

            People

            Assignee:
            sanja Oleksandr Byelkin
            Reporter:
            elenst Elena Stepanova
            Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

              Dates

              Created:
              Updated:

                Git Integration

                Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.