[MDEV-30048] Prefix keys for CHAR work differently for MyISAM vs InnoDB - Jira

Alexander Barkov created issue - 2022-11-21 06:11

Alexander Barkov made changes - 2022-11-21 06:12

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

Alexander Barkov made changes - 2022-11-21 06:12

Link

This issue blocks ~~MDEV-27670~~ [ ~~MDEV-27670~~ ]

Alexander Barkov made changes - 2022-11-21 06:13

Link

This issue blocks ~~MDEV-27768~~ [ ~~MDEV-27768~~ ]

Alexander Barkov made changes - 2022-11-21 06:13

Priority

Major [ 3 ]

Critical [ 2 ]

Alexander Barkov made changes - 2022-11-21 06:20

Description

{code:sql}
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (a CHAR(10) COLLATE utf8mb3_unicode_nopad_ci, UNIQUE KEY(a(2))) ENGINE=MyISAM;
INSERT INTO t1 VALUES ('ss'),('ß');
{code}
{noformat}
ERROR 1062 (23000): Duplicate entry 'ß' for key 'a'
{noformat}

{code:sql}
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (a CHAR(10) COLLATE utf8mb3_unicode_nopad_ci, UNIQUE KEY(a(2))) ENGINE=InnoDB;
INSERT INTO t1 VALUES ('ss'),('ß');
{code}
{noformat}
Query OK, 2 rows affected (0.002 sec)
Records: 2 Duplicates: 0 Warnings: 0
{noformat}

{code:sql}
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (a CHAR(10) COLLATE utf8mb3_unicode_nopad_ci, UNIQUE KEY(a)) ENGINE=MyISAM;
INSERT INTO t1 VALUES ('ss'),('ß');
{code}
{noformat}
ERROR 1062 (23000): Duplicate entry 'ß' for key 'a'
{noformat}

{code:sql}
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (a CHAR(10) COLLATE utf8mb3_unicode_nopad_ci, UNIQUE KEY(a)) ENGINE=InnoDB;
INSERT INTO t1 VALUES ('ss'),('ß');
{code}
{noformat}
Query OK, 2 rows affected (0.002 sec)
Records: 2 Duplicates: 0 Warnings: 0
{noformat}

The same problem is repeatable with prefix keys:
{code:sql}
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (a CHAR(10) COLLATE utf8mb3_unicode_nopad_ci, UNIQUE KEY(a(2))) ENGINE=MyISAM;
INSERT INTO t1 VALUES ('ss'),('ß');
{code}
{noformat}
ERROR 1062 (23000): Duplicate entry 'ß' for key 'a'
{noformat}

{code:sql}
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (a CHAR(10) COLLATE utf8mb3_unicode_nopad_ci, UNIQUE KEY(a(2))) ENGINE=InnoDB;
INSERT INTO t1 VALUES ('ss'),('ß');
{code}
{noformat}
Query OK, 2 rows affected (0.002 sec)
Records: 2 Duplicates: 0 Warnings: 0
{noformat}

Alexander Barkov made changes - 2022-11-21 06:20

Description

{code:sql}
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (a CHAR(10) COLLATE utf8mb3_unicode_nopad_ci, UNIQUE KEY(a)) ENGINE=MyISAM;
INSERT INTO t1 VALUES ('ss'),('ß');
{code}
{noformat}
ERROR 1062 (23000): Duplicate entry 'ß' for key 'a'
{noformat}

{code:sql}
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (a CHAR(10) COLLATE utf8mb3_unicode_nopad_ci, UNIQUE KEY(a)) ENGINE=InnoDB;
INSERT INTO t1 VALUES ('ss'),('ß');
{code}
{noformat}
Query OK, 2 rows affected (0.002 sec)
Records: 2 Duplicates: 0 Warnings: 0
{noformat}

The same problem is repeatable with prefix keys:
{code:sql}
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (a CHAR(10) COLLATE utf8mb3_unicode_nopad_ci, UNIQUE KEY(a(2))) ENGINE=MyISAM;
INSERT INTO t1 VALUES ('ss'),('ß');
{code}
{noformat}
ERROR 1062 (23000): Duplicate entry 'ß' for key 'a'
{noformat}

{code:sql}
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (a CHAR(10) COLLATE utf8mb3_unicode_nopad_ci, UNIQUE KEY(a(2))) ENGINE=InnoDB;
INSERT INTO t1 VALUES ('ss'),('ß');
{code}
{noformat}
Query OK, 2 rows affected (0.002 sec)
Records: 2 Duplicates: 0 Warnings: 0
{noformat}

{code:sql}
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (a CHAR(10) COLLATE utf8mb3_unicode_nopad_ci, UNIQUE KEY(a)) ENGINE=MyISAM;
INSERT INTO t1 VALUES ('ss'),('ß');
{code}
{noformat}
ERROR 1062 (23000): Duplicate entry 'ß' for key 'a'
{noformat}

{code:sql}
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (a CHAR(10) COLLATE utf8mb3_unicode_nopad_ci, UNIQUE KEY(a)) ENGINE=InnoDB;
INSERT INTO t1 VALUES ('ss'),('ß');
{code}
{noformat}
Query OK, 2 rows affected (0.002 sec)
Records: 2 Duplicates: 0 Warnings: 0
{noformat}

h3. The same problem is repeatable with prefix keys:
{code:sql}
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (a CHAR(10) COLLATE utf8mb3_unicode_nopad_ci, UNIQUE KEY(a(2))) ENGINE=MyISAM;
INSERT INTO t1 VALUES ('ss'),('ß');
{code}
{noformat}
ERROR 1062 (23000): Duplicate entry 'ß' for key 'a'
{noformat}

{code:sql}
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (a CHAR(10) COLLATE utf8mb3_unicode_nopad_ci, UNIQUE KEY(a(2))) ENGINE=InnoDB;
INSERT INTO t1 VALUES ('ss'),('ß');
{code}
{noformat}
Query OK, 2 rows affected (0.002 sec)
Records: 2 Duplicates: 0 Warnings: 0
{noformat}

Alexander Barkov made changes - 2022-11-21 06:20

Description

{code:sql}
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (a CHAR(10) COLLATE utf8mb3_unicode_nopad_ci, UNIQUE KEY(a)) ENGINE=MyISAM;
INSERT INTO t1 VALUES ('ss'),('ß');
{code}
{noformat}
ERROR 1062 (23000): Duplicate entry 'ß' for key 'a'
{noformat}

{code:sql}
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (a CHAR(10) COLLATE utf8mb3_unicode_nopad_ci, UNIQUE KEY(a)) ENGINE=InnoDB;
INSERT INTO t1 VALUES ('ss'),('ß');
{code}
{noformat}
Query OK, 2 rows affected (0.002 sec)
Records: 2 Duplicates: 0 Warnings: 0
{noformat}

h3. The same problem is repeatable with prefix keys:
{code:sql}
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (a CHAR(10) COLLATE utf8mb3_unicode_nopad_ci, UNIQUE KEY(a(2))) ENGINE=MyISAM;
INSERT INTO t1 VALUES ('ss'),('ß');
{code}
{noformat}
ERROR 1062 (23000): Duplicate entry 'ß' for key 'a'
{noformat}

{code:sql}
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (a CHAR(10) COLLATE utf8mb3_unicode_nopad_ci, UNIQUE KEY(a(2))) ENGINE=InnoDB;
INSERT INTO t1 VALUES ('ss'),('ß');
{code}
{noformat}
Query OK, 2 rows affected (0.002 sec)
Records: 2 Duplicates: 0 Warnings: 0
{noformat}

{code:sql}
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (a CHAR(10) COLLATE utf8mb3_unicode_nopad_ci, UNIQUE KEY(a)) ENGINE=MyISAM;
INSERT INTO t1 VALUES ('ss'),('ß');
{code}
{noformat}
ERROR 1062 (23000): Duplicate entry 'ß' for key 'a'
{noformat}

{code:sql}
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (a CHAR(10) COLLATE utf8mb3_unicode_nopad_ci, UNIQUE KEY(a)) ENGINE=InnoDB;
INSERT INTO t1 VALUES ('ss'),('ß');
{code}
{noformat}
Query OK, 2 rows affected (0.002 sec)
Records: 2 Duplicates: 0 Warnings: 0
{noformat}

h2. The same problem is repeatable with prefix keys:
{code:sql}
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (a CHAR(10) COLLATE utf8mb3_unicode_nopad_ci, UNIQUE KEY(a(2))) ENGINE=MyISAM;
INSERT INTO t1 VALUES ('ss'),('ß');
{code}
{noformat}
ERROR 1062 (23000): Duplicate entry 'ß' for key 'a'
{noformat}

{code:sql}
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (a CHAR(10) COLLATE utf8mb3_unicode_nopad_ci, UNIQUE KEY(a(2))) ENGINE=InnoDB;
INSERT INTO t1 VALUES ('ss'),('ß');
{code}
{noformat}
Query OK, 2 rows affected (0.002 sec)
Records: 2 Duplicates: 0 Warnings: 0
{noformat}

Alexander Barkov made changes - 2022-11-21 07:58

Link

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

Alexander Barkov made changes - 2022-11-21 07:59

Link

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

Alexander Barkov made changes - 2022-11-21 07:59

Link

This issue blocks ~~MDEV-27670~~ [ ~~MDEV-27670~~ ]

Alexander Barkov made changes - 2022-11-21 07:59

Link

This issue blocks ~~MDEV-27768~~ [ ~~MDEV-27768~~ ]

Alexander Barkov added a comment - 2022-11-21 10:17 - edited

Heap works like InnoDB (this fact is now also noted in above description) :

DROP TABLE IF EXISTS t1;

CREATE TABLE t1 (a CHAR(10) COLLATE utf8mb3_unicode_nopad_ci, UNIQUE KEY(a)) ENGINE=HEAP;

INSERT INTO t1 VALUES ('ss'),('ß');

Query OK, 2 rows affected (0.000 sec)

Records: 2  Duplicates: 0  Warnings: 0

Alexander Barkov added a comment - 2022-11-21 10:17 - edited Heap works like InnoDB (this fact is now also noted in above description) : DROP TABLE IF EXISTS t1; CREATE TABLE t1 (a CHAR (10) COLLATE utf8mb3_unicode_nopad_ci, UNIQUE KEY (a)) ENGINE=HEAP; INSERT INTO t1 VALUES ( 'ss' ),( 'ß' ); Query OK, 2 rows affected (0.000 sec) Records: 2 Duplicates: 0 Warnings: 0

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

Description

{code:sql}
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (a CHAR(10) COLLATE utf8mb3_unicode_nopad_ci, UNIQUE KEY(a)) ENGINE=MyISAM;
INSERT INTO t1 VALUES ('ss'),('ß');
{code}
{noformat}
ERROR 1062 (23000): Duplicate entry 'ß' for key 'a'
{noformat}

{code:sql}
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (a CHAR(10) COLLATE utf8mb3_unicode_nopad_ci, UNIQUE KEY(a)) ENGINE=InnoDB;
INSERT INTO t1 VALUES ('ss'),('ß');
{code}
{noformat}
Query OK, 2 rows affected (0.002 sec)
Records: 2 Duplicates: 0 Warnings: 0
{noformat}

h2. The same problem is repeatable with prefix keys:
{code:sql}
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (a CHAR(10) COLLATE utf8mb3_unicode_nopad_ci, UNIQUE KEY(a(2))) ENGINE=MyISAM;
INSERT INTO t1 VALUES ('ss'),('ß');
{code}
{noformat}
ERROR 1062 (23000): Duplicate entry 'ß' for key 'a'
{noformat}

{code:sql}
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (a CHAR(10) COLLATE utf8mb3_unicode_nopad_ci, UNIQUE KEY(a(2))) ENGINE=InnoDB;
INSERT INTO t1 VALUES ('ss'),('ß');
{code}
{noformat}
Query OK, 2 rows affected (0.002 sec)
Records: 2 Duplicates: 0 Warnings: 0
{noformat}

{code:sql}
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (a CHAR(10) COLLATE utf8mb3_unicode_nopad_ci, UNIQUE KEY(a)) ENGINE=MyISAM;
INSERT INTO t1 VALUES ('ss'),('ß');
{code}
{noformat}
ERROR 1062 (23000): Duplicate entry 'ß' for key 'a'
{noformat}

{code:sql}
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (a CHAR(10) COLLATE utf8mb3_unicode_nopad_ci, UNIQUE KEY(a)) ENGINE=InnoDB;
INSERT INTO t1 VALUES ('ss'),('ß');
{code}
{noformat}
Query OK, 2 rows affected (0.002 sec)
Records: 2 Duplicates: 0 Warnings: 0
{noformat}

InnoDB works correctly.

h2. Rationale

We compare two fixed length strings with 10 characters:
- Two characters 'ss' followed by 8 spaces
- One 'ß' character followed by 9 spaces

The comparison of 'ss' and 'ß' should work as follows for CHAR(10):
- 'ss' is equal to 'ß' in this collation
- 8 spaces are smaller than 9 spaces (because this is a NOPAD collation)
- the result is "smaller"

Note, left join works correctly:
{code:sql}
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (a CHAR(10) COLLATE utf8mb3_unicode_nopad_ci, UNIQUE KEY(a)) ENGINE=InnoDB;
INSERT INTO t1 VALUES ('ss'),('ß');
select * from t1, t1 as t2 where t1.a=t2.a;
{code}
{noformat}
+------+------+
| a | a |
+------+------+
| ss | ss |
| ß | ß |
+------+------+
{noformat}

h2. The same problem is repeatable with prefix keys:
{code:sql}
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (a CHAR(10) COLLATE utf8mb3_unicode_nopad_ci, UNIQUE KEY(a(2))) ENGINE=MyISAM;
INSERT INTO t1 VALUES ('ss'),('ß');
{code}
{noformat}
ERROR 1062 (23000): Duplicate entry 'ß' for key 'a'
{noformat}

{code:sql}
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (a CHAR(10) COLLATE utf8mb3_unicode_nopad_ci, UNIQUE KEY(a(2))) ENGINE=InnoDB;
INSERT INTO t1 VALUES ('ss'),('ß');
{code}
{noformat}
Query OK, 2 rows affected (0.002 sec)
Records: 2 Duplicates: 0 Warnings: 0
{noformat}

Alexander Barkov made changes - 2023-03-31 14:27

Link

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

Alexander Barkov made changes - 2023-03-31 14:29

Link

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

Alexander Barkov made changes - 2023-03-31 14:30

Link

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

Alexander Barkov made changes - 2023-04-07 09:27

Description

{code:sql}
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (a CHAR(10) COLLATE utf8mb3_unicode_nopad_ci, UNIQUE KEY(a)) ENGINE=MyISAM;
INSERT INTO t1 VALUES ('ss'),('ß');
{code}
{noformat}
ERROR 1062 (23000): Duplicate entry 'ß' for key 'a'
{noformat}

{code:sql}
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (a CHAR(10) COLLATE utf8mb3_unicode_nopad_ci, UNIQUE KEY(a)) ENGINE=InnoDB;
INSERT INTO t1 VALUES ('ss'),('ß');
{code}
{noformat}
Query OK, 2 rows affected (0.002 sec)
Records: 2 Duplicates: 0 Warnings: 0
{noformat}

InnoDB works correctly.

h2. Rationale

We compare two fixed length strings with 10 characters:
- Two characters 'ss' followed by 8 spaces
- One 'ß' character followed by 9 spaces

The comparison of 'ss' and 'ß' should work as follows for CHAR(10):
- 'ss' is equal to 'ß' in this collation
- 8 spaces are smaller than 9 spaces (because this is a NOPAD collation)
- the result is "smaller"

Note, left join works correctly:
{code:sql}
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (a CHAR(10) COLLATE utf8mb3_unicode_nopad_ci, UNIQUE KEY(a)) ENGINE=InnoDB;
INSERT INTO t1 VALUES ('ss'),('ß');
select * from t1, t1 as t2 where t1.a=t2.a;
{code}
{noformat}
+------+------+
| a | a |
+------+------+
| ss | ss |
| ß | ß |
+------+------+
{noformat}

h2. The same problem is repeatable with prefix keys:
{code:sql}
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (a CHAR(10) COLLATE utf8mb3_unicode_nopad_ci, UNIQUE KEY(a(2))) ENGINE=MyISAM;
INSERT INTO t1 VALUES ('ss'),('ß');
{code}
{noformat}
ERROR 1062 (23000): Duplicate entry 'ß' for key 'a'
{noformat}

{code:sql}
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (a CHAR(10) COLLATE utf8mb3_unicode_nopad_ci, UNIQUE KEY(a(2))) ENGINE=InnoDB;
INSERT INTO t1 VALUES ('ss'),('ß');
{code}
{noformat}
Query OK, 2 rows affected (0.002 sec)
Records: 2 Duplicates: 0 Warnings: 0
{noformat}

{code:sql}
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (a CHAR(10) COLLATE utf8mb3_unicode_nopad_ci, UNIQUE KEY(a)) ENGINE=MyISAM;
INSERT INTO t1 VALUES ('ss'),('ß');
{code}
{noformat}
ERROR 1062 (23000): Duplicate entry 'ß' for key 'a'
{noformat}

{code:sql}
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (a CHAR(10) COLLATE utf8mb3_unicode_nopad_ci, UNIQUE KEY(a)) ENGINE=InnoDB;
INSERT INTO t1 VALUES ('ss'),('ß');
{code}
{noformat}
Query OK, 2 rows affected (0.002 sec)
Records: 2 Duplicates: 0 Warnings: 0
{noformat}

InnoDB works correctly.

h2. Rationale

We compare two fixed length strings with 10 characters:
- Two characters 'ss' followed by 8 spaces
- One 'ß' character followed by 9 spaces

The comparison of 'ss' and 'ß' should work as follows for CHAR(10):
- 'ss' is equal to 'ß' in this collation
- 8 spaces are smaller than 9 spaces (because this is a NOPAD collation)
- the result is "smaller"

Note, left join works correctly:
{code:sql}
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (a CHAR(10) COLLATE utf8mb3_unicode_nopad_ci, UNIQUE KEY(a)) ENGINE=InnoDB;
INSERT INTO t1 VALUES ('ss'),('ß');
select * from t1, t1 as t2 where t1.a=t2.a;
{code}
{noformat}
+------+------+
| a | a |
+------+------+
| ss | ss |
| ß | ß |
+------+------+
{noformat}

h2. The same problem is repeatable with ENGINE=ARIA

{code:sql}
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (a CHAR(10) COLLATE utf8mb3_unicode_nopad_ci, UNIQUE KEY(a)) ENGINE=Aria;
INSERT INTO t1 VALUES ('ss'),('ß');
{code}
{noformat}
ERROR 1062 (23000): Duplicate entry 'ß' for key 'a'
{noformat}

h2. The same problem is repeatable with prefix keys:
{code:sql}
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (a CHAR(10) COLLATE utf8mb3_unicode_nopad_ci, UNIQUE KEY(a(2))) ENGINE=MyISAM;
INSERT INTO t1 VALUES ('ss'),('ß');
{code}
{noformat}
ERROR 1062 (23000): Duplicate entry 'ß' for key 'a'
{noformat}

{code:sql}
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (a CHAR(10) COLLATE utf8mb3_unicode_nopad_ci, UNIQUE KEY(a(2))) ENGINE=InnoDB;
INSERT INTO t1 VALUES ('ss'),('ß');
{code}
{noformat}
Query OK, 2 rows affected (0.002 sec)
Records: 2 Duplicates: 0 Warnings: 0
{noformat}

Alexander Barkov made changes - 2023-04-07 09:29

Description

{code:sql}
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (a CHAR(10) COLLATE utf8mb3_unicode_nopad_ci, UNIQUE KEY(a)) ENGINE=MyISAM;
INSERT INTO t1 VALUES ('ss'),('ß');
{code}
{noformat}
ERROR 1062 (23000): Duplicate entry 'ß' for key 'a'
{noformat}

{code:sql}
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (a CHAR(10) COLLATE utf8mb3_unicode_nopad_ci, UNIQUE KEY(a)) ENGINE=InnoDB;
INSERT INTO t1 VALUES ('ss'),('ß');
{code}
{noformat}
Query OK, 2 rows affected (0.002 sec)
Records: 2 Duplicates: 0 Warnings: 0
{noformat}

InnoDB works correctly.

h2. Rationale

We compare two fixed length strings with 10 characters:
- Two characters 'ss' followed by 8 spaces
- One 'ß' character followed by 9 spaces

The comparison of 'ss' and 'ß' should work as follows for CHAR(10):
- 'ss' is equal to 'ß' in this collation
- 8 spaces are smaller than 9 spaces (because this is a NOPAD collation)
- the result is "smaller"

Note, left join works correctly:
{code:sql}
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (a CHAR(10) COLLATE utf8mb3_unicode_nopad_ci, UNIQUE KEY(a)) ENGINE=InnoDB;
INSERT INTO t1 VALUES ('ss'),('ß');
select * from t1, t1 as t2 where t1.a=t2.a;
{code}
{noformat}
+------+------+
| a | a |
+------+------+
| ss | ss |
| ß | ß |
+------+------+
{noformat}

h2. The same problem is repeatable with ENGINE=ARIA

{code:sql}
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (a CHAR(10) COLLATE utf8mb3_unicode_nopad_ci, UNIQUE KEY(a)) ENGINE=Aria;
INSERT INTO t1 VALUES ('ss'),('ß');
{code}
{noformat}
ERROR 1062 (23000): Duplicate entry 'ß' for key 'a'
{noformat}

h2. The same problem is repeatable with prefix keys:
{code:sql}
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (a CHAR(10) COLLATE utf8mb3_unicode_nopad_ci, UNIQUE KEY(a(2))) ENGINE=MyISAM;
INSERT INTO t1 VALUES ('ss'),('ß');
{code}
{noformat}
ERROR 1062 (23000): Duplicate entry 'ß' for key 'a'
{noformat}

{code:sql}
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (a CHAR(10) COLLATE utf8mb3_unicode_nopad_ci, UNIQUE KEY(a(2))) ENGINE=InnoDB;
INSERT INTO t1 VALUES ('ss'),('ß');
{code}
{noformat}
Query OK, 2 rows affected (0.002 sec)
Records: 2 Duplicates: 0 Warnings: 0
{noformat}

{code:sql}
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (a CHAR(10) COLLATE utf8mb3_unicode_nopad_ci, UNIQUE KEY(a)) ENGINE=MyISAM;
INSERT INTO t1 VALUES ('ss'),('ß');
{code}
{noformat}
ERROR 1062 (23000): Duplicate entry 'ß' for key 'a'
{noformat}

{code:sql}
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (a CHAR(10) COLLATE utf8mb3_unicode_nopad_ci, UNIQUE KEY(a)) ENGINE=InnoDB;
INSERT INTO t1 VALUES ('ss'),('ß');
{code}
{noformat}
Query OK, 2 rows affected (0.002 sec)
Records: 2 Duplicates: 0 Warnings: 0
{noformat}

InnoDB works correctly.

h2. Rationale

We compare two fixed length strings with 10 characters:
- Two characters 'ss' followed by 8 spaces
- One 'ß' character followed by 9 spaces

The comparison of 'ss' and 'ß' should work as follows for CHAR(10):
- 'ss' is equal to 'ß' in this collation
- 8 spaces are smaller than 9 spaces (because this is a NOPAD collation)
- the result is "smaller"

Note, left join works correctly:
{code:sql}
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (a CHAR(10) COLLATE utf8mb3_unicode_nopad_ci, UNIQUE KEY(a)) ENGINE=InnoDB;
INSERT INTO t1 VALUES ('ss'),('ß');
select * from t1, t1 as t2 where t1.a=t2.a;
{code}
{noformat}
+------+------+
| a | a |
+------+------+
| ss | ss |
| ß | ß |
+------+------+
{noformat}

h2. The same problem is repeatable with ENGINE=ARIA

{code:sql}
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (a CHAR(10) COLLATE utf8mb3_unicode_nopad_ci, UNIQUE KEY(a)) ENGINE=Aria;
INSERT INTO t1 VALUES ('ss'),('ß');
{code}
{noformat}
ERROR 1062 (23000): Duplicate entry 'ß' for key 'a'
{noformat}

h2. The same problem is repeatable with prefix keys:
{code:sql}
# MyISAM is wrong
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (a CHAR(10) COLLATE utf8mb3_unicode_nopad_ci, UNIQUE KEY(a(2))) ENGINE=MyISAM;
INSERT INTO t1 VALUES ('ss'),('ß');
{code}
{noformat}
ERROR 1062 (23000): Duplicate entry 'ß' for key 'a'
{noformat}

{code:sql}
# Aria is wrong
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (a CHAR(10) COLLATE utf8mb3_unicode_nopad_ci, UNIQUE KEY(a(2))) ENGINE=ARIA;
INSERT INTO t1 VALUES ('ss'),('ß');
{code}
{noformat}
ERROR 1062 (23000): Duplicate entry 'ß' for key 'a'
{noformat}

{code:sql}
# InnoDB is correct
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (a CHAR(10) COLLATE utf8mb3_unicode_nopad_ci, UNIQUE KEY(a(2))) ENGINE=InnoDB;
INSERT INTO t1 VALUES ('ss'),('ß');
{code}
{noformat}
Query OK, 2 rows affected (0.002 sec)
Records: 2 Duplicates: 0 Warnings: 0
{noformat}

Alexander Barkov made changes - 2023-04-07 09:30

Description

{code:sql}
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (a CHAR(10) COLLATE utf8mb3_unicode_nopad_ci, UNIQUE KEY(a)) ENGINE=MyISAM;
INSERT INTO t1 VALUES ('ss'),('ß');
{code}
{noformat}
ERROR 1062 (23000): Duplicate entry 'ß' for key 'a'
{noformat}

{code:sql}
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (a CHAR(10) COLLATE utf8mb3_unicode_nopad_ci, UNIQUE KEY(a)) ENGINE=InnoDB;
INSERT INTO t1 VALUES ('ss'),('ß');
{code}
{noformat}
Query OK, 2 rows affected (0.002 sec)
Records: 2 Duplicates: 0 Warnings: 0
{noformat}

InnoDB works correctly.

h2. Rationale

We compare two fixed length strings with 10 characters:
- Two characters 'ss' followed by 8 spaces
- One 'ß' character followed by 9 spaces

The comparison of 'ss' and 'ß' should work as follows for CHAR(10):
- 'ss' is equal to 'ß' in this collation
- 8 spaces are smaller than 9 spaces (because this is a NOPAD collation)
- the result is "smaller"

Note, left join works correctly:
{code:sql}
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (a CHAR(10) COLLATE utf8mb3_unicode_nopad_ci, UNIQUE KEY(a)) ENGINE=InnoDB;
INSERT INTO t1 VALUES ('ss'),('ß');
select * from t1, t1 as t2 where t1.a=t2.a;
{code}
{noformat}
+------+------+
| a | a |
+------+------+
| ss | ss |
| ß | ß |
+------+------+
{noformat}

h2. The same problem is repeatable with ENGINE=ARIA

{code:sql}
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (a CHAR(10) COLLATE utf8mb3_unicode_nopad_ci, UNIQUE KEY(a)) ENGINE=Aria;
INSERT INTO t1 VALUES ('ss'),('ß');
{code}
{noformat}
ERROR 1062 (23000): Duplicate entry 'ß' for key 'a'
{noformat}

h2. The same problem is repeatable with prefix keys:
{code:sql}
# MyISAM is wrong
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (a CHAR(10) COLLATE utf8mb3_unicode_nopad_ci, UNIQUE KEY(a(2))) ENGINE=MyISAM;
INSERT INTO t1 VALUES ('ss'),('ß');
{code}
{noformat}
ERROR 1062 (23000): Duplicate entry 'ß' for key 'a'
{noformat}

{code:sql}
# Aria is wrong
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (a CHAR(10) COLLATE utf8mb3_unicode_nopad_ci, UNIQUE KEY(a(2))) ENGINE=ARIA;
INSERT INTO t1 VALUES ('ss'),('ß');
{code}
{noformat}
ERROR 1062 (23000): Duplicate entry 'ß' for key 'a'
{noformat}

{code:sql}
# InnoDB is correct
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (a CHAR(10) COLLATE utf8mb3_unicode_nopad_ci, UNIQUE KEY(a(2))) ENGINE=InnoDB;
INSERT INTO t1 VALUES ('ss'),('ß');
{code}
{noformat}
Query OK, 2 rows affected (0.002 sec)
Records: 2 Duplicates: 0 Warnings: 0
{noformat}

{code:sql}
# MyISAM is wrong
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (a CHAR(10) COLLATE utf8mb3_unicode_nopad_ci, UNIQUE KEY(a)) ENGINE=MyISAM;
INSERT INTO t1 VALUES ('ss'),('ß');
{code}
{noformat}
ERROR 1062 (23000): Duplicate entry 'ß' for key 'a'
{noformat}

{code:sql}
# InnoDB is correct
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (a CHAR(10) COLLATE utf8mb3_unicode_nopad_ci, UNIQUE KEY(a)) ENGINE=InnoDB;
INSERT INTO t1 VALUES ('ss'),('ß');
{code}
{noformat}
Query OK, 2 rows affected (0.002 sec)
Records: 2 Duplicates: 0 Warnings: 0
{noformat}

InnoDB works correctly.

h2. Rationale

We compare two fixed length strings with 10 characters:
- Two characters 'ss' followed by 8 spaces
- One 'ß' character followed by 9 spaces

The comparison of 'ss' and 'ß' should work as follows for CHAR(10):
- 'ss' is equal to 'ß' in this collation
- 8 spaces are smaller than 9 spaces (because this is a NOPAD collation)
- the result is "smaller"

Note, left join works correctly:
{code:sql}
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (a CHAR(10) COLLATE utf8mb3_unicode_nopad_ci, UNIQUE KEY(a)) ENGINE=InnoDB;
INSERT INTO t1 VALUES ('ss'),('ß');
select * from t1, t1 as t2 where t1.a=t2.a;
{code}
{noformat}
+------+------+
| a | a |
+------+------+
| ss | ss |
| ß | ß |
+------+------+
{noformat}

h2. The same problem is repeatable with ENGINE=ARIA

{code:sql}
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (a CHAR(10) COLLATE utf8mb3_unicode_nopad_ci, UNIQUE KEY(a)) ENGINE=Aria;
INSERT INTO t1 VALUES ('ss'),('ß');
{code}
{noformat}
ERROR 1062 (23000): Duplicate entry 'ß' for key 'a'
{noformat}

h2. The same problem is repeatable with prefix keys:
{code:sql}
# MyISAM is wrong
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (a CHAR(10) COLLATE utf8mb3_unicode_nopad_ci, UNIQUE KEY(a(2))) ENGINE=MyISAM;
INSERT INTO t1 VALUES ('ss'),('ß');
{code}
{noformat}
ERROR 1062 (23000): Duplicate entry 'ß' for key 'a'
{noformat}

{code:sql}
# Aria is wrong
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (a CHAR(10) COLLATE utf8mb3_unicode_nopad_ci, UNIQUE KEY(a(2))) ENGINE=ARIA;
INSERT INTO t1 VALUES ('ss'),('ß');
{code}
{noformat}
ERROR 1062 (23000): Duplicate entry 'ß' for key 'a'
{noformat}

{code:sql}
# InnoDB is correct
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (a CHAR(10) COLLATE utf8mb3_unicode_nopad_ci, UNIQUE KEY(a(2))) ENGINE=InnoDB;
INSERT INTO t1 VALUES ('ss'),('ß');
{code}
{noformat}
Query OK, 2 rows affected (0.002 sec)
Records: 2 Duplicates: 0 Warnings: 0
{noformat}

Alexander Barkov made changes - 2023-04-07 09:31

Description

{code:sql}
# MyISAM is wrong
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (a CHAR(10) COLLATE utf8mb3_unicode_nopad_ci, UNIQUE KEY(a)) ENGINE=MyISAM;
INSERT INTO t1 VALUES ('ss'),('ß');
{code}
{noformat}
ERROR 1062 (23000): Duplicate entry 'ß' for key 'a'
{noformat}

{code:sql}
# InnoDB is correct
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (a CHAR(10) COLLATE utf8mb3_unicode_nopad_ci, UNIQUE KEY(a)) ENGINE=InnoDB;
INSERT INTO t1 VALUES ('ss'),('ß');
{code}
{noformat}
Query OK, 2 rows affected (0.002 sec)
Records: 2 Duplicates: 0 Warnings: 0
{noformat}

InnoDB works correctly.

h2. Rationale

We compare two fixed length strings with 10 characters:
- Two characters 'ss' followed by 8 spaces
- One 'ß' character followed by 9 spaces

The comparison of 'ss' and 'ß' should work as follows for CHAR(10):
- 'ss' is equal to 'ß' in this collation
- 8 spaces are smaller than 9 spaces (because this is a NOPAD collation)
- the result is "smaller"

Note, left join works correctly:
{code:sql}
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (a CHAR(10) COLLATE utf8mb3_unicode_nopad_ci, UNIQUE KEY(a)) ENGINE=InnoDB;
INSERT INTO t1 VALUES ('ss'),('ß');
select * from t1, t1 as t2 where t1.a=t2.a;
{code}
{noformat}
+------+------+
| a | a |
+------+------+
| ss | ss |
| ß | ß |
+------+------+
{noformat}

h2. The same problem is repeatable with ENGINE=ARIA

{code:sql}
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (a CHAR(10) COLLATE utf8mb3_unicode_nopad_ci, UNIQUE KEY(a)) ENGINE=Aria;
INSERT INTO t1 VALUES ('ss'),('ß');
{code}
{noformat}
ERROR 1062 (23000): Duplicate entry 'ß' for key 'a'
{noformat}

h2. The same problem is repeatable with prefix keys:
{code:sql}
# MyISAM is wrong
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (a CHAR(10) COLLATE utf8mb3_unicode_nopad_ci, UNIQUE KEY(a(2))) ENGINE=MyISAM;
INSERT INTO t1 VALUES ('ss'),('ß');
{code}
{noformat}
ERROR 1062 (23000): Duplicate entry 'ß' for key 'a'
{noformat}

{code:sql}
# Aria is wrong
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (a CHAR(10) COLLATE utf8mb3_unicode_nopad_ci, UNIQUE KEY(a(2))) ENGINE=ARIA;
INSERT INTO t1 VALUES ('ss'),('ß');
{code}
{noformat}
ERROR 1062 (23000): Duplicate entry 'ß' for key 'a'
{noformat}

{code:sql}
# InnoDB is correct
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (a CHAR(10) COLLATE utf8mb3_unicode_nopad_ci, UNIQUE KEY(a(2))) ENGINE=InnoDB;
INSERT INTO t1 VALUES ('ss'),('ß');
{code}
{noformat}
Query OK, 2 rows affected (0.002 sec)
Records: 2 Duplicates: 0 Warnings: 0
{noformat}

{code:sql}
# MyISAM is wrong
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (a CHAR(10) COLLATE utf8mb3_unicode_nopad_ci, UNIQUE KEY(a)) ENGINE=MyISAM;
INSERT INTO t1 VALUES ('ss'),('ß');
{code}
{noformat}
ERROR 1062 (23000): Duplicate entry 'ß' for key 'a'
{noformat}

{code:sql}
# Aria is wrong
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (a CHAR(10) COLLATE utf8mb3_unicode_nopad_ci, UNIQUE KEY(a)) ENGINE=Aria;
INSERT INTO t1 VALUES ('ss'),('ß');
{code}
{noformat}
ERROR 1062 (23000): Duplicate entry 'ß' for key 'a'
{noformat}

{code:sql}
# InnoDB is correct
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (a CHAR(10) COLLATE utf8mb3_unicode_nopad_ci, UNIQUE KEY(a)) ENGINE=InnoDB;
INSERT INTO t1 VALUES ('ss'),('ß');
{code}
{noformat}
Query OK, 2 rows affected (0.002 sec)
Records: 2 Duplicates: 0 Warnings: 0
{noformat}

InnoDB works correctly.

h2. Rationale

We compare two fixed length strings with 10 characters:
- Two characters 'ss' followed by 8 spaces
- One 'ß' character followed by 9 spaces

The comparison of 'ss' and 'ß' should work as follows for CHAR(10):
- 'ss' is equal to 'ß' in this collation
- 8 spaces are smaller than 9 spaces (because this is a NOPAD collation)
- the result is "smaller"

Note, left join works correctly:
{code:sql}
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (a CHAR(10) COLLATE utf8mb3_unicode_nopad_ci, UNIQUE KEY(a)) ENGINE=InnoDB;
INSERT INTO t1 VALUES ('ss'),('ß');
select * from t1, t1 as t2 where t1.a=t2.a;
{code}
{noformat}
+------+------+
| a | a |
+------+------+
| ss | ss |
| ß | ß |
+------+------+
{noformat}

h2. The same problem is repeatable with prefix keys:
{code:sql}
# MyISAM is wrong
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (a CHAR(10) COLLATE utf8mb3_unicode_nopad_ci, UNIQUE KEY(a(2))) ENGINE=MyISAM;
INSERT INTO t1 VALUES ('ss'),('ß');
{code}
{noformat}
ERROR 1062 (23000): Duplicate entry 'ß' for key 'a'
{noformat}

{code:sql}
# Aria is wrong
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (a CHAR(10) COLLATE utf8mb3_unicode_nopad_ci, UNIQUE KEY(a(2))) ENGINE=ARIA;
INSERT INTO t1 VALUES ('ss'),('ß');
{code}
{noformat}
ERROR 1062 (23000): Duplicate entry 'ß' for key 'a'
{noformat}

{code:sql}
# InnoDB is correct
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (a CHAR(10) COLLATE utf8mb3_unicode_nopad_ci, UNIQUE KEY(a(2))) ENGINE=InnoDB;
INSERT INTO t1 VALUES ('ss'),('ß');
{code}
{noformat}
Query OK, 2 rows affected (0.002 sec)
Records: 2 Duplicates: 0 Warnings: 0
{noformat}

Alexander Barkov made changes - 2023-04-07 09:33

Description

{code:sql}
# MyISAM is wrong
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (a CHAR(10) COLLATE utf8mb3_unicode_nopad_ci, UNIQUE KEY(a)) ENGINE=MyISAM;
INSERT INTO t1 VALUES ('ss'),('ß');
{code}
{noformat}
ERROR 1062 (23000): Duplicate entry 'ß' for key 'a'
{noformat}

{code:sql}
# Aria is wrong
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (a CHAR(10) COLLATE utf8mb3_unicode_nopad_ci, UNIQUE KEY(a)) ENGINE=Aria;
INSERT INTO t1 VALUES ('ss'),('ß');
{code}
{noformat}
ERROR 1062 (23000): Duplicate entry 'ß' for key 'a'
{noformat}

{code:sql}
# InnoDB is correct
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (a CHAR(10) COLLATE utf8mb3_unicode_nopad_ci, UNIQUE KEY(a)) ENGINE=InnoDB;
INSERT INTO t1 VALUES ('ss'),('ß');
{code}
{noformat}
Query OK, 2 rows affected (0.002 sec)
Records: 2 Duplicates: 0 Warnings: 0
{noformat}

InnoDB works correctly.

h2. Rationale

We compare two fixed length strings with 10 characters:
- Two characters 'ss' followed by 8 spaces
- One 'ß' character followed by 9 spaces

The comparison of 'ss' and 'ß' should work as follows for CHAR(10):
- 'ss' is equal to 'ß' in this collation
- 8 spaces are smaller than 9 spaces (because this is a NOPAD collation)
- the result is "smaller"

Note, left join works correctly:
{code:sql}
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (a CHAR(10) COLLATE utf8mb3_unicode_nopad_ci, UNIQUE KEY(a)) ENGINE=InnoDB;
INSERT INTO t1 VALUES ('ss'),('ß');
select * from t1, t1 as t2 where t1.a=t2.a;
{code}
{noformat}
+------+------+
| a | a |
+------+------+
| ss | ss |
| ß | ß |
+------+------+
{noformat}

h2. The same problem is repeatable with prefix keys:
{code:sql}
# MyISAM is wrong
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (a CHAR(10) COLLATE utf8mb3_unicode_nopad_ci, UNIQUE KEY(a(2))) ENGINE=MyISAM;
INSERT INTO t1 VALUES ('ss'),('ß');
{code}
{noformat}
ERROR 1062 (23000): Duplicate entry 'ß' for key 'a'
{noformat}

{code:sql}
# Aria is wrong
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (a CHAR(10) COLLATE utf8mb3_unicode_nopad_ci, UNIQUE KEY(a(2))) ENGINE=ARIA;
INSERT INTO t1 VALUES ('ss'),('ß');
{code}
{noformat}
ERROR 1062 (23000): Duplicate entry 'ß' for key 'a'
{noformat}

{code:sql}
# InnoDB is correct
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (a CHAR(10) COLLATE utf8mb3_unicode_nopad_ci, UNIQUE KEY(a(2))) ENGINE=InnoDB;
INSERT INTO t1 VALUES ('ss'),('ß');
{code}
{noformat}
Query OK, 2 rows affected (0.002 sec)
Records: 2 Duplicates: 0 Warnings: 0
{noformat}

{code:sql}
# MyISAM is wrong
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (a CHAR(10) COLLATE utf8mb3_unicode_nopad_ci, UNIQUE KEY(a)) ENGINE=MyISAM;
INSERT INTO t1 VALUES ('ss'),('ß');
{code}
{noformat}
ERROR 1062 (23000): Duplicate entry 'ß' for key 'a'
{noformat}

{code:sql}
# Aria is wrong
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (a CHAR(10) COLLATE utf8mb3_unicode_nopad_ci, UNIQUE KEY(a)) ENGINE=Aria;
INSERT INTO t1 VALUES ('ss'),('ß');
{code}
{noformat}
ERROR 1062 (23000): Duplicate entry 'ß' for key 'a'
{noformat}

{code:sql}
# InnoDB is correct
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (a CHAR(10) COLLATE utf8mb3_unicode_nopad_ci, UNIQUE KEY(a)) ENGINE=InnoDB;
INSERT INTO t1 VALUES ('ss'),('ß');
{code}
{noformat}
Query OK, 2 rows affected (0.002 sec)
Records: 2 Duplicates: 0 Warnings: 0
{noformat}

{code:sql}
# MEMORY is correct
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (a CHAR(10) COLLATE utf8mb3_unicode_nopad_ci, UNIQUE KEY(a)) ENGINE=MEMORY;
INSERT INTO t1 VALUES ('ss'),('ß');
{code}
{noformat}
Query OK, 2 rows affected (0.001 sec)
Records: 2 Duplicates: 0 Warnings: 0
{noformat}

MyISAM and Aria work incorrectly.
InnoDB and HEAP work correctly.

h2. Rationale

We compare two fixed length strings with 10 characters:
- Two characters 'ss' followed by 8 spaces
- One 'ß' character followed by 9 spaces

The comparison of 'ss' and 'ß' should work as follows for CHAR(10):
- 'ss' is equal to 'ß' in this collation
- 8 spaces are smaller than 9 spaces (because this is a NOPAD collation)
- the result is "smaller"

Note, left join works correctly:
{code:sql}
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (a CHAR(10) COLLATE utf8mb3_unicode_nopad_ci, UNIQUE KEY(a)) ENGINE=InnoDB;
INSERT INTO t1 VALUES ('ss'),('ß');
select * from t1, t1 as t2 where t1.a=t2.a;
{code}
{noformat}
+------+------+
| a | a |
+------+------+
| ss | ss |
| ß | ß |
+------+------+
{noformat}

h2. The same problem is repeatable with prefix keys:
{code:sql}
# MyISAM is wrong
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (a CHAR(10) COLLATE utf8mb3_unicode_nopad_ci, UNIQUE KEY(a(2))) ENGINE=MyISAM;
INSERT INTO t1 VALUES ('ss'),('ß');
{code}
{noformat}
ERROR 1062 (23000): Duplicate entry 'ß' for key 'a'
{noformat}

{code:sql}
# Aria is wrong
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (a CHAR(10) COLLATE utf8mb3_unicode_nopad_ci, UNIQUE KEY(a(2))) ENGINE=ARIA;
INSERT INTO t1 VALUES ('ss'),('ß');
{code}
{noformat}
ERROR 1062 (23000): Duplicate entry 'ß' for key 'a'
{noformat}

{code:sql}
# InnoDB is correct
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (a CHAR(10) COLLATE utf8mb3_unicode_nopad_ci, UNIQUE KEY(a(2))) ENGINE=InnoDB;
INSERT INTO t1 VALUES ('ss'),('ß');
{code}
{noformat}
Query OK, 2 rows affected (0.002 sec)
Records: 2 Duplicates: 0 Warnings: 0
{noformat}

Alexander Barkov made changes - 2023-04-07 09:34

Description

{code:sql}
# MyISAM is wrong
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (a CHAR(10) COLLATE utf8mb3_unicode_nopad_ci, UNIQUE KEY(a)) ENGINE=MyISAM;
INSERT INTO t1 VALUES ('ss'),('ß');
{code}
{noformat}
ERROR 1062 (23000): Duplicate entry 'ß' for key 'a'
{noformat}

{code:sql}
# Aria is wrong
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (a CHAR(10) COLLATE utf8mb3_unicode_nopad_ci, UNIQUE KEY(a)) ENGINE=Aria;
INSERT INTO t1 VALUES ('ss'),('ß');
{code}
{noformat}
ERROR 1062 (23000): Duplicate entry 'ß' for key 'a'
{noformat}

{code:sql}
# InnoDB is correct
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (a CHAR(10) COLLATE utf8mb3_unicode_nopad_ci, UNIQUE KEY(a)) ENGINE=InnoDB;
INSERT INTO t1 VALUES ('ss'),('ß');
{code}
{noformat}
Query OK, 2 rows affected (0.002 sec)
Records: 2 Duplicates: 0 Warnings: 0
{noformat}

{code:sql}
# MEMORY is correct
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (a CHAR(10) COLLATE utf8mb3_unicode_nopad_ci, UNIQUE KEY(a)) ENGINE=MEMORY;
INSERT INTO t1 VALUES ('ss'),('ß');
{code}
{noformat}
Query OK, 2 rows affected (0.001 sec)
Records: 2 Duplicates: 0 Warnings: 0
{noformat}

MyISAM and Aria work incorrectly.
InnoDB and HEAP work correctly.

h2. Rationale

We compare two fixed length strings with 10 characters:
- Two characters 'ss' followed by 8 spaces
- One 'ß' character followed by 9 spaces

The comparison of 'ss' and 'ß' should work as follows for CHAR(10):
- 'ss' is equal to 'ß' in this collation
- 8 spaces are smaller than 9 spaces (because this is a NOPAD collation)
- the result is "smaller"

Note, left join works correctly:
{code:sql}
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (a CHAR(10) COLLATE utf8mb3_unicode_nopad_ci, UNIQUE KEY(a)) ENGINE=InnoDB;
INSERT INTO t1 VALUES ('ss'),('ß');
select * from t1, t1 as t2 where t1.a=t2.a;
{code}
{noformat}
+------+------+
| a | a |
+------+------+
| ss | ss |
| ß | ß |
+------+------+
{noformat}

h2. The same problem is repeatable with prefix keys:
{code:sql}
# MyISAM is wrong
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (a CHAR(10) COLLATE utf8mb3_unicode_nopad_ci, UNIQUE KEY(a(2))) ENGINE=MyISAM;
INSERT INTO t1 VALUES ('ss'),('ß');
{code}
{noformat}
ERROR 1062 (23000): Duplicate entry 'ß' for key 'a'
{noformat}

{code:sql}
# Aria is wrong
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (a CHAR(10) COLLATE utf8mb3_unicode_nopad_ci, UNIQUE KEY(a(2))) ENGINE=ARIA;
INSERT INTO t1 VALUES ('ss'),('ß');
{code}
{noformat}
ERROR 1062 (23000): Duplicate entry 'ß' for key 'a'
{noformat}

{code:sql}
# InnoDB is correct
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (a CHAR(10) COLLATE utf8mb3_unicode_nopad_ci, UNIQUE KEY(a(2))) ENGINE=InnoDB;
INSERT INTO t1 VALUES ('ss'),('ß');
{code}
{noformat}
Query OK, 2 rows affected (0.002 sec)
Records: 2 Duplicates: 0 Warnings: 0
{noformat}

{code:sql}
# MyISAM is wrong
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (a CHAR(10) COLLATE utf8mb3_unicode_nopad_ci, UNIQUE KEY(a)) ENGINE=MyISAM;
INSERT INTO t1 VALUES ('ss'),('ß');
{code}
{noformat}
ERROR 1062 (23000): Duplicate entry 'ß' for key 'a'
{noformat}

{code:sql}
# Aria is wrong
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (a CHAR(10) COLLATE utf8mb3_unicode_nopad_ci, UNIQUE KEY(a)) ENGINE=Aria;
INSERT INTO t1 VALUES ('ss'),('ß');
{code}
{noformat}
ERROR 1062 (23000): Duplicate entry 'ß' for key 'a'
{noformat}

{code:sql}
# InnoDB is correct
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (a CHAR(10) COLLATE utf8mb3_unicode_nopad_ci, UNIQUE KEY(a)) ENGINE=InnoDB;
INSERT INTO t1 VALUES ('ss'),('ß');
{code}
{noformat}
Query OK, 2 rows affected (0.002 sec)
Records: 2 Duplicates: 0 Warnings: 0
{noformat}

{code:sql}
# MEMORY is correct
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (a CHAR(10) COLLATE utf8mb3_unicode_nopad_ci, UNIQUE KEY(a)) ENGINE=MEMORY;
INSERT INTO t1 VALUES ('ss'),('ß');
{code}
{noformat}
Query OK, 2 rows affected (0.001 sec)
Records: 2 Duplicates: 0 Warnings: 0
{noformat}

MyISAM and Aria work incorrectly.
InnoDB and HEAP work correctly.

h2. Rationale

We compare two fixed length strings with 10 characters:
- Two characters 'ss' followed by 8 spaces
- One 'ß' character followed by 9 spaces

The comparison of 'ss' and 'ß' should work as follows for CHAR(10):
- 'ss' is equal to 'ß' in this collation
- 8 spaces are smaller than 9 spaces (because this is a NOPAD collation)
- the result is "smaller"

Note, left join works correctly:
{code:sql}
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (a CHAR(10) COLLATE utf8mb3_unicode_nopad_ci, UNIQUE KEY(a)) ENGINE=InnoDB;
INSERT INTO t1 VALUES ('ss'),('ß');
select * from t1, t1 as t2 where t1.a=t2.a;
{code}
{noformat}
+------+------+
| a | a |
+------+------+
| ss | ss |
| ß | ß |
+------+------+
{noformat}

h2. The same problem is repeatable with prefix keys:
{code:sql}
# MyISAM is wrong
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (a CHAR(10) COLLATE utf8mb3_unicode_nopad_ci, UNIQUE KEY(a(2))) ENGINE=MyISAM;
INSERT INTO t1 VALUES ('ss'),('ß');
{code}
{noformat}
ERROR 1062 (23000): Duplicate entry 'ß' for key 'a'
{noformat}

{code:sql}
# Aria is wrong
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (a CHAR(10) COLLATE utf8mb3_unicode_nopad_ci, UNIQUE KEY(a(2))) ENGINE=ARIA;
INSERT INTO t1 VALUES ('ss'),('ß');
{code}
{noformat}
ERROR 1062 (23000): Duplicate entry 'ß' for key 'a'
{noformat}

{code:sql}
# InnoDB is correct
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (a CHAR(10) COLLATE utf8mb3_unicode_nopad_ci, UNIQUE KEY(a(2))) ENGINE=InnoDB;
INSERT INTO t1 VALUES ('ss'),('ß');
{code}
{noformat}
Query OK, 2 rows affected (0.002 sec)
Records: 2 Duplicates: 0 Warnings: 0
{noformat}

# MEMORY is correct
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (a CHAR(10) COLLATE utf8mb3_unicode_nopad_ci, UNIQUE KEY(a(2))) ENGINE=MEMORY;
INSERT INTO t1 VALUES ('ss'),('ß');
{code}
{noformat}
Query OK, 2 rows affected (0.000 sec)
Records: 2 Duplicates: 0 Warnings: 0

Alexander Barkov made changes - 2023-04-07 09:35

Description

{code:sql}
# MyISAM is wrong
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (a CHAR(10) COLLATE utf8mb3_unicode_nopad_ci, UNIQUE KEY(a)) ENGINE=MyISAM;
INSERT INTO t1 VALUES ('ss'),('ß');
{code}
{noformat}
ERROR 1062 (23000): Duplicate entry 'ß' for key 'a'
{noformat}

{code:sql}
# Aria is wrong
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (a CHAR(10) COLLATE utf8mb3_unicode_nopad_ci, UNIQUE KEY(a)) ENGINE=Aria;
INSERT INTO t1 VALUES ('ss'),('ß');
{code}
{noformat}
ERROR 1062 (23000): Duplicate entry 'ß' for key 'a'
{noformat}

{code:sql}
# InnoDB is correct
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (a CHAR(10) COLLATE utf8mb3_unicode_nopad_ci, UNIQUE KEY(a)) ENGINE=InnoDB;
INSERT INTO t1 VALUES ('ss'),('ß');
{code}
{noformat}
Query OK, 2 rows affected (0.002 sec)
Records: 2 Duplicates: 0 Warnings: 0
{noformat}

{code:sql}
# MEMORY is correct
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (a CHAR(10) COLLATE utf8mb3_unicode_nopad_ci, UNIQUE KEY(a)) ENGINE=MEMORY;
INSERT INTO t1 VALUES ('ss'),('ß');
{code}
{noformat}
Query OK, 2 rows affected (0.001 sec)
Records: 2 Duplicates: 0 Warnings: 0
{noformat}

MyISAM and Aria work incorrectly.
InnoDB and HEAP work correctly.

h2. Rationale

We compare two fixed length strings with 10 characters:
- Two characters 'ss' followed by 8 spaces
- One 'ß' character followed by 9 spaces

The comparison of 'ss' and 'ß' should work as follows for CHAR(10):
- 'ss' is equal to 'ß' in this collation
- 8 spaces are smaller than 9 spaces (because this is a NOPAD collation)
- the result is "smaller"

Note, left join works correctly:
{code:sql}
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (a CHAR(10) COLLATE utf8mb3_unicode_nopad_ci, UNIQUE KEY(a)) ENGINE=InnoDB;
INSERT INTO t1 VALUES ('ss'),('ß');
select * from t1, t1 as t2 where t1.a=t2.a;
{code}
{noformat}
+------+------+
| a | a |
+------+------+
| ss | ss |
| ß | ß |
+------+------+
{noformat}

h2. The same problem is repeatable with prefix keys:
{code:sql}
# MyISAM is wrong
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (a CHAR(10) COLLATE utf8mb3_unicode_nopad_ci, UNIQUE KEY(a(2))) ENGINE=MyISAM;
INSERT INTO t1 VALUES ('ss'),('ß');
{code}
{noformat}
ERROR 1062 (23000): Duplicate entry 'ß' for key 'a'
{noformat}

{code:sql}
# Aria is wrong
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (a CHAR(10) COLLATE utf8mb3_unicode_nopad_ci, UNIQUE KEY(a(2))) ENGINE=ARIA;
INSERT INTO t1 VALUES ('ss'),('ß');
{code}
{noformat}
ERROR 1062 (23000): Duplicate entry 'ß' for key 'a'
{noformat}

{code:sql}
# InnoDB is correct
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (a CHAR(10) COLLATE utf8mb3_unicode_nopad_ci, UNIQUE KEY(a(2))) ENGINE=InnoDB;
INSERT INTO t1 VALUES ('ss'),('ß');
{code}
{noformat}
Query OK, 2 rows affected (0.002 sec)
Records: 2 Duplicates: 0 Warnings: 0
{noformat}

# MEMORY is correct
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (a CHAR(10) COLLATE utf8mb3_unicode_nopad_ci, UNIQUE KEY(a(2))) ENGINE=MEMORY;
INSERT INTO t1 VALUES ('ss'),('ß');
{code}
{noformat}
Query OK, 2 rows affected (0.000 sec)
Records: 2 Duplicates: 0 Warnings: 0

{code:sql}
# MyISAM is wrong
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (a CHAR(10) COLLATE utf8mb3_unicode_nopad_ci, UNIQUE KEY(a)) ENGINE=MyISAM;
INSERT INTO t1 VALUES ('ss'),('ß');
{code}
{noformat}
ERROR 1062 (23000): Duplicate entry 'ß' for key 'a'
{noformat}

{code:sql}
# Aria is wrong
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (a CHAR(10) COLLATE utf8mb3_unicode_nopad_ci, UNIQUE KEY(a)) ENGINE=Aria;
INSERT INTO t1 VALUES ('ss'),('ß');
{code}
{noformat}
ERROR 1062 (23000): Duplicate entry 'ß' for key 'a'
{noformat}

{code:sql}
# InnoDB is correct
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (a CHAR(10) COLLATE utf8mb3_unicode_nopad_ci, UNIQUE KEY(a)) ENGINE=InnoDB;
INSERT INTO t1 VALUES ('ss'),('ß');
{code}
{noformat}
Query OK, 2 rows affected (0.002 sec)
Records: 2 Duplicates: 0 Warnings: 0
{noformat}

{code:sql}
# MEMORY is correct
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (a CHAR(10) COLLATE utf8mb3_unicode_nopad_ci, UNIQUE KEY(a)) ENGINE=MEMORY;
INSERT INTO t1 VALUES ('ss'),('ß');
{code}
{noformat}
Query OK, 2 rows affected (0.001 sec)
Records: 2 Duplicates: 0 Warnings: 0
{noformat}

MyISAM and Aria work incorrectly.
InnoDB and HEAP work correctly.

h2. Rationale

We compare two fixed length strings with 10 characters:
- Two characters 'ss' followed by 8 spaces
- One 'ß' character followed by 9 spaces

The comparison of 'ss' and 'ß' should work as follows for CHAR(10):
- 'ss' is equal to 'ß' in this collation
- 8 spaces are smaller than 9 spaces (because this is a NOPAD collation)
- the result is "smaller"

Note, left join works correctly:
{code:sql}
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (a CHAR(10) COLLATE utf8mb3_unicode_nopad_ci, UNIQUE KEY(a)) ENGINE=InnoDB;
INSERT INTO t1 VALUES ('ss'),('ß');
select * from t1, t1 as t2 where t1.a=t2.a;
{code}
{noformat}
+------+------+
| a | a |
+------+------+
| ss | ss |
| ß | ß |
+------+------+
{noformat}

h2. The same problem is repeatable with prefix keys:
{code:sql}
# MyISAM is wrong
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (a CHAR(10) COLLATE utf8mb3_unicode_nopad_ci, UNIQUE KEY(a(2))) ENGINE=MyISAM;
INSERT INTO t1 VALUES ('ss'),('ß');
{code}
{noformat}
ERROR 1062 (23000): Duplicate entry 'ß' for key 'a'
{noformat}

{code:sql}
# Aria is wrong
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (a CHAR(10) COLLATE utf8mb3_unicode_nopad_ci, UNIQUE KEY(a(2))) ENGINE=ARIA;
INSERT INTO t1 VALUES ('ss'),('ß');
{code}
{noformat}
ERROR 1062 (23000): Duplicate entry 'ß' for key 'a'
{noformat}

{code:sql}
# InnoDB is correct
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (a CHAR(10) COLLATE utf8mb3_unicode_nopad_ci, UNIQUE KEY(a(2))) ENGINE=InnoDB;
INSERT INTO t1 VALUES ('ss'),('ß');
{code}
{noformat}
Query OK, 2 rows affected (0.002 sec)
Records: 2 Duplicates: 0 Warnings: 0
{noformat}

{code:sql}
# MEMORY is correct
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (a CHAR(10) COLLATE utf8mb3_unicode_nopad_ci, UNIQUE KEY(a(2))) ENGINE=MEMORY;
INSERT INTO t1 VALUES ('ss'),('ß');
{code}
{noformat}
Query OK, 2 rows affected (0.000 sec)
Records: 2 Duplicates: 0 Warnings: 0

Alexander Barkov made changes - 2023-04-07 09:35

Description

{code:sql}
# MyISAM is wrong
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (a CHAR(10) COLLATE utf8mb3_unicode_nopad_ci, UNIQUE KEY(a)) ENGINE=MyISAM;
INSERT INTO t1 VALUES ('ss'),('ß');
{code}
{noformat}
ERROR 1062 (23000): Duplicate entry 'ß' for key 'a'
{noformat}

{code:sql}
# Aria is wrong
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (a CHAR(10) COLLATE utf8mb3_unicode_nopad_ci, UNIQUE KEY(a)) ENGINE=Aria;
INSERT INTO t1 VALUES ('ss'),('ß');
{code}
{noformat}
ERROR 1062 (23000): Duplicate entry 'ß' for key 'a'
{noformat}

{code:sql}
# InnoDB is correct
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (a CHAR(10) COLLATE utf8mb3_unicode_nopad_ci, UNIQUE KEY(a)) ENGINE=InnoDB;
INSERT INTO t1 VALUES ('ss'),('ß');
{code}
{noformat}
Query OK, 2 rows affected (0.002 sec)
Records: 2 Duplicates: 0 Warnings: 0
{noformat}

{code:sql}
# MEMORY is correct
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (a CHAR(10) COLLATE utf8mb3_unicode_nopad_ci, UNIQUE KEY(a)) ENGINE=MEMORY;
INSERT INTO t1 VALUES ('ss'),('ß');
{code}
{noformat}
Query OK, 2 rows affected (0.001 sec)
Records: 2 Duplicates: 0 Warnings: 0
{noformat}

MyISAM and Aria work incorrectly.
InnoDB and HEAP work correctly.

h2. Rationale

We compare two fixed length strings with 10 characters:
- Two characters 'ss' followed by 8 spaces
- One 'ß' character followed by 9 spaces

The comparison of 'ss' and 'ß' should work as follows for CHAR(10):
- 'ss' is equal to 'ß' in this collation
- 8 spaces are smaller than 9 spaces (because this is a NOPAD collation)
- the result is "smaller"

Note, left join works correctly:
{code:sql}
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (a CHAR(10) COLLATE utf8mb3_unicode_nopad_ci, UNIQUE KEY(a)) ENGINE=InnoDB;
INSERT INTO t1 VALUES ('ss'),('ß');
select * from t1, t1 as t2 where t1.a=t2.a;
{code}
{noformat}
+------+------+
| a | a |
+------+------+
| ss | ss |
| ß | ß |
+------+------+
{noformat}

h2. The same problem is repeatable with prefix keys:
{code:sql}
# MyISAM is wrong
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (a CHAR(10) COLLATE utf8mb3_unicode_nopad_ci, UNIQUE KEY(a(2))) ENGINE=MyISAM;
INSERT INTO t1 VALUES ('ss'),('ß');
{code}
{noformat}
ERROR 1062 (23000): Duplicate entry 'ß' for key 'a'
{noformat}

{code:sql}
# Aria is wrong
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (a CHAR(10) COLLATE utf8mb3_unicode_nopad_ci, UNIQUE KEY(a(2))) ENGINE=ARIA;
INSERT INTO t1 VALUES ('ss'),('ß');
{code}
{noformat}
ERROR 1062 (23000): Duplicate entry 'ß' for key 'a'
{noformat}

{code:sql}
# InnoDB is correct
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (a CHAR(10) COLLATE utf8mb3_unicode_nopad_ci, UNIQUE KEY(a(2))) ENGINE=InnoDB;
INSERT INTO t1 VALUES ('ss'),('ß');
{code}
{noformat}
Query OK, 2 rows affected (0.002 sec)
Records: 2 Duplicates: 0 Warnings: 0
{noformat}

{code:sql}
# MEMORY is correct
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (a CHAR(10) COLLATE utf8mb3_unicode_nopad_ci, UNIQUE KEY(a(2))) ENGINE=MEMORY;
INSERT INTO t1 VALUES ('ss'),('ß');
{code}
{noformat}
Query OK, 2 rows affected (0.000 sec)
Records: 2 Duplicates: 0 Warnings: 0

{code:sql}
# MyISAM is wrong
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (a CHAR(10) COLLATE utf8mb3_unicode_nopad_ci, UNIQUE KEY(a)) ENGINE=MyISAM;
INSERT INTO t1 VALUES ('ss'),('ß');
{code}
{noformat}
ERROR 1062 (23000): Duplicate entry 'ß' for key 'a'
{noformat}

{code:sql}
# Aria is wrong
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (a CHAR(10) COLLATE utf8mb3_unicode_nopad_ci, UNIQUE KEY(a)) ENGINE=Aria;
INSERT INTO t1 VALUES ('ss'),('ß');
{code}
{noformat}
ERROR 1062 (23000): Duplicate entry 'ß' for key 'a'
{noformat}

{code:sql}
# InnoDB is correct
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (a CHAR(10) COLLATE utf8mb3_unicode_nopad_ci, UNIQUE KEY(a)) ENGINE=InnoDB;
INSERT INTO t1 VALUES ('ss'),('ß');
{code}
{noformat}
Query OK, 2 rows affected (0.002 sec)
Records: 2 Duplicates: 0 Warnings: 0
{noformat}

{code:sql}
# MEMORY is correct
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (a CHAR(10) COLLATE utf8mb3_unicode_nopad_ci, UNIQUE KEY(a)) ENGINE=MEMORY;
INSERT INTO t1 VALUES ('ss'),('ß');
{code}
{noformat}
Query OK, 2 rows affected (0.001 sec)
Records: 2 Duplicates: 0 Warnings: 0
{noformat}

MyISAM and Aria work incorrectly.
InnoDB and HEAP work correctly.

h2. Rationale

We compare two fixed length strings with 10 characters:
- Two characters 'ss' followed by 8 spaces
- One 'ß' character followed by 9 spaces

The comparison of 'ss' and 'ß' should work as follows for CHAR(10):
- 'ss' is equal to 'ß' in this collation
- 8 spaces are smaller than 9 spaces (because this is a NOPAD collation)
- the result is "smaller"

Note, left join works correctly:
{code:sql}
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (a CHAR(10) COLLATE utf8mb3_unicode_nopad_ci, UNIQUE KEY(a)) ENGINE=InnoDB;
INSERT INTO t1 VALUES ('ss'),('ß');
select * from t1, t1 as t2 where t1.a=t2.a;
{code}
{noformat}
+------+------+
| a | a |
+------+------+
| ss | ss |
| ß | ß |
+------+------+
{noformat}

h2. The same problem is repeatable with prefix keys:
{code:sql}
# MyISAM is wrong
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (a CHAR(10) COLLATE utf8mb3_unicode_nopad_ci, UNIQUE KEY(a(2))) ENGINE=MyISAM;
INSERT INTO t1 VALUES ('ss'),('ß');
{code}
{noformat}
ERROR 1062 (23000): Duplicate entry 'ß' for key 'a'
{noformat}

{code:sql}
# Aria is wrong
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (a CHAR(10) COLLATE utf8mb3_unicode_nopad_ci, UNIQUE KEY(a(2))) ENGINE=ARIA;
INSERT INTO t1 VALUES ('ss'),('ß');
{code}
{noformat}
ERROR 1062 (23000): Duplicate entry 'ß' for key 'a'
{noformat}

{code:sql}
# InnoDB is correct
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (a CHAR(10) COLLATE utf8mb3_unicode_nopad_ci, UNIQUE KEY(a(2))) ENGINE=InnoDB;
INSERT INTO t1 VALUES ('ss'),('ß');
{code}
{noformat}
Query OK, 2 rows affected (0.002 sec)
Records: 2 Duplicates: 0 Warnings: 0
{noformat}

{code:sql}
# MEMORY is correct
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (a CHAR(10) COLLATE utf8mb3_unicode_nopad_ci, UNIQUE KEY(a(2))) ENGINE=MEMORY;
INSERT INTO t1 VALUES ('ss'),('ß');
{code}
{noformat}
Query OK, 2 rows affected (0.000 sec)
Records: 2 Duplicates: 0 Warnings: 0
{noformat}

Alexander Barkov made changes - 2023-04-07 10:55

Status

Open [ 1 ]

In Progress [ 3 ]

Alexander Barkov added a comment - 2023-04-07 10:56

Hello serg,

Can you please review a patch:
https://github.com/MariaDB/server/commit/56fa1da9c676ebc480b5cb43627752150010cf4c

Thanks.

Alexander Barkov added a comment - 2023-04-07 10:56 Hello serg , Can you please review a patch: https://github.com/MariaDB/server/commit/56fa1da9c676ebc480b5cb43627752150010cf4c Thanks.

Alexander Barkov made changes - 2023-04-07 10:56

Assignee	Alexander Barkov [ bar ]	Sergei Golubchik [ serg ]
Status	In Progress [ 3 ]	In Review [ 10002 ]

Sergei Golubchik made changes - 2023-10-17 20:29

Assignee	Sergei Golubchik [ serg ]	Alexander Barkov [ bar ]
Status	In Review [ 10002 ]	Stalled [ 10000 ]

Sergei Golubchik made changes - 2023-10-17 20:29

Affects Version/s		10.5 [ 23123 ]
Affects Version/s		10.6 [ 24028 ]
Affects Version/s		10.7 [ 24805 ]
Affects Version/s		10.8 [ 26121 ]
Affects Version/s		10.9 [ 26905 ]
Affects Version/s		10.10 [ 27530 ]
Affects Version/s		10.11 [ 27614 ]

Rick James added a comment - 2023-10-17 23:49

I hope this is not a repeat of the fiasco many years ago utf8_general_mysql500_ci .

Rick James added a comment - 2023-10-17 23:49 I hope this is not a repeat of the fiasco many years ago utf8_general_mysql500_ci .

Alexander Barkov added a comment - 2023-10-18 00:11

rjasdfiii, which fiasco do you mean?

Alexander Barkov added a comment - 2023-10-18 00:11 rjasdfiii , which fiasco do you mean?

Rick James added a comment - 2023-10-18 15:59

@AlexanderBarkov - Many users experienced "duplicate key" in their PRIMARY KEY due to this incompatible change.

----- 2012-04-10 5.6.5 Milestone 8 & 2012-03-21 5.1.62 & 2012-02-17 5.5.21 – Functionality Added or Changed – -----

New utf8_general_mysql500_ci and ucs2_general_mysql500_ci collations have been added that preserve the behavior of utf8_general_ci and ucs2_general_ci from versions of MySQL previous to 5.1.24. [CHECK TABLE](https://dev.mysql.com/doc/refman/5.7/en/check-table.html) produces this error:

----- 2008-11-14 5.1.30

The CHECK TABLE ... FOR UPGRADE statement did not check for incompatible collation changes made in MySQL 5.1.24 (Bug #27877). This also affects mysqlcheck and mysql_upgrade, which cause that statement to be executed. See Checking Whether Tables or Indexes Must Be Rebuilt.
Prior to this fix, a binary upgrade (performed without dumping tables with mysqldump before the upgrade and reloading the dump file after the upgrade) would corrupt tables that have indexes that use the utf8_general_ci or ucs2_general_ci collation for columns that contain 'ß' LATIN SMALL LETTER SHARP S (German). After the fix, CHECK TABLE ... FOR UPGRADE properly detects the problem and warns about tables that need repair.

However, the fix is not backward compatible and can result in a downgrading problem under these circumstances:

Perform a binary upgrade to a version of MySQL that includes the fix.

Run CHECK TABLE ... FOR UPGRADE (or mysqlcheck or mysql_upgrade) to upgrade tables.

Perform a binary downgrade to a version of MySQL that does not include the fix.

The solution is to dump tables with mysqldump before the downgrade and reload the dump file after the downgrade. Alternatively, drop and recreate affected indexes. (Bug #40053)

Rick James added a comment - 2023-10-18 15:59 @AlexanderBarkov - Many users experienced "duplicate key" in their PRIMARY KEY due to this incompatible change. ----- 2012-04-10 5.6.5 Milestone 8 & 2012-03-21 5.1.62 & 2012-02-17 5.5.21 – Functionality Added or Changed – ----- New utf8_general_mysql500_ci and ucs2_general_mysql500_ci collations have been added that preserve the behavior of utf8_general_ci and ucs2_general_ci from versions of MySQL previous to 5.1.24. [CHECK TABLE] ( https://dev.mysql.com/doc/refman/5.7/en/check-table.html ) produces this error: ----- 2008-11-14 5.1.30 The CHECK TABLE ... FOR UPGRADE statement did not check for incompatible collation changes made in MySQL 5.1.24 (Bug #27877). This also affects mysqlcheck and mysql_upgrade, which cause that statement to be executed. See Checking Whether Tables or Indexes Must Be Rebuilt. Prior to this fix, a binary upgrade (performed without dumping tables with mysqldump before the upgrade and reloading the dump file after the upgrade) would corrupt tables that have indexes that use the utf8_general_ci or ucs2_general_ci collation for columns that contain 'ß' LATIN SMALL LETTER SHARP S (German). After the fix, CHECK TABLE ... FOR UPGRADE properly detects the problem and warns about tables that need repair. However, the fix is not backward compatible and can result in a downgrading problem under these circumstances: Perform a binary upgrade to a version of MySQL that includes the fix. Run CHECK TABLE ... FOR UPGRADE (or mysqlcheck or mysql_upgrade) to upgrade tables. Perform a binary downgrade to a version of MySQL that does not include the fix. The solution is to dump tables with mysqldump before the downgrade and reload the dump file after the downgrade. Alternatively, drop and recreate affected indexes. (Bug #40053)

Alexander Barkov made changes - 2023-10-19 12:50

Assignee	Alexander Barkov [ bar ]	Sergei Golubchik [ serg ]
Status	Stalled [ 10000 ]	In Review [ 10002 ]

Sergei Golubchik made changes - 2023-10-21 13:57

Assignee	Sergei Golubchik [ serg ]	Alexander Barkov [ bar ]
Status	In Review [ 10002 ]	Stalled [ 10000 ]

Alexander Barkov made changes - 2023-10-23 02:49

Assignee	Alexander Barkov [ bar ]	Sergei Golubchik [ serg ]
Status	Stalled [ 10000 ]	In Review [ 10002 ]

Sergei Golubchik added a comment - 2023-10-23 13:04

99ffd7e3934 is ok to push.

I'd still suggest to split the commit in two — a cleanup commit that introduces all functions in my_compare.h and replaces them as needed, but does not change the behavior (doesn't use ha_compare_char_fixed for HA_KEYTYPE_TEXT). And the second commit that fixes the bug.

Sergei Golubchik added a comment - 2023-10-23 13:04 99ffd7e3934 is ok to push. I'd still suggest to split the commit in two — a cleanup commit that introduces all functions in my_compare.h and replaces them as needed, but does not change the behavior (doesn't use ha_compare_char_fixed for HA_KEYTYPE_TEXT). And the second commit that fixes the bug.

Sergei Golubchik made changes - 2023-10-23 13:04

Assignee	Sergei Golubchik [ serg ]	Alexander Barkov [ bar ]
Status	In Review [ 10002 ]	Stalled [ 10000 ]

Alexander Barkov made changes - 2023-10-24 00:40

Fix Version/s		10.4.32 [ 29300 ]
Fix Version/s		10.5.23 [ 29012 ]
Fix Version/s		10.6.16 [ 29014 ]
Fix Version/s		10.10.7 [ 29018 ]
Fix Version/s		10.11.6 [ 29020 ]
Fix Version/s		11.0.4 [ 29021 ]
Fix Version/s		11.1.3 [ 29023 ]
Fix Version/s		11.2.2 [ 29035 ]
Fix Version/s		11.3.1 [ 29416 ]
Fix Version/s		10.9.8 [ 29015 ]
Fix Version/s		10.8.8 [ 28518 ]
Fix Version/s	10.4 [ 22408 ]
Resolution		Fixed [ 1 ]
Status	Stalled [ 10000 ]	Closed [ 6 ]

MariaDB Server

Prefix keys for CHAR work differently for MyISAM vs InnoDB

Details

Description

Rationale

The same problem is repeatable with prefix keys:

Attachments

Issue Links

Activity

People

Dates

Git Integration