Details
-
Bug
-
Status: Closed (View Workflow)
-
Minor
-
Resolution: Fixed
-
5.5, 10.0, 10.1, 10.3.8, 10.2, 10.3
-
Ubuntu GNU/Linux 18.04 (Bionic Beaver) x64
Description
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.
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)
|
|