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

Bad result set with ignorable characters when using a prefix key

        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.

            Gliffy Diagrams

              Attachments

                Activity

                There are no comments yet on this issue.

                  People

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

                    Dates

                    • Created:
                      Updated: