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
• Status: Not a Bug
• Category: MySQL Server: InnoDB storage engine
• Severity: S2 (Serious)
• Version: 5.5.27
• OS: Windows (7 64 bit)
• 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.