Bug #116320 Inconsistent behavior for enforcing UNIQUE constraint

• Submitted: 9 Oct 2024 7:37
• Modified: 9 Oct 2024 11:55
• Reporter: John Jove
• Status: Verified
• Category: MySQL Server: DDL
• Severity: S3 (Non-critical)
• Version: 8.0
• OS: Any
• CPU Architecture: Any

[9 Oct 2024 7:37] John Jove

Description:
I try to enforce the same UNIQUE constraint on a column by CREATE INDEX, ALTER TABLE or directly defined in CREATE TABLE. I think that for the same workload, these three ways should return the same execution results. However, I observe some difference, in which case 1 and 2 return an error, while case 3 does not throw an error.

How to repeat:
-- case 1
CREATE TABLE t0 (c1 NUMERIC UNSIGNED NOT NULL, c2 INT3 UNIQUE, c3 BIT(2) PRIMARY KEY);
ALTER TABLE t0 ADD UNIQUE INDEX (c1);

-- case 2
CREATE TABLE t0 (c1 NUMERIC UNSIGNED NOT NULL, c2 INT3 UNIQUE, c3 BIT(2) PRIMARY KEY);
CREATE UNIQUE INDEX i1 ON t0(c1);

-- case 3
CREATE TABLE t0 (c1 NUMERIC UNSIGNED NOT NULL UNIQUE, c2 INT3 UNIQUE, c3 BIT(2) PRIMARY KEY);

-- workload
INSERT INTO t0 (c1,c2,c3) VALUES (0,null,b'01');
INSERT INTO t0 (c1,c2,c3) VALUES (1,1,b'10');
UPDATE IGNORE t0 SET c1 = 0, c2 = null;
REPLACE INTO t0 (c1,c2,c3) VALUES (0,1,b'11'); -- 1,2: Duplicate entry '0' for key 'i1', 3: ok

[9 Oct 2024 11:55] MySQL Verification Team

Hi Mr. Jove,

Thank you for your bug report.

We added one more SELECT after UPDATE, just to see the contents. 

We repeated the behaviour from your test case.

We repeated the behaviour on 8.0 and 9.0.

Verified as reported for 8.0 and higher versions.

This is now a verified bug report.