Bug #31737 INSERT SELECT - ON DUPLICATE KEY UPDATE

• Submitted: 21 Oct 2007 15:58
• Modified: 21 Oct 2007 17:30
• Reporter: Fouad Mardini
• Status: Can't repeat
• Category: MySQL Server
• Severity: S3 (Non-critical)
• Version: 5.0.38
• OS: Linux
• CPU Architecture: Any

[21 Oct 2007 15:58] Fouad Mardini

Description:
Insert select is not working as advertised :)
When duplicate rows exist, updates use the value returned from the last row of the  select query, and not the corresponding row

How to repeat:
CREATE TABLE `t1` (
  `id1` int(11) NOT NULL,
  `id2` int(11) NOT NULL,
  `val1` int(11) default '0',
  `val2` int(11) default '0',
  PRIMARY KEY  (`id1`,`id2`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

INSERT INTO t1( `id1` , `id2` , `val1` , `val2` ) VALUES ( 1, 1, 1, 10 ) , ( 1, 2, 2, 20 ) , ( 1, 3, 3, 30 ) , ( 2, 1, 4, 40 ) , ( 2, 2, 5, 50 ) , ( 2, 3, 6, 60 )

INSERT INTO t1
SELECT 1, a2.id2, 0, 100
FROM t1 a2 WHERE a2.id1 = 2
ON DUPLICATE KEY UPDATE t1.val2 = a2.val1 + 100

Actual Result
1       1       1       106
1       2       2       106
1       3       0       100
2       1       4       40
2       2       5       50
2       3       6       60

Expected Result
1       1       1       104
1       2       2       105
1       3       0       100
2       1       4       40
2       2       5       50
2       3       6       60

[21 Oct 2007 16:06] Fouad Mardini

OOPS, the insert should be like this

INSERT INTO t1(`id1`, `id2`, `val1`, `val2`) VALUES (1, 1, 1, 10 ), (1, 2, 2, 20),  (2, 1, 4, 40), (2, 2, 5, 50), (2, 3, 6, 60)

[21 Oct 2007 17:30] MySQL Verification Team

Thank you for the bug report. I can't repeat with current source server.
Please upgrade or wait to latest released version.

mysql> INSERT INTO t1
    -> SELECT 1, a2.id2, 0, 100
    -> FROM t1 a2 WHERE a2.id1 = 2
    -> ON DUPLICATE KEY UPDATE t1.val2 = a2.val1 + 100
    -> ;
Query OK, 5 rows affected (0.00 sec)
Records: 3  Duplicates: 2  Warnings: 0

mysql> select * from t1;
+-----+-----+------+------+
| id1 | id2 | val1 | val2 |
+-----+-----+------+------+
|   1 |   1 |    1 |  104 |
|   1 |   2 |    2 |  105 |
|   1 |   3 |    0 |  100 |
|   2 |   1 |    4 |   40 |
|   2 |   2 |    5 |   50 |
|   2 |   3 |    6 |   60 |
+-----+-----+------+------+
6 rows in set (0.00 sec)

mysql> select version();
+---------------+
| version()     |
+---------------+
| 5.0.52-nt-log |
+---------------+
1 row in set (0.00 sec)