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

UNIQUE USING HASH accepts duplicate entries for tricky collations

Details

    Description

      If I use a traditional unique constraint, the table correctly rejects the second record:

      CREATE OR REPLACE TABLE t1 (a VARCHAR(2000) COLLATE utf8_unicode_nopad_ci, UNIQUE(a(3)));
      		 
      INSERT INTO t1 VALUES ('ss ');
      		 
      INSERT INTO t1 VALUES ('ß ');


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

      Now if I do the same thing with UNIQUE USING HASH, it accepts the duplicate record:

      CREATE OR REPLACE TABLE t1 (a VARCHAR(2000) COLLATE utf8_unicode_nopad_ci, UNIQUE(a(3)) USING HASH);
      		 
      INSERT INTO t1 VALUES ('ss ');
      		 
      INSERT INTO t1 VALUES ('ß ');

      This is wrong. The second record should be rejected.

      Attachments

        Issue Links

          is caused by

          Task - A task that needs to be done. MDEV-371 Unique indexes for blobs

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

          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
          relates to

          Bug - A problem which impairs or prevents the functions of the product. MDEV-30048 Prefix keys for CHAR work differently for MyISAM vs InnoDB

          • 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

          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-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

          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 -
            Description If I use a traditional unique constraint, the table correctly rejects the second record:
            {code:sql}
            CREATE OR REPLACE TABLE t1 (a VARCHAR(2000) COLLATE utf8_unicode_nopad_ci, UNIQUE(a(3)));
            INSERT INTO t1 VALUES ('ss ');
            INSERT INTO t1 VALUES ('ß ');
            {code}
            {noformat}
            ERROR 1062 (23000): Duplicate entry 'ß ' for key 'a'
            {noformat}


            Now if I do the same this, it accepts the duplicate record:
            {code:sql}
            CREATE OR REPLACE TABLE t1 (a VARCHAR(2000) COLLATE utf8_unicode_nopad_ci, UNIQUE(a(3)) USING HASH);
            INSERT INTO t1 VALUES ('ss ');
            INSERT INTO t1 VALUES ('ß ');
            {code}
            This is wrong. The second record should be rejected.
            		If I use a traditional unique constraint, the table correctly rejects the second record:
            {code:sql}
            CREATE OR REPLACE TABLE t1 (a VARCHAR(2000) COLLATE utf8_unicode_nopad_ci, UNIQUE(a(3)));
            INSERT INTO t1 VALUES ('ss ');
            INSERT INTO t1 VALUES ('ß ');
            {code}
            {noformat}
            ERROR 1062 (23000): Duplicate entry 'ß ' for key 'a'
            {noformat}


            Now if I do the same thing with UNIQUE USING HASH, it accepts the duplicate record:
            {code:sql}
            CREATE OR REPLACE TABLE t1 (a VARCHAR(2000) COLLATE utf8_unicode_nopad_ci, UNIQUE(a(3)) USING HASH);
            INSERT INTO t1 VALUES ('ss ');
            INSERT INTO t1 VALUES ('ß ');
            {code}
            This is wrong. The second record should be rejected.
            bar Alexander Barkov made changes -
            bar Alexander Barkov made changes -
            serg Sergei Golubchik made changes -
            Fix Version/s 10.5 [ 23123 ]
            Fix Version/s 10.6 [ 24028 ]
            Fix Version/s 10.7 [ 24805 ]
            Fix Version/s 10.8 [ 26121 ]
            Fix Version/s 10.9 [ 26905 ]
            Fix Version/s 10.10 [ 27530 ]
            Fix Version/s 10.11 [ 27614 ]
            julien.fritsch Julien Fritsch made changes -
            Fix Version/s 10.7 [ 24805 ]
            bar Alexander Barkov made changes -
            bar Alexander Barkov made changes -
            bar Alexander Barkov made changes -
            Fix Version/s 10.4.29 [ 28510 ]
            Fix Version/s 10.5.20 [ 28512 ]
            Fix Version/s 10.6.13 [ 28514 ]
            Fix Version/s 10.8.8 [ 28518 ]
            Fix Version/s 10.9.6 [ 28520 ]
            Fix Version/s 10.10.4 [ 28522 ]
            Fix Version/s 10.11.3 [ 28524 ]
            Fix Version/s 10.7.8 [ 28515 ]
            Fix Version/s 10.4 [ 22408 ]
            Fix Version/s 10.5 [ 23123 ]
            Fix Version/s 10.6 [ 24028 ]
            Fix Version/s 10.8 [ 26121 ]
            Fix Version/s 10.9 [ 26905 ]
            Fix Version/s 10.10 [ 27530 ]
            Fix Version/s 10.11 [ 27614 ]
            Resolution Fixed [ 1 ]
            Status Open [ 1 ] Closed [ 6 ]
            marko Marko Mäkelä made changes -
            Fix Version/s 10.7.8 [ 28515 ]
            marko Marko Mäkelä made changes -

            People

              bar Alexander Barkov
              bar Alexander Barkov
              Votes:
              0 Vote for this issue
              Watchers:
              2 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.