Bug #31737 INSERT SELECT - ON DUPLICATE KEY UPDATE

Submitted: 21 Oct 2007 15:58 Modified: 21 Oct 2007 17:30
Reporter: Fouad Mardini Email Updates:
Status: Can't repeat Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:5.0.38 OS:Linux
Assigned to: 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)

Portions of this website are copyright © 2001, 2002 The PHP Group

Page generated in 0.048 sec. using MySQL 8.4.7-u6-cloud

Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.