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

Details

Type: Bug
Status: Open (View Workflow)
Priority: Major
Resolution: Unresolved
Affects Version/s: 10.4(EOL), 10.5, 10.6, 10.7(EOL), 10.8(EOL), 10.9(EOL), 10.10(EOL), 10.11
Fix Version/s: 10.4(EOL)
Component/s: Character Sets
Labels:
None

Description

This script returns an unexpected duplicate entry error:

CREATE OR REPLACE TABLE t1 (

  a LONGTEXT CHARACTER SET latin1 COLLATE latin1_swedish_nopad_ci,

  UNIQUE KEY(a(33000)) USING HASH

);

INSERT INTO t1 VALUES (CONCAT('td',SPACE(31696)));

INSERT INTO t1 VALUES (CONCAT('td',SPACE(31728)));

ERROR 1062 (23000): Duplicate entry 'td                                                           ...' for key 'a'

The two inserted strings cannot be equal because this is a NOPAD collation - different amounts of trailing spaces make the longer string greater. Therefore, the duplicate entry error is not correct.

The same problem is repeatable with these two strings:

CREATE OR REPLACE TABLE t1 (

  a LONGTEXT CHARACTER SET latin1 COLLATE latin1_swedish_nopad_ci,

  UNIQUE KEY(a(33000)) USING HASH

);

INSERT INTO t1 VALUES (CONCAT('xh',SPACE(32457)));

INSERT INTO t1 VALUES (CONCAT('xh',SPACE(32489)));

ERROR 1062 (23000): Duplicate entry 'xh                                                           ...' for key 'a'

Problem details

These two data pairs are special - they were found programatically.

The idea is that these two expressions produce the same hash value 10067658370460279189 in the collation latin1_swedish_nopad_ci:

CONCAT('td',SPACE(31696))
CONCAT('td',SPACE(31728))

The problem is that the further duplicate hash resolution (performed to know if the actual values are really equal) erroneously treat these strings as equal using strnncollsp_nchars(). Using this function is correct for the CHAR data type, but it's not correct for VARCHAR/TEXT.

The other two expressions also produce equal hash value 1452848758766898498 in the collation latin1_swedish_nopad_ci:

CONCAT('xh',SPACE(32457))
CONCAT('xh',SPACE(32489))

Attachments

Issue Links

is caused by

MDEV-25904 New collation functions to compare InnoDB style trimmed NO PAD strings

Closed

relates to

MDEV-371 Unique indexes for blobs

Closed

MDEV-33812 Index ingnores trailing spaces

Closed

MDEV-30072 Wrong ORDER BY for a partitioned prefix key + NOPAD

Closed

Activity

Ascending order - Click to sort in descending order

Alexander Barkov created issue - 2022-11-25 15:05

Alexander Barkov made changes - 2022-11-25 15:05

Field	Original Value	New Value
Link		This issue relates to ~~MDEV-30072~~ [ ~~MDEV-30072~~ ]

Alexander Barkov made changes - 2022-11-25 15:05

Link

This issue is caused by ~~MDEV-27653~~ [ ~~MDEV-27653~~ ]

Alexander Barkov made changes - 2022-11-25 15:05

Link

This issue is caused by ~~MDEV-27653~~ [ ~~MDEV-27653~~ ]

Alexander Barkov made changes - 2022-11-25 15:05

Link

This issue is caused by ~~MDEV-25904~~ [ ~~MDEV-25904~~ ]

Alexander Barkov made changes - 2022-11-25 15:08

Link

This issue relates to ~~MDEV-371~~ [ ~~MDEV-371~~ ]

Alexander Barkov made changes - 2022-11-25 15:08

Description

This script returns an unexpected duplicate entry errors:

{code:sql}
CREATE OR REPLACE TABLE t1 (
  a LONGTEXT CHARACTER SET latin1 COLLATE latin1_swedish_nopad_ci,
  UNIQUE KEY(a(33000)) USING HASH
);
INSERT INTO t1 VALUES (CONCAT('td',SPACE(31696)));
INSERT INTO t1 VALUES (CONCAT('td',SPACE(31728)));
{code}
{noformat}
ERROR 1062 (23000): Duplicate entry 'td ...' for key 'a'
{noformat}

The two inserted strings cannot be equal because this is a NOPAD collation - different amounts of trailing spaces make the longer string greater. Therefore, the duplicate entry error is not correct.

The same problem is repeatable with these two strings:
{code:sql}
CREATE OR REPLACE TABLE t1 (
  a LONGTEXT CHARACTER SET latin1 COLLATE latin1_swedish_nopad_ci,
  UNIQUE KEY(a(33000)) USING HASH
);
INSERT INTO t1 VALUES (CONCAT('xh',SPACE(32457)));
INSERT INTO t1 VALUES (CONCAT('xh',SPACE(32489)));
{code}
{noformat}
ERROR 1062 (23000): Duplicate entry 'xh ...' for key 'a'
{noformat}

Note, these two data pairs are special - they were found programmatically.

The idea is that these two expressions produce the same hash value 10067658370460279189 in the collation latin1_swedish_nopad_ci:
- {{CONCAT('td',SPACE(31696))}}
- {{CONCAT('td',SPACE(31728))}}
The problems is that the further duplicate hash resolution (to know if the actual values are really equal) erroneously treat these strings as equal.

The other two expressions also produce equal hash value 1452848758766898498 in the collation latin1_swedish_nopad_ci:
- {{CONCAT('xh',SPACE(32457))}}
- {{CONCAT('xh',SPACE(32489))}}

This script returns an unexpected duplicate entry error:

{code:sql}
CREATE OR REPLACE TABLE t1 (
  a LONGTEXT CHARACTER SET latin1 COLLATE latin1_swedish_nopad_ci,
  UNIQUE KEY(a(33000)) USING HASH
);
INSERT INTO t1 VALUES (CONCAT('td',SPACE(31696)));
INSERT INTO t1 VALUES (CONCAT('td',SPACE(31728)));
{code}
{noformat}
ERROR 1062 (23000): Duplicate entry 'td ...' for key 'a'
{noformat}

The two inserted strings cannot be equal because this is a NOPAD collation - different amounts of trailing spaces make the longer string greater. Therefore, the duplicate entry error is not correct.

The same problem is repeatable with these two strings:
{code:sql}
CREATE OR REPLACE TABLE t1 (
  a LONGTEXT CHARACTER SET latin1 COLLATE latin1_swedish_nopad_ci,
  UNIQUE KEY(a(33000)) USING HASH
);
INSERT INTO t1 VALUES (CONCAT('xh',SPACE(32457)));
INSERT INTO t1 VALUES (CONCAT('xh',SPACE(32489)));
{code}
{noformat}
ERROR 1062 (23000): Duplicate entry 'xh ...' for key 'a'
{noformat}

Note, these two data pairs are special - they were found programmatically.

The idea is that these two expressions produce the same hash value 10067658370460279189 in the collation latin1_swedish_nopad_ci:
- {{CONCAT('td',SPACE(31696))}}
- {{CONCAT('td',SPACE(31728))}}
The problems is that the further duplicate hash resolution (to know if the actual values are really equal) erroneously treat these strings as equal.

The other two expressions also produce equal hash value 1452848758766898498 in the collation latin1_swedish_nopad_ci:
- {{CONCAT('xh',SPACE(32457))}}
- {{CONCAT('xh',SPACE(32489))}}

Alexander Barkov made changes - 2022-11-25 15:09

Description

This script returns an unexpected duplicate entry error:

{code:sql}
CREATE OR REPLACE TABLE t1 (
  a LONGTEXT CHARACTER SET latin1 COLLATE latin1_swedish_nopad_ci,
  UNIQUE KEY(a(33000)) USING HASH
);
INSERT INTO t1 VALUES (CONCAT('td',SPACE(31696)));
INSERT INTO t1 VALUES (CONCAT('td',SPACE(31728)));
{code}
{noformat}
ERROR 1062 (23000): Duplicate entry 'td ...' for key 'a'
{noformat}

The two inserted strings cannot be equal because this is a NOPAD collation - different amounts of trailing spaces make the longer string greater. Therefore, the duplicate entry error is not correct.

The same problem is repeatable with these two strings:
{code:sql}
CREATE OR REPLACE TABLE t1 (
  a LONGTEXT CHARACTER SET latin1 COLLATE latin1_swedish_nopad_ci,
  UNIQUE KEY(a(33000)) USING HASH
);
INSERT INTO t1 VALUES (CONCAT('xh',SPACE(32457)));
INSERT INTO t1 VALUES (CONCAT('xh',SPACE(32489)));
{code}
{noformat}
ERROR 1062 (23000): Duplicate entry 'xh ...' for key 'a'
{noformat}

Note, these two data pairs are special - they were found programmatically.

The idea is that these two expressions produce the same hash value 10067658370460279189 in the collation latin1_swedish_nopad_ci:
- {{CONCAT('td',SPACE(31696))}}
- {{CONCAT('td',SPACE(31728))}}

The problem is that the further duplicate hash resolution (to know if the actual values are really equal) erroneously treat these strings as equal.

The other two expressions also produce equal hash value 1452848758766898498 in the collation latin1_swedish_nopad_ci:
- {{CONCAT('xh',SPACE(32457))}}
- {{CONCAT('xh',SPACE(32489))}}

Alexander Barkov made changes - 2022-11-25 15:11

Description

This script returns an unexpected duplicate entry error:

{code:sql}
CREATE OR REPLACE TABLE t1 (
  a LONGTEXT CHARACTER SET latin1 COLLATE latin1_swedish_nopad_ci,
  UNIQUE KEY(a(33000)) USING HASH
);
INSERT INTO t1 VALUES (CONCAT('td',SPACE(31696)));
INSERT INTO t1 VALUES (CONCAT('td',SPACE(31728)));
{code}
{noformat}
ERROR 1062 (23000): Duplicate entry 'td ...' for key 'a'
{noformat}

The two inserted strings cannot be equal because this is a NOPAD collation - different amounts of trailing spaces make the longer string greater. Therefore, the duplicate entry error is not correct.

The same problem is repeatable with these two strings:
{code:sql}
CREATE OR REPLACE TABLE t1 (
  a LONGTEXT CHARACTER SET latin1 COLLATE latin1_swedish_nopad_ci,
  UNIQUE KEY(a(33000)) USING HASH
);
INSERT INTO t1 VALUES (CONCAT('xh',SPACE(32457)));
INSERT INTO t1 VALUES (CONCAT('xh',SPACE(32489)));
{code}
{noformat}
ERROR 1062 (23000): Duplicate entry 'xh ...' for key 'a'
{noformat}

Note, these two data pairs are special - they were found programmatically.

The idea is that these two expressions produce the same hash value 10067658370460279189 in the collation latin1_swedish_nopad_ci:
- {{CONCAT('td',SPACE(31696))}}
- {{CONCAT('td',SPACE(31728))}}

The problem is that the further duplicate hash resolution (to know if the actual values are really equal) erroneously treat these strings as equal.

The other two expressions also produce equal hash value 1452848758766898498 in the collation latin1_swedish_nopad_ci:
- {{CONCAT('xh',SPACE(32457))}}
- {{CONCAT('xh',SPACE(32489))}}

This script returns an unexpected duplicate entry error:

{code:sql}
CREATE OR REPLACE TABLE t1 (
  a LONGTEXT CHARACTER SET latin1 COLLATE latin1_swedish_nopad_ci,
  UNIQUE KEY(a(33000)) USING HASH
);
INSERT INTO t1 VALUES (CONCAT('td',SPACE(31696)));
INSERT INTO t1 VALUES (CONCAT('td',SPACE(31728)));
{code}
{noformat}
ERROR 1062 (23000): Duplicate entry 'td ...' for key 'a'
{noformat}

The two inserted strings cannot be equal because this is a NOPAD collation - different amounts of trailing spaces make the longer string greater. Therefore, the duplicate entry error is not correct.

The same problem is repeatable with these two strings:
{code:sql}
CREATE OR REPLACE TABLE t1 (
  a LONGTEXT CHARACTER SET latin1 COLLATE latin1_swedish_nopad_ci,
  UNIQUE KEY(a(33000)) USING HASH
);
INSERT INTO t1 VALUES (CONCAT('xh',SPACE(32457)));
INSERT INTO t1 VALUES (CONCAT('xh',SPACE(32489)));
{code}
{noformat}
ERROR 1062 (23000): Duplicate entry 'xh ...' for key 'a'
{noformat}

h2. Problem details

These two data pairs are special - they were found programatically.

The idea is that these two expressions produce the same hash value 10067658370460279189 in the collation latin1_swedish_nopad_ci:
- {{CONCAT('td',SPACE(31696))}}
- {{CONCAT('td',SPACE(31728))}}

The problem is that the further duplicate hash resolution (to know if the actual values are really equal) erroneously treat these strings as equal.

The other two expressions also produce equal hash value 1452848758766898498 in the collation latin1_swedish_nopad_ci:
- {{CONCAT('xh',SPACE(32457))}}
- {{CONCAT('xh',SPACE(32489))}}

Alexander Barkov made changes - 2022-11-25 15:12

Description

This script returns an unexpected duplicate entry error:

{code:sql}
CREATE OR REPLACE TABLE t1 (
  a LONGTEXT CHARACTER SET latin1 COLLATE latin1_swedish_nopad_ci,
  UNIQUE KEY(a(33000)) USING HASH
);
INSERT INTO t1 VALUES (CONCAT('td',SPACE(31696)));
INSERT INTO t1 VALUES (CONCAT('td',SPACE(31728)));
{code}
{noformat}
ERROR 1062 (23000): Duplicate entry 'td ...' for key 'a'
{noformat}

The two inserted strings cannot be equal because this is a NOPAD collation - different amounts of trailing spaces make the longer string greater. Therefore, the duplicate entry error is not correct.

The same problem is repeatable with these two strings:
{code:sql}
CREATE OR REPLACE TABLE t1 (
  a LONGTEXT CHARACTER SET latin1 COLLATE latin1_swedish_nopad_ci,
  UNIQUE KEY(a(33000)) USING HASH
);
INSERT INTO t1 VALUES (CONCAT('xh',SPACE(32457)));
INSERT INTO t1 VALUES (CONCAT('xh',SPACE(32489)));
{code}
{noformat}
ERROR 1062 (23000): Duplicate entry 'xh ...' for key 'a'
{noformat}

h2. Problem details

These two data pairs are special - they were found programatically.

The idea is that these two expressions produce the same hash value 10067658370460279189 in the collation latin1_swedish_nopad_ci:
- {{CONCAT('td',SPACE(31696))}}
- {{CONCAT('td',SPACE(31728))}}

The problem is that the further duplicate hash resolution (to know if the actual values are really equal) erroneously treat these strings as equal.

The other two expressions also produce equal hash value 1452848758766898498 in the collation latin1_swedish_nopad_ci:
- {{CONCAT('xh',SPACE(32457))}}
- {{CONCAT('xh',SPACE(32489))}}

This script returns an unexpected duplicate entry error:

{code:sql}
CREATE OR REPLACE TABLE t1 (
  a LONGTEXT CHARACTER SET latin1 COLLATE latin1_swedish_nopad_ci,
  UNIQUE KEY(a(33000)) USING HASH
);
INSERT INTO t1 VALUES (CONCAT('td',SPACE(31696)));
INSERT INTO t1 VALUES (CONCAT('td',SPACE(31728)));
{code}
{noformat}
ERROR 1062 (23000): Duplicate entry 'td ...' for key 'a'
{noformat}

The two inserted strings cannot be equal because this is a NOPAD collation - different amounts of trailing spaces make the longer string greater. Therefore, the duplicate entry error is not correct.

The same problem is repeatable with these two strings:
{code:sql}
CREATE OR REPLACE TABLE t1 (
  a LONGTEXT CHARACTER SET latin1 COLLATE latin1_swedish_nopad_ci,
  UNIQUE KEY(a(33000)) USING HASH
);
INSERT INTO t1 VALUES (CONCAT('xh',SPACE(32457)));
INSERT INTO t1 VALUES (CONCAT('xh',SPACE(32489)));
{code}
{noformat}
ERROR 1062 (23000): Duplicate entry 'xh ...' for key 'a'
{noformat}

h2. Problem details

These two data pairs are special - they were found programatically.

The idea is that these two expressions produce the same hash value 10067658370460279189 in the collation latin1_swedish_nopad_ci:
- {{CONCAT('td',SPACE(31696))}}
- {{CONCAT('td',SPACE(31728))}}

The problem is that the further duplicate hash resolution (to know if the actual values are really equal) erroneously treat these strings as equal using strnncollsp_nchars(). Using this function is correct for the CHAR data type, but it's not correct for VARCHAR/TEXT.

The other two expressions also produce equal hash value 1452848758766898498 in the collation latin1_swedish_nopad_ci:
- {{CONCAT('xh',SPACE(32457))}}
- {{CONCAT('xh',SPACE(32489))}}

Alexander Barkov made changes - 2022-11-25 15:13

Description

This script returns an unexpected duplicate entry error:

{code:sql}
CREATE OR REPLACE TABLE t1 (
  a LONGTEXT CHARACTER SET latin1 COLLATE latin1_swedish_nopad_ci,
  UNIQUE KEY(a(33000)) USING HASH
);
INSERT INTO t1 VALUES (CONCAT('td',SPACE(31696)));
INSERT INTO t1 VALUES (CONCAT('td',SPACE(31728)));
{code}
{noformat}
ERROR 1062 (23000): Duplicate entry 'td ...' for key 'a'
{noformat}

The two inserted strings cannot be equal because this is a NOPAD collation - different amounts of trailing spaces make the longer string greater. Therefore, the duplicate entry error is not correct.

The same problem is repeatable with these two strings:
{code:sql}
CREATE OR REPLACE TABLE t1 (
  a LONGTEXT CHARACTER SET latin1 COLLATE latin1_swedish_nopad_ci,
  UNIQUE KEY(a(33000)) USING HASH
);
INSERT INTO t1 VALUES (CONCAT('xh',SPACE(32457)));
INSERT INTO t1 VALUES (CONCAT('xh',SPACE(32489)));
{code}
{noformat}
ERROR 1062 (23000): Duplicate entry 'xh ...' for key 'a'
{noformat}

h2. Problem details

These two data pairs are special - they were found programatically.

The idea is that these two expressions produce the same hash value 10067658370460279189 in the collation latin1_swedish_nopad_ci:
- {{CONCAT('td',SPACE(31696))}}
- {{CONCAT('td',SPACE(31728))}}

The problem is that the further duplicate hash resolution (to know if the actual values are really equal) erroneously treat these strings as equal using strnncollsp_nchars(). Using this function is correct for the CHAR data type, but it's not correct for VARCHAR/TEXT.

The other two expressions also produce equal hash value 1452848758766898498 in the collation latin1_swedish_nopad_ci:
- {{CONCAT('xh',SPACE(32457))}}
- {{CONCAT('xh',SPACE(32489))}}

This script returns an unexpected duplicate entry error:

{code:sql}
CREATE OR REPLACE TABLE t1 (
  a LONGTEXT CHARACTER SET latin1 COLLATE latin1_swedish_nopad_ci,
  UNIQUE KEY(a(33000)) USING HASH
);
INSERT INTO t1 VALUES (CONCAT('td',SPACE(31696)));
INSERT INTO t1 VALUES (CONCAT('td',SPACE(31728)));
{code}
{noformat}
ERROR 1062 (23000): Duplicate entry 'td ...' for key 'a'
{noformat}

The two inserted strings cannot be equal because this is a NOPAD collation - different amounts of trailing spaces make the longer string greater. Therefore, the duplicate entry error is not correct.

The same problem is repeatable with these two strings:
{code:sql}
CREATE OR REPLACE TABLE t1 (
  a LONGTEXT CHARACTER SET latin1 COLLATE latin1_swedish_nopad_ci,
  UNIQUE KEY(a(33000)) USING HASH
);
INSERT INTO t1 VALUES (CONCAT('xh',SPACE(32457)));
INSERT INTO t1 VALUES (CONCAT('xh',SPACE(32489)));
{code}
{noformat}
ERROR 1062 (23000): Duplicate entry 'xh ...' for key 'a'
{noformat}

h2. Problem details

These two data pairs are special - they were found programatically.

The idea is that these two expressions produce the same hash value 10067658370460279189 in the collation latin1_swedish_nopad_ci:
- {{CONCAT('td',SPACE(31696))}}
- {{CONCAT('td',SPACE(31728))}}

The problem is that the further duplicate hash resolution (performed to know if the actual values are really equal) erroneously treat these strings as equal using strnncollsp_nchars(). Using this function is correct for the CHAR data type, but it's not correct for VARCHAR/TEXT.

The other two expressions also produce equal hash value 1452848758766898498 in the collation latin1_swedish_nopad_ci:
- {{CONCAT('xh',SPACE(32457))}}
- {{CONCAT('xh',SPACE(32489))}}

Marko Mäkelä made changes - 2024-04-03 05:39

Link

This issue relates to ~~MDEV-33812~~ [ ~~MDEV-33812~~ ]

People

Assignee:: Alexander Barkov

Reporter:: Alexander Barkov

Votes:: 1 Vote for this issue

Watchers:: 3 Start watching this issue

Dates

Created:: 2022-11-25 15:05

Updated:: 2024-04-03 05:39

Git Integration

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

MariaDB Server

Details

Description

Problem details

Attachments

Issue Links

Activity

People

Dates

Git Integration