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

Wrong auto increment values on INSERT .. ON DUPLICATE KEY UPDATE when the inserted columns include NULL in an auto-increment column

    XMLWordPrintable

Details

    Description

      An auto increment value of 0 is created at MariaDB 10.0.13/14 when NULL is used
      The MySQL 5.6.15 and MySQL 5.7.5 behave properly.
      example 10.0.14:

      --------------
      SELECT @@version
      --------------
       
      +---------------------+
      | @@version           |
      +---------------------+
      | 10.0.14-MariaDB-log |
      +---------------------+
      1 row in set (0.00 sec)
       
      --------------
      DROP TABLE IF EXISTS iodku
      --------------
       
      Query OK, 0 rows affected, 1 warning (0.00 sec)
       
      --------------
      CREATE TABLE iodku (
      	  id int(10) unsigned NOT NULL AUTO_INCREMENT,
      	  value int(11) NOT NULL DEFAULT '0',
      	  updates int not null default 0,
      	  PRIMARY KEY (id)
      ) ENGINE=InnoDB DEFAULT CHARSET=latin1
      --------------
       
      Query OK, 0 rows affected (0.02 sec)
       
      --------------
      INSERT INTO iodku (id, value) VALUES (NULL, 1), (NULL, 2), (NULL, 3) ON DUPLICATE KEY UPDATE value = VALUES(value) , updates = updates+1
      --------------
       
      Query OK, 3 rows affected (0.01 sec)
      Records: 3  Duplicates: 0  Warnings: 0
       
      --------------
      SELECT * FROM iodku
      --------------
       
      +----+-------+---------+
      | id | value | updates |
      +----+-------+---------+
      |  1 |     1 |       0 |
      |  2 |     2 |       0 |
      |  3 |     3 |       0 |
      +----+-------+---------+
      3 rows in set (0.00 sec)
       
      --------------
      INSERT INTO iodku (id, value) VALUES (NULL, 4), (NULL, 5), (3, -3) ON DUPLICATE KEY UPDATE value = VALUES(value) , updates = updates+1
      --------------
       
      Query OK, 4 rows affected (0.00 sec)
      Records: 3  Duplicates: 1  Warnings: 0
       
      --------------
      SELECT * FROM iodku
      --------------
       
      +----+-------+---------+
      | id | value | updates |
      +----+-------+---------+
      |  1 |     1 |       0 |
      |  2 |     2 |       0 |
      |  3 |    -3 |       1 |
      |  4 |     4 |       0 |
      |  5 |     5 |       0 |
      +----+-------+---------+
      5 rows in set (0.00 sec)
       
      --------------
      INSERT INTO iodku (id, value) VALUES (1, -1), (NULL, 6), (NULL, 7) ON DUPLICATE KEY UPDATE value = VALUES(value) , updates = updates+1
      --------------
       
      Query OK, 4 rows affected (0.00 sec)
      Records: 3  Duplicates: 1  Warnings: 0
       
      --------------
      SELECT * FROM iodku
      --------------
       
      +----+-------+---------+
      | id | value | updates |
      +----+-------+---------+
      |  1 |    -1 |       1 |
      |  2 |     2 |       0 |
      |  3 |    -3 |       1 |
      |  4 |     4 |       0 |
      |  5 |     5 |       0 |
      |  7 |     6 |       0 |
      |  8 |     7 |       0 |
      +----+-------+---------+
      7 rows in set (0.00 sec)
       
      --------------
      INSERT INTO iodku (id, value) VALUES (NULL, 8), (2, -2), (NULL, 9) ON DUPLICATE KEY UPDATE value = VALUES(value) , updates = updates+1
      --------------
       
      Query OK, 4 rows affected (0.01 sec)
      Records: 3  Duplicates: 1  Warnings: 0
       
      --------------
      SELECT * FROM iodku
      --------------
       
      +----+-------+---------+
      | id | value | updates |
      +----+-------+---------+
      |  0 |     9 |       0 |
      |  1 |    -1 |       1 |
      |  2 |    -2 |       1 |
      |  3 |    -3 |       1 |
      |  4 |     4 |       0 |
      |  5 |     5 |       0 |
      |  7 |     6 |       0 |
      |  8 |     7 |       0 |
      | 10 |     8 |       0 |
      +----+-------+---------+
      9 rows in set (0.00 sec)
       
      --------------
      INSERT INTO iodku (id, value) VALUES (4, -4), (NULL, 10), (5, -5), (NULL, 11), (NULL, 12), (1, 1), (NULL, 13) ON DUPLICATE KEY UPDATE value = VALUES(value), updates = updates+1
      --------------
       
      Query OK, 13 rows affected (0.00 sec)
      Records: 7  Duplicates: 6  Warnings: 0
       
      --------------
      SELECT * FROM iodku
      --------------
       
      +----+-------+---------+
      | id | value | updates |
      +----+-------+---------+
      |  0 |    13 |       3 |
      |  1 |     1 |       2 |
      |  2 |    -2 |       1 |
      |  3 |    -3 |       1 |
      |  4 |    -4 |       1 |
      |  5 |    -5 |       1 |
      |  7 |     6 |       0 |
      |  8 |     7 |       0 |
      | 10 |     8 |       0 |
      | 13 |    10 |       0 |
      +----+-------+---------+
      10 rows in set (0.00 sec)
       
      --------------
      DROP TABLE IF EXISTS iodku
      --------------
       
      Query OK, 0 rows affected (0.14 sec)
       
      Bye

      There are test cases at the uploaded files.

      Attachments

        1. innodb.patch
          4 kB
          Jan Lindström
        2. innodb-mdev7150.test
          2 kB
          Jan Lindström
        3. mariadb-10.0.14-with-updates.txt
          4 kB
          Stoykov
        4. mysql-5.6.15-with-updates.txt
          4 kB
          Stoykov
        5. mysql-5.7.5-with-updates.txt
          4 kB
          Stoykov

        Activity

          People

            serg Sergei Golubchik
            ivan.stoykov@skysql.com Stoykov (Inactive)
            Votes:
            1 Vote for this issue
            Watchers:
            6 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.