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

Bad result set with expansions when using a prefix key

        Details

          Description

          This bug is very similar to MDEV-8625.

          The script with no index:

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

          and the script with KEY(a):

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

          correctly return two rows:

          +------+
| a    |
+------+
| a    |
| ä    |
+------+

          This script with KEY(a(1)):

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

          erroneously returns only one row:

          +------+
| a    |
+------+
| a    |
+------+

          The problem happens because when building a search value for the prefix key lookup, the character combination 'ae' (which is equal to 'ä') is broken apart and only the leading letter 'a' is stored into the search value.

            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: