MySQL Bugs: #121466: Incorrect index range for VARCHAR(1) with utf8mb4_0900_ai

Bug #121466	Incorrect index range for VARCHAR(1) with utf8mb4_0900_ai_ci causes missing rows
Submitted:	9 Oct 8:21	Modified:	14 Oct 11:32
Reporter:	jinxin gui	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S2 (Serious)
Version:	8.0.46, 8.4.11, 9.7.2, 26.07	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	INDEX, varchar

Description:
On MySQL 26.10.0-er, a query using x BETWEEN 'r' AND 'ss' on an indexed VARCHAR(1) column returns only 's', while the same query with IGNORE INDEX returns both 's' and 'ß'.

The column uses utf8mb4_0900_ai_ci, under which 'ß' compares equal to 'ss'. However, the index range shown by EXPLAIN FORMAT=TREE is 'r' <= x <= 's'. This truncated upper bound excludes 'ß' before the original predicate is evaluated.

This appears related to the issue discussed in Bug #119286 (https://bugs.mysql.com/bug.php?id=119286), which a developer indicated was fixed in MySQL 8.4.0. I observe similar incorrect behavior in MySQL 26.10.0-er, suggesting a possible regression.

How to repeat:
Run the following in an empty schema:

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 FORMAT=TREE SELECT * FROM t WHERE x BETWEEN 'r' AND 'ss';

Actual result:

The query using the index returns one row:

s

The query with IGNORE INDEX returns two rows:

ß
s

The index access uses the truncated range:

Covering index range scan on t using x over ('r' <= x <= 's')

Expected result:

Both queries should return 's' and 'ß'. Since 'ß' compares equal to 'ss' under utf8mb4_0900_ai_ci, it satisfies the inclusive upper bound. Using an index should not change the result set.

The bug fix mentioned in bug#119286 was later reverted under bug#118009, which is why you still see the bug in MySQL 26.10.0.

The problem is already tracked by bug#39298, so I'm closing this bug as a duplicate.

Thank you for the clarification and for the team's efforts to fix this issue. I appreciate your time and help in reviewing this report.