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

Wrong result with prefix key on CHAR column and nopad collation

    XMLWordPrintable

    Details

      Description

      --source include/have_innodb.inc
       
      CREATE TABLE t1 (f char(8), KEY (f(4))) ENGINE=InnoDB COLLATE=utf8mb3_nopad_bin;
      INSERT INTO t1 VALUES ('y'),('x'),('x'),('x'),('w'),('w'),('v'),('u'),('t'),('t');
       
      EXPLAIN FORMAT=JSON
      SELECT * FROM t1 WHERE f >= 'w';
      SELECT * FROM t1 WHERE f >= 'w';
       
      # Cleanup
      DROP TABLE t1;
      

      Actual result on 10.5 4c3ad244

      SELECT * FROM t1 WHERE f >= 'w';
      f
      x
      x
      x
      y
      

      This is wrong, 'w' values should also be there.

      The following query will also fail with an empty result:

      SELECT * FROM t1 WHERE f = 'w';
      

      Changing the engine to MyISAM will return the correct result.

      The failure appeared in 10.5 branch after this commit in 10.5.3:

      commit eb483c5181ab430877c135c16224284cfc517b3d
      Author: Monty
      Date:   Fri Feb 28 12:59:30 2020 +0200
       
          Updated optimizer costs in multi_range_read_info_const() and sql_select.cc
      

      Plan on the current branch (wrong result)

      {
        "query_block": {
          "select_id": 1,
          "table": {
            "table_name": "t1",
            "access_type": "range",
            "possible_keys": ["f"],
            "key": "f",
            "key_length": "13",
            "used_key_parts": ["f"],
            "rows": 4,
            "filtered": 100,
            "attached_condition": "t1.f >= 'w'"
          }
        }
      }
      

      Plan before the change (correct result)

      {
        "query_block": {
          "select_id": 1,
          "table": {
            "table_name": "t1",
            "access_type": "ALL",
            "possible_keys": ["f"],
            "rows": 10,
            "filtered": 40,
            "attached_condition": "t1.f >= 'w'"
          }
        }
      }
      

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              bar Alexander Barkov
              Reporter:
              elenst Elena Stepanova
              Votes:
              0 Vote for this issue
              Watchers:
              5 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.