[MDEV-35115] Inconsistent REPLACE behaviors - Jira

XML

Word

Printable

In the following test case, the REPLACE statement should successfully insert data, instead of returning an error.

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

CREATE UNIQUE INDEX i1 ON t0(c1);

INSERT INTO t0 (c1,c2,c3) VALUES (0,0,b'01');

INSERT INTO t0 (c1,c2,c3) VALUES (1,1,b'10');

REPLACE INTO t0 (c1,c2,c3) VALUES (0,1,b'11'); --  Duplicate entry '0' for key 'i1'

The reference test case can be obtained by defining the UNIQUE constraint in CREATE TABLE, and the same REPLACE statement successfully executed.

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

INSERT INTO t0 (c1,c2,c3) VALUES (0,0,b'01');

INSERT INTO t0 (c1,c2,c3) VALUES (1,1,b'10');

REPLACE INTO t0 (c1,c2,c3) VALUES (0,1,b'11'); -- no errors

As a result, the final table content differs because of the different ways of creating UNIQUE constraints.

Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.