INSERT ON DUPLICATE KEY UPDATE fails on AUTO_INCREMENT table with UNIQUE constraint

[dengn]

Description

When using INSERT ... ON DUPLICATE KEY UPDATE on a table with AUTO_INCREMENT and a UNIQUE constraint, MatrixOne incorrectly reports a duplicate key error instead of updating the existing row. In MySQL, this syntax should update the existing row when a duplicate key is encountered, rather than throwing an error.

Table Schema

CREATE TABLE ai_duplicate_test (
    id INT AUTO_INCREMENT PRIMARY KEY,
    code VARCHAR(20) UNIQUE,
    count INT
);

SQL Statements

-- Create table with AUTO_INCREMENT and UNIQUE constraint
CREATE TABLE ai_duplicate_test (
    id INT AUTO_INCREMENT PRIMARY KEY,
    code VARCHAR(20) UNIQUE,
    count INT
);

-- Insert initial data
INSERT INTO ai_duplicate_test (code, count) VALUES ('A', 1), ('B', 2);

-- Attempt to insert with ON DUPLICATE KEY UPDATE
INSERT INTO ai_duplicate_test (code, count) VALUES ('A', 5)
ON DUPLICATE KEY UPDATE count = count + 1;

-- Expected: The row with code='A' should be updated: count should become 2 (1 + 1)
-- Then insert a new row
INSERT INTO ai_duplicate_test (code, count) VALUES ('C', 10);

Error Message

ERROR 1062 (HY000): Duplicate entry 'A' for key 'code'

Expected Behavior

In MySQL, INSERT ... ON DUPLICATE KEY UPDATE works as follows:

1. Attempt to insert the new row
2. If a duplicate key violation occurs, instead of throwing an error, update the existing row using the UPDATE clause
3. The AUTO_INCREMENT id of the existing row remains unchanged

For the example above:

• After INSERT INTO ai_duplicate_test (code, count) VALUES ('A', 5) ON DUPLICATE KEY UPDATE count = count + 1;, the row with code='A' should be updated: count should become 2 (original value 1 + 1 from the UPDATE clause)
• The id should remain 1 (the original AUTO_INCREMENT value)
• The final result should show 3 rows: (id=1, code='A', count=2), (id=2, code='B', count=2), (id=3, code='C', count=10)

Actual Behavior

MatrixOne throws a duplicate key error instead of performing the update operation, indicating that the ON DUPLICATE KEY UPDATE clause is not being properly processed.

Impact

• Functionality: INSERT ... ON DUPLICATE KEY UPDATE cannot be used on tables with AUTO_INCREMENT and UNIQUE constraints
• MySQL Compatibility: This is a standard MySQL feature that should work correctly
• Use Cases: Common scenarios include:
  • Upsert operations where you want to update existing records or insert new ones
  • Counter/increment operations (e.g., incrementing a view count)
  • Data synchronization where you need to update records by unique key
  • Simplifying logic that would otherwise require SELECT + UPDATE or INSERT

Test Context

This issue was discovered during comprehensive AUTO_INCREMENT boundary testing (100 test cases). The error occurred consistently in all 10 test cases that attempted to use INSERT ... ON DUPLICATE KEY UPDATE on AUTO_INCREMENT tables with UNIQUE constraints.

Related Test Cases

• Case 3130, 3140, 3150, 3160, 3170, 3180, 3190, 3200, 3210, 3220 (all ai_insert_duplicate test cases)

[aptend]

MatrixOne's ON DUPLICATE KEY UPDATE only triggers on primary key conflicts, not on unique key conflicts. Unique key violations will result in an error instead of triggering the UPDATE clause. This is an intentional technical choice.