Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.0.14, 5.5(EOL), 10.0(EOL)
-
None
-
Red Hat Enterprise Linux
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.