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

Bad result set with ignorable characters when using a prefix key

    XMLWordPrintable

Details

    Description

      This bug is known for a long time since adding UCA collations.

      This script with no index:

      DROP TABLE IF EXISTS t1;
      CREATE TABLE t1 (a VARCHAR(10) CHARACTER SET utf8 COLLATE utf8_unicode_ci);
      INSERT INTO t1 VALUES ('a'),('b'),('c'),('d'),('e'),('f'),('g'),('h');
      SELECT * FROM t1 WHERE a<'\0b';

      and this script with KEY(a):

      DROP TABLE IF EXISTS t1;
      CREATE TABLE t1 (a VARCHAR(10) CHARACTER SET utf8 COLLATE utf8_unicode_ci, KEY(a));
      INSERT INTO t1 VALUES ('a'),('b'),('c'),('d'),('e'),('f'),('g'),('h');
      SELECT * FROM t1 WHERE a<'\0b';

      correctly return one row:

      +------+
      | a    |
      +------+
      | a    |
      +------+
      1 row in set (0.00 sec)

      This script with a prefix index KEY(a(1))

      DROP TABLE IF EXISTS t1;
      CREATE TABLE t1 (a VARCHAR(10) CHARACTER SET utf8 COLLATE utf8_unicode_ci, KEY(a(1)));
      INSERT INTO t1 VALUES ('a'),('b'),('c'),('d'),('e'),('f'),('g'),('h');
      SELECT * FROM t1 WHERE a<'\0b';

      erroneously returns empty set.

      Attachments

        Issue Links

          Activity

            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.