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

Prefix keys for CHAR work differently for MyISAM vs InnoDB

    XMLWordPrintable

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

            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.