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

            bar Alexander Barkov
            bar Alexander Barkov
            Votes:
            0 Vote for this issue
            Watchers:
            3 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.