Posted on by

INSERT IGNORE … ON DUPLICATE KEY UPDATE … allowed

Not serious, but just interesting: MDEV-16925: INSERT IGNORE … ON DUPLICATE KEY UPDATE … allowed

  • INSERT IGNORE allows an insert to come back with “ok” even if the row already exists.
  • INSERT … ON DUPLICATE KEY UPDATE … intends the UPDATE to be executed in case the row already exists.

So combining the two makes no sense, and while harmless, perhaps it would be better if the parser were to throw a syntax error for it.
Currently, it appears the server executes the ON DUPLICATE KEY, thus disregarding the IGNORE:

MariaDB [test]> create table dup (a int, b int, unique (a,b));
Query OK, 0 rows affected (0.019 sec)

MariaDB [test]> insert into dup values (1,20),(2,24);
Query OK, 2 rows affected (0.013 sec)
Records: 2  Duplicates: 0  Warnings: 0

MariaDB [test]> select * from dup;
+------+------+
| a    | b    |
+------+------+
|    1 |   20 |
|    2 |   24 |
+------+------+
2 rows in set (0.000 sec)

MariaDB [test]> insert ignore foo values (2,24) on duplicate key update b=23;
Query OK, 1 row affected (0.006 sec)

MariaDB [test]> select * from dup;
+------+------+
| a    | b    |
+------+------+
|    1 |   20 |
|    2 |   24 |
+------+------+
2 rows in set (0.000 sec)

MariaDB [test]> insert ignore dup values (2,24) on duplicate key update b=23;
Query OK, 2 rows affected (0.007 sec)

MariaDB [test]> select * from dup;
+------+------+
| a    | b    |
+------+------+
|    1 |   20 |
|    2 |   23 |
+------+------+
2 rows in set (0.001 sec)

It has been noted that INSERT can chuck errors for reasons other than a duplicate key.
As the manual states:

“IGNORE has a similar effect on inserts into partitioned tables where no partition matching a given value is found. Without IGNORE, such INSERT statements are aborted with an error. When INSERT IGNORE is used, the insert operation fails silently for rows containing the unmatched value, but inserts rows that are matched.”

One example would be a foreign key check failing, in this case IGNORE would see the INSERT return without error (but without inserting a row, of course).

However, when combined with ON DUPLICATE KEY may create an ambiguity. If an fk check fails, but there is also a dup key clause, which one takes precedence? That is, will the on dup key be executed in that case if IGNORE is also specified, or will the server just return without error because of the fk check fail?
If we intend to allow this syntax, then the exact behaviour for each of the possible combinations of errors should be documented.

Posted on by