[MDEV-27632] Wrong result upon LIKE on indexed column with nopad collation Created: 2022-01-26  Updated: 2023-11-28

Status: Open
Project: MariaDB Server
Component/s: Character Sets, Optimizer
Affects Version/s: 10.5, 10.6, 10.7, 10.8
Fix Version/s: 10.5, 10.6

Type: Bug Priority: Major
Reporter: Elena Stepanova Assignee: Sergei Petrunia
Resolution: Unresolved Votes: 0
Labels: 11.0-sel


 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
 
    }


Generated at Thu Feb 08 09:54:24 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.