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

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

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Minor
    • Resolution: Fixed
    • 5.5, 10.0, 10.1, 10.3.8, 10.2, 10.3
    • N/A
    • Documentation, Parser
    • 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

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

            Dates

              Created:
              Updated:
              Resolved:

              Git Integration

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