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

    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

          Activity

            People

            • Assignee:
              serg Sergei Golubchik
              Reporter:
              ivan.stoykov@skysql.com Stoykov (Inactive)
            • Votes:
              1 Vote for this issue
              Watchers:
              6 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: