Index range scan on VARCHAR(1) omits ß for BETWEEN 'r' AND 'ss' under utf8mb4_0900_ai_ci

A BETWEEN predicate on an indexed VARCHAR(1) column returns different results depending on whether the index is used.

With utf8mb4_0900_ai_ci, the indexed query returns only 's', while the same query with IGNORE INDEX returns both 'ß' and 's'.

The issue reproduces using the native mariadb command-line client. DISTINCT, subqueries and CASE expressions are not required.

Reproducer:

CREATE TABLE t (

x VARCHAR(1),

INDEX (x)

) ENGINE=InnoDB

DEFAULT CHARSET=utf8mb4

COLLATE=utf8mb4_0900_ai_ci;

INSERT INTO t VALUES ('a'), ('b'), ('æ'), ('ß'), ('s');

INSERT INTO t SELECT 'x' FROM t;

INSERT INTO t SELECT 'x' FROM t;

ANALYZE TABLE t;

SELECT *

FROM t

WHERE x BETWEEN 'r' AND 'ss';

SELECT *

FROM t IGNORE INDEX (x)

WHERE x BETWEEN 'r' AND 'ss';

EXPLAIN

SELECT *

FROM t

WHERE x BETWEEN 'r' AND 'ss';

Actual result on 11.8.9-MariaDB:

The query using the index returns one row:

x

s

The query with IGNORE INDEX returns two rows:

x

ß

s

The execution plan for the indexed query is:

id  select_type  table  type   possible_keys  key  key_len  ref   rows  Extra

1   SIMPLE       t      range  x              x    7        NULL  1     Using where; Using index

Expected result:

Both queries should return 'ß' and 's', ignoring row order. Under the specified collation, 'ß' compares equal to 'ss' and satisfies the inclusive upper bound. Selecting an index access path should not remove a row that satisfies the predicate.

Related reports:

The corresponding MySQL report is:
https://bugs.mysql.com/bug.php?id=121466