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

Wrong result upon LIKE on indexed column with nopad collation

    XMLWordPrintable

    Details

      Description

      create table t (c char(16), key(c)) ENGINE=MyISAM COLLATE cp1250_general_nopad_ci;
      insert into t values('j'),('joo');
      select c from t where c like 'j%';
       
      # Cleanup
      drop table t;
      

      10.5 e8e755ea

      select c from t where c like 'j%';
      c
      joo
      

      The expected result is both rows.

      Reproducible on 10.5+ with at least MyISAM and Aria.

      The wrong result started showing up on 10.5 after this commit

      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
      

      however, it's very likely that the problem existed before, and the commit above just caused a change of plan.
      The plan from 10.5 (with a wrong result):

        "query_block": {
          "select_id": 1,
          "table": {
            "table_name": "t",
            "access_type": "range",
            "possible_keys": ["c"],
            "key": "c",
            "key_length": "17",
            "used_key_parts": ["c"],
            "rows": 1,
            "filtered": 100,
            "attached_condition": "t.c like 'j%'",
            "using_index": true
          }
      

      Explain from 10.4 (correct result):

        "query_block": {
          "select_id": 1,
          "table": {
            "table_name": "t",
            "access_type": "index",
            "possible_keys": ["c"],
            "key": "c",
            "key_length": "17",
            "used_key_parts": ["c"],
            "rows": 2,
            "filtered": 50,
            "attached_condition": "t.c like 'j%'",
            "using_index": true
          }
      

        Attachments

          Activity

            People

            Assignee:
            psergei Sergei Petrunia
            Reporter:
            elenst Elena Stepanova
            Votes:
            0 Vote for this issue
            Watchers:
            2 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.