Bug #27504 Bugfix 23170 for INSERT...SELECT introduces regression to INSERT.. ON DUPLICATE.
Submitted: 28 Mar 2007 19:34 Modified: 29 Mar 2007 22:15
Reporter: Matthew Montgomery Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server: General Severity:S2 (Serious)
Version:5.0.36, 5.0.38 OS:Linux (Linux 2.6)
Assigned to: CPU Architecture:Any

[28 Mar 2007 19:34] Matthew Montgomery
Description:
The behavior of INSERT... ON DUPLICATE KEY UPDATE... changed between 5.0.34 and 5.0.36 due to a bug fix for INSERT... IGNORE behavior.

Current behavior does not conform to Documented behavior for 5.0.36 or 5.1.16

How to repeat:
In mysql-enterprise-gpl-5.0.34.rhel.x86_64:

CREATE TABLE Circuit (
CIRCUIT_ID INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
CIRCUIT VARCHAR(256) NOT NULL UNIQUE
) ENGINE=InnoDB;

mysql> INSERT INTO Circuit SET CIRCUIT = "foo" ON DUPLICATE KEY UPDATE CIRCUIT_ID=LAST_INSERT_ID(CIRCUIT_ID); select LAST_INSERT_ID();
Query OK, 1 row affected (0.01 sec)

+------------------+
| LAST_INSERT_ID() |
+------------------+
| 1 |
+------------------+
1 row in set (0.00 sec)

mysql> INSERT INTO Circuit SET CIRCUIT = "foo" ON DUPLICATE KEY UPDATE CIRCUIT_ID=LAST_INSERT_ID(CIRCUIT_ID); select LAST_INSERT_ID();
Query OK, 0 rows affected (0.00 sec)

+------------------+
| LAST_INSERT_ID() |
+------------------+
| 1 |
+------------------+
1 row in set (0.00 sec)

=========================================================================

In mysql-enterprise-gpl-5.0.36.rhel.x86_64:

CREATE TABLE Circuit (
CIRCUIT_ID INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
CIRCUIT VARCHAR(256) NOT NULL UNIQUE
) ENGINE=InnoDB;

mysql> INSERT INTO Circuit SET CIRCUIT = "foo" ON DUPLICATE KEY UPDATE CIRCUIT_ID=LAST_INSERT_ID(CIRCUIT_ID); select LAST_INSERT_ID();
Query OK, 1 row affected (0.01 sec)

+------------------+
| LAST_INSERT_ID() |
+------------------+
| 1 |
+------------------+
1 row in set (0.00 sec)

mysql> INSERT INTO Circuit SET CIRCUIT = "foo" ON DUPLICATE KEY UPDATE CIRCUIT_ID=LAST_INSERT_ID(CIRCUIT_ID); select LAST_INSERT_ID();
Query OK, 0 rows affected (0.00 sec)

+------------------+
| LAST_INSERT_ID() |
+------------------+
| 0 |
+------------------+
1 row in set (0.00 sec)
[28 Mar 2007 19:36] Matthew Montgomery
See LAST_INSERT_ID() notes at bottom of :
http://dev.mysql.com/doc/refman/5.0/en/insert-on-duplicate.html
[29 Mar 2007 21:03] Matthew Montgomery
Still present in 5.0.38
[29 Mar 2007 22:15] Matthew Montgomery
Duplicate of Bug #27033