Bug #73925 INSERT INTO ... ON DUPLICATE KEY UPDATE not taking into account multiple column

Submitted: 16 Sep 2014 9:34 Modified: 16 Sep 2014 11:22
Reporter: Pasqual Casanova Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S2 (Serious)
Version:5.5.27 OS:Windows (7 64 bit)
Assigned to: CPU Architecture:Any
Tags: bug, constraint, multiple column, ON DUPLICATE KEY, primary key, unique

[16 Sep 2014 9:34] Pasqual Casanova
Description:
I don't know if this is the expected result, but I suspect it is not correct.

If I try INSERT INTO ... ON DUPLICATE KEY UPDATE with a MULTIPLE COLUMN primary key or unique constraint, it updates the column even if only *ONE* of the multiple constraint column values already exists.

For instance, I have the table 'addresses' with 3 columns;

id | district | number

I define a multiple PRIMARY KEY constraint involving 'district' and 'number' like this:

ALTER TABLE addresses ADD CONSTRAINT my_constraint PRIMARY KEY (street,number)

Then I have those values:

id | district | number
-----------------------
1  |        1 |      1
2  |        2 |      3

If I do a an

INSERT INTO addresses (district, number) VALUES (2,4) I WOULD EXPECT a new record to be inserted:

Expected result:

id | district | number
-----------------------
1  |        1 |      1
2  |        2 |      3
3  |        2 |      4

Instead, it seems to understand that the constraint is violated because there's already a row with 'district' = 2, thus UPDATING row with 'id' = 2:

Actual result:

Expected result:

id | district | number
-----------------------
1  |        1 |      1
2  |        2 |      4

The same result happens with a UNIQUE constraint instead of a PRIMARY KEY.

This behaviour totally breaks functionality since it seems to ignore what UNIQUE / PRIMARY KEY constraints are for.

I'm on 5.5.27 under Windows 7 64 bit. Tables are InnoDB.

How to repeat:
Follow the procedure in the description.

Suggested fix:
Take multiple column constraints into account.
[16 Sep 2014 11:22] Pasqual Casanova
Sorry, I checked the constraints on the table - I had multiple ones. After deleting them and keeping only the PRIMARY KEY for the multiple intended columns, everything works as expected.

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

Page generated in 0.039 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.