[MDEV-40671] Non-unique values can be inserted into column with unique hash key - Jira

XML

Word

Printable

Possibly relates to MDEV-30095, but no nopad here

# Check that the values are equal

SELECT _tis620 0xE0A1 = _tis620 0xA1E0;

CREATE TABLE t1 (a VARCHAR(32) NOT NULL, UNIQUE(a))

  ENGINE=MEMORY CHARACTER SET tis620;

INSERT INTO t1 VALUES (0xE0A1),(0xA1E0);

SELECT COUNT(*) FROM t1 IGNORE INDEX (a) WHERE a = _tis620 0xE0A1;

SELECT COUNT(*) FROM t1 WHERE a = _tis620 0xE0A1;

DROP TABLE t1;

10.11 46ee2066f06e45b3cb701feb35a8816b27ac31b8
SELECT _tis620 0xE0A1 = _tis620 0xA1E0;
_tis620 0xE0A1 = _tis620 0xA1E0
1
CREATE TABLE t1 (a VARCHAR(32) NOT NULL, UNIQUE(a))
ENGINE=MEMORY CHARACTER SET tis620;
INSERT INTO t1 VALUES (0xE0A1),(0xA1E0);
SELECT COUNT(*) FROM t1 IGNORE INDEX (a) WHERE a = _tis620 0xE0A1;
COUNT(*)
2
SELECT COUNT(*) FROM t1 WHERE a = _tis620 0xE0A1;
COUNT(*)
1

For the memory table above a hash index is used by default. The failure is also reproducible with e.g. MyISAM and explicit USING HASH.

CREATE TABLE t1 (a VARCHAR(32) NOT NULL, UNIQUE(a) USING HASH)

ENGINE=MyISAM CHARACTER SET tis620;

INSERT INTO t1 VALUES (0xE0A1),(0xA1E0);

SELECT COUNT(*) FROM t1 WHERE a = _tis620 0xE0A1;

COUNT(*)

relates to

MDEV-30095 Unexpected duplicate entry error for UNIQUE USING HASH + NOPAD

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