Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-30048

Prefix keys for CHAR work differently for MyISAM vs InnoDB

Details

    Description

      # 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'),('ß');


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


      # 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'),('ß');


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


      # 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'),('ß');


      Query OK, 2 rows affected (0.002 sec)
      		 
      Records: 2  Duplicates: 0  Warnings: 0


      # 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'),('ß');


      Query OK, 2 rows affected (0.001 sec)
      		 
      Records: 2  Duplicates: 0  Warnings: 0

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

      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:

      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;


      +------+------+
      		 
      | a    | a    |
      		 
      +------+------+
      		 
      | ss   | ss   |
      		 
      | ß    | ß    |
      		 
      +------+------+

      The same problem is repeatable with prefix keys:

      # 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'),('ß');


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


      # 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'),('ß');


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


      # 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'),('ß');


      Query OK, 2 rows affected (0.002 sec)
      		 
      Records: 2  Duplicates: 0  Warnings: 0


      # 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'),('ß');


      Query OK, 2 rows affected (0.000 sec)
      		 
      Records: 2  Duplicates: 0  Warnings: 0

      Attachments

        Issue Links

          relates to

          Bug - A problem which impairs or prevents the functions of the product. MDEV-25904 New collation functions to compare InnoDB style trimmed NO PAD strings

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-27670 Assertion `(cs->state & 0x20000) == 0' failed in my_strnncollsp_nchars_generic_8bit

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-27768 MDEV-25440: Assertion `(cs->state & 0x20000) == 0' failed in my_strnncollsp_nchars_generic_8bit

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-30072 Wrong ORDER BY for a partitioned prefix key + NOPAD

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-30034 UNIQUE USING HASH accepts duplicate entries for tricky collations

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-30050 Inconsistent results of DISTINCT with NOPAD

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Activity

            Ascending order - Click to sort in descending order
            bar Alexander Barkov created issue -
            bar Alexander Barkov made changes -
            Field Original Value New Value
            bar Alexander Barkov made changes -
            bar Alexander Barkov made changes -
            bar Alexander Barkov made changes -
            Priority Major [ 3 ] Critical [ 2 ]
            bar Alexander Barkov made changes -
            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}
            bar Alexander Barkov made changes -
            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}
            bar Alexander Barkov made changes -
            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}
            bar Alexander Barkov made changes -
            bar Alexander Barkov made changes -
            bar Alexander Barkov made changes -
            bar Alexander Barkov made changes -
            bar Alexander Barkov made changes -
            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}
            bar Alexander Barkov made changes -
            bar Alexander Barkov made changes -
            bar Alexander Barkov made changes -
            bar Alexander Barkov made changes -
            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}
            bar Alexander Barkov made changes -
            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}
            bar Alexander Barkov made changes -
            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}
            bar Alexander Barkov made changes -
            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}
            bar Alexander Barkov made changes -
            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}
            bar Alexander Barkov made changes -
            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
            bar Alexander Barkov made changes -
            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
            bar Alexander Barkov made changes -
            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}
            bar Alexander Barkov made changes -
            Status Open [ 1 ] In Progress [ 3 ]
            bar Alexander Barkov made changes -
            Assignee Alexander Barkov [ bar ] Sergei Golubchik [ serg ]
            Status In Progress [ 3 ] In Review [ 10002 ]
            serg Sergei Golubchik made changes -
            Assignee Sergei Golubchik [ serg ] Alexander Barkov [ bar ]
            Status In Review [ 10002 ] Stalled [ 10000 ]
            serg Sergei Golubchik made changes -
            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 ]
            bar Alexander Barkov made changes -
            Assignee Alexander Barkov [ bar ] Sergei Golubchik [ serg ]
            Status Stalled [ 10000 ] In Review [ 10002 ]
            serg Sergei Golubchik made changes -
            Assignee Sergei Golubchik [ serg ] Alexander Barkov [ bar ]
            Status In Review [ 10002 ] Stalled [ 10000 ]
            bar Alexander Barkov made changes -
            Assignee Alexander Barkov [ bar ] Sergei Golubchik [ serg ]
            Status Stalled [ 10000 ] In Review [ 10002 ]
            serg Sergei Golubchik made changes -
            Assignee Sergei Golubchik [ serg ] Alexander Barkov [ bar ]
            Status In Review [ 10002 ] Stalled [ 10000 ]
            bar Alexander Barkov made changes -
            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 ]

            People

              bar Alexander Barkov
              bar Alexander Barkov
              Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

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