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

INSERT IGNORE ... ON DUPLICATE KEY UPDATE ... allowed

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Closed (View Workflow)
    • Priority: Minor
    • Resolution: Fixed
    • Affects Version/s: 5.5, 10.0, 10.1, 10.2, 10.3, 10.3.8
    • Fix Version/s: N/A
    • Component/s: Documentation, Parser
    • Labels:
    • Environment:
      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)
      
      

        Attachments

          Activity

            People

            • Assignee:
              greenman Ian Gilfillan
              Reporter:
              arjen Arjen Lentz
            • Votes:
              1 Vote for this issue
              Watchers:
              5 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: