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

Bad result set with expansions when using a prefix key

    XMLWordPrintable

    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.

        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: