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

MyISAM, Aria, MEMORY: CHAR+nopad does not work well

Details

    Description

      This bug is similar for MDEV-26743, but for MyISAM.

      The same problem is repeatable with:

      • ENGINE=Aria
      • ENGINE=MEMORY in combination with BTREE index algorithm.

      Basic latin letter vs equal accented letter

      SET NAMES utf8mb3;
      DROP TABLE IF EXISTS t1;
      CREATE TABLE t1 (a CHAR(2), PRIMARY KEY(a)) COLLATE utf8_unicode_nopad_ci ENGINE=MyISAM;
      INSERT INTO t1 VALUES ('a'),('รค');
      

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

      Looks wrong. The expected result is to throw a duplicate key error. See MDEV-26743 for details.

      Two letters vs equal (but space padded) expansion

      SET NAMES utf8mb3;
      DROP TABLE IF EXISTS t1;
      CREATE TABLE t1 (a CHAR(2), PRIMARY KEY(a)) COLLATE utf8_unicode_nopad_ci ENGINE=MyISAM;
      INSERT INTO t1 VALUES ('ss'),('รŸ');
      

      ERROR 1062 (23000): Duplicate entry 'รŸ' for key 'PRIMARY'
      

      Looks wrong. The expected result is to accept both values. See MDEV-26743 for details.

      Basic latin letter (but followed by an ignorable character) vs equal accented letter

      SET NAMES utf8mb3;
      DROP TABLE IF EXISTS t1;
      CREATE TABLE t1 (a CHAR(3), PRIMARY KEY(a)) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_nopad_ci ENGINE=MyISAM;
      INSERT INTO t1 VALUES (CONCAT('a',_utf8mb3 0x01)),('รค');
      

      ERROR 1062 (23000): Duplicate entry 'รค' for key 'PRIMARY'
      

      Looks wrong. The expected result is to accept both values. See MDEV-26743 for details.

      SET NAMES utf8mb3;
      DROP TABLE IF EXISTS t1;
      CREATE TABLE t1 (a CHAR(2), PRIMARY KEY(a)) COLLATE utf8_unicode_nopad_ci ENGINE=MyISAM;
      INSERT INTO t1 VALUES (CONCAT('a',_utf8mb3 0x01)),('รค');
      

      ERROR 1062 (23000): Duplicate entry 'รค' for key 'PRIMARY'
      

      Looks wrong. The expected result is to accept both values. See MDEV-26743 for details.

      Attachments

        Issue Links

          Activity

            Scripts to reproduce the problem with ENGINE=MEMORY with BTREE indexes:

            Basic latin letter vs equal accented letter

            SET NAMES utf8mb3;
            DROP TABLE IF EXISTS t1;
            CREATE TABLE t1 (a CHAR(2), PRIMARY KEY(a) USING BTREE) COLLATE utf8_unicode_nopad_ci ENGINE=MEMORY;
            INSERT INTO t1 VALUES ('a'),('รค');
            

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

            Looks wrong. The expected result is to throw a duplicate key error. See MDEV-26743 for details.

            Two letters vs equal (but space padded) expansion

            SET NAMES utf8mb3;
            DROP TABLE IF EXISTS t1;
            CREATE TABLE t1 (a CHAR(2), PRIMARY KEY(a) USING BTREE) COLLATE utf8_unicode_nopad_ci ENGINE=MEMORY;
            INSERT INTO t1 VALUES ('ss'),('รŸ');
            

            ERROR 1062 (23000): Duplicate entry 'รŸ' for key 'PRIMARY'
            

            Looks wrong. The expected result is to accept both values. See MDEV-26743 for details.

            Basic latin letter (but followed by an ignorable character) vs equal accented letter

            SET NAMES utf8mb3;
            DROP TABLE IF EXISTS t1;
            CREATE TABLE t1 (a CHAR(3), PRIMARY KEY(a) USING BTREE) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_nopad_ci ENGINE=MEMORY;
            INSERT INTO t1 VALUES (CONCAT('a',_utf8mb3 0x01)),('รค');
            

            ERROR 1062 (23000): Duplicate entry 'รค' for key 'PRIMARY'
            

            Looks wrong. The expected result is to accept both values. See MDEV-26743 for details.

            SET NAMES utf8mb3;
            DROP TABLE IF EXISTS t1;
            CREATE TABLE t1 (a CHAR(2), PRIMARY KEY(a) USING BTREE) COLLATE utf8_unicode_nopad_ci ENGINE=MEMORY;
            INSERT INTO t1 VALUES (CONCAT('a',_utf8mb3 0x01)),('รค');
            

            ERROR 1062 (23000): Duplicate entry 'รค' for key 'PRIMARY'
            

            Looks wrong. The expected result is to accept both values. See MDEV-26743 for details.

            bar Alexander Barkov added a comment - Scripts to reproduce the problem with ENGINE=MEMORY with BTREE indexes: Basic latin letter vs equal accented letter SET NAMES utf8mb3; DROP TABLE IF EXISTS t1; CREATE TABLE t1 (a CHAR (2), PRIMARY KEY (a) USING BTREE) COLLATE utf8_unicode_nopad_ci ENGINE=MEMORY; INSERT INTO t1 VALUES ( 'a' ),( 'รค' ); Query OK, 2 rows affected (0.001 sec) Records: 2 Duplicates: 0 Warnings: 0 Looks wrong. The expected result is to throw a duplicate key error. See MDEV-26743 for details. Two letters vs equal (but space padded) expansion SET NAMES utf8mb3; DROP TABLE IF EXISTS t1; CREATE TABLE t1 (a CHAR (2), PRIMARY KEY (a) USING BTREE) COLLATE utf8_unicode_nopad_ci ENGINE=MEMORY; INSERT INTO t1 VALUES ( 'ss' ),( 'รŸ' ); ERROR 1062 (23000): Duplicate entry 'รŸ' for key 'PRIMARY' Looks wrong. The expected result is to accept both values. See MDEV-26743 for details. Basic latin letter (but followed by an ignorable character) vs equal accented letter SET NAMES utf8mb3; DROP TABLE IF EXISTS t1; CREATE TABLE t1 (a CHAR (3), PRIMARY KEY (a) USING BTREE) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_nopad_ci ENGINE=MEMORY; INSERT INTO t1 VALUES (CONCAT( 'a' ,_utf8mb3 0x01)),( 'รค' ); ERROR 1062 (23000): Duplicate entry 'รค' for key 'PRIMARY' Looks wrong. The expected result is to accept both values. See MDEV-26743 for details. SET NAMES utf8mb3; DROP TABLE IF EXISTS t1; CREATE TABLE t1 (a CHAR (2), PRIMARY KEY (a) USING BTREE) COLLATE utf8_unicode_nopad_ci ENGINE=MEMORY; INSERT INTO t1 VALUES (CONCAT( 'a' ,_utf8mb3 0x01)),( 'รค' ); ERROR 1062 (23000): Duplicate entry 'รค' for key 'PRIMARY' Looks wrong. The expected result is to accept both values. See MDEV-26743 for details.

            People

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

              Dates

                Created:
                Updated:

                Git Integration

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