Bug #29180 INSERT ... ON DUPLICATE KEY don't work correctly

• Submitted: 18 Jun 2007 16:48
• Modified: 26 Jun 2007 12:57
• Reporter: Vyacheslav Matjukhin
• Status: Closed
• Category: MySQL Server
• Severity: S2 (Serious)
• Version: 5.0.41-log
• OS: Linux
• CPU Architecture: Any
• Tags: ON DUPLICATE KEY UPDATE

[18 Jun 2007 16:48] Vyacheslav Matjukhin

Description:
When I insert multiple rows into table using 'ON DUPLICATE KEY UPDATE' syntax, where some rows are duplicate and some are not, unique rows can't be inserted or sometimes inserts partially and in wrong rows.

4.1 version works correctly, 5.0.25 probably too. I can repeat it with both MyISAM and InnoDB tables.

How to repeat:
CREATE TABLE t (
    i INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    r INTEGER,
    t VARCHAR(40), UNIQUE(t)
);

INSERT t (r,t) VALUES
    (1,'value1'),
    (2,'value2'),
    (3,'value3');

INSERT t (r,t) VALUES
    (2,'value2'),
    (4,'value4');

SELECT * FROM t;

Result:
+---+------+--------+
| i | r    | t      |
+---+------+--------+
| 1 |    1 | value1 |
| 2 |    2 | value2 |
| 3 |    3 | value4 |
+---+------+--------+

[18 Jun 2007 16:52] Vyacheslav Matjukhin

Sorry, i meant following query as second insert:

INSERT t (r,t) VALUES
    (2,'value2'),
    (4,'value4')
ON DUPLICATE KEY UPDATE t = VALUES(t);

[18 Jun 2007 19:24] Vyacheslav Matjukhin

This one gives wrong results too:

INSERT t (r,t) VALUES
    (2,'value2'),
    (4,'value4')
ON DUPLICATE KEY UPDATE r = VALUES(r);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 1  Warnings: 0

SELECT * FROM t;
+---+------+--------+
| i | r    | t      |
+---+------+--------+
| 1 |    1 | value1 |
| 2 |    2 | value2 |
| 3 |    4 | value3 |
+---+------+--------+

And this one don't insert anything at all:
INSERT t (r,t) VALUES
    (1,'value1'),
    (2,'value2'),
    (4,'value4'),
    (3,'value3')
ON DUPLICATE KEY UPDATE r = VALUES(r);
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 2  Warnings: 0

SELECT * FROM t;
+---+------+--------+
| i | r    | t      |
+---+------+--------+
| 1 |    1 | value1 |
| 2 |    2 | value2 |
| 3 |    3 | value3 |
+---+------+--------+

PS. All these queries works correctly with 5.0.32. But 5.0.41 is completely broken.

[26 Jun 2007 12:57] MySQL Verification Team

Thank you for your bug report. This issue was already reported and fixed
in the source tree:

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.0.46-debug Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> CREATE TABLE t (
    ->     i INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    ->     r INTEGER,
    ->     t VARCHAR(40), UNIQUE(t)
    -> );
Query OK, 0 rows affected (0.05 sec)

mysql> 
mysql> INSERT t (r,t) VALUES
    ->     (1,'value1'),
    ->     (2,'value2'),
    ->     (3,'value3');
Query OK, 3 rows affected (0.03 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> INSERT t (r,t) VALUES
    ->     (2,'value2'),
    ->     (4,'value4')
    -> ON DUPLICATE KEY UPDATE t = VALUES(t);
Query OK, 1 row affected (0.04 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM t;
+---+------+--------+
| i | r    | t      |
+---+------+--------+
| 1 |    1 | value1 | 
| 2 |    2 | value2 | 
| 3 |    3 | value3 | 
| 4 |    4 | value4 | 
+---+------+--------+
4 rows in set (0.00 sec)

mysql> drop table t;
Query OK, 0 rows affected (0.14 sec)

mysql> CREATE TABLE t (
    ->     i INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    ->     r INTEGER,
    ->     t VARCHAR(40), UNIQUE(t)
    -> );
Query OK, 0 rows affected (0.02 sec)

mysql> 
mysql> INSERT t (r,t) VALUES
    ->     (1,'value1'),
    ->     (2,'value2'),
    ->     (3,'value3');
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> INSERT t (r,t) VALUES
    ->     (1,'value1'),
    ->     (2,'value2'),
    ->     (4,'value4'),
    ->     (3,'value3')
    -> ON DUPLICATE KEY UPDATE r = VALUES(r);
Query OK, 1 row affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM t;
+---+------+--------+
| i | r    | t      |
+---+------+--------+
| 1 |    1 | value1 | 
| 2 |    2 | value2 | 
| 3 |    3 | value3 | 
| 4 |    4 | value4 | 
+---+------+--------+
4 rows in set (0.00 sec)