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

          Activity

            People

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

              Dates

              Created:
              Updated: