SELECT DISTINCT with LIKE returns an empty result when using index for group-by with trailing-space-equivalent values

SELECT DISTINCT incorrectly returns an empty result for a LIKE predicate when an indexed VARCHAR column contains two values differing only by a trailing space.

The issue reproduces when the value with a trailing space is inserted first. Ignoring the index returns the expected row.

Reproducer:

CREATE TABLE t (

v VARCHAR(20),

KEY(v)

) ENGINE=InnoDB

CHARACTER SET=latin1

COLLATE=latin1_swedish_ci;

INSERT INTO t VALUES ('a '), ('a');

SELECT DISTINCT v FROM t WHERE v LIKE 'a';

SELECT DISTINCT v FROM t IGNORE INDEX(v) WHERE v LIKE 'a';

EXPLAIN SELECT DISTINCT v FROM t WHERE v LIKE 'a';

Actual result on 11.8.9-MariaDB:

The first query returns an empty result:

Empty set

The query with IGNORE INDEX returns:

v

a

The execution plan for the first query is:

id  select_type  table  type   possible_keys  key  key_len  ref   rows  Extra

1   SIMPLE       t      range  v              v    23       NULL  1     Using where; Using index for group-by

Expected result:

Both queries should return one row containing 'a':

v

a