MySQL Bugs: #121581: ORDER BY satisfied by index scan returns wrong order when skipped const keypart is expanding collation prefix

Bug #121581	ORDER BY satisfied by index scan returns wrong order when skipped const keypart is expanding collation prefix
Submitted:	22 Oct 13:20	Modified:	23 Oct 5:47
Reporter:	Laurynas Biveinis (OCA)	Email Updates:
Status:	Verified	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

Description:
The optimizer decides index k(a, s(1), d) can satisfy ORDER BY a, d, because s in the middle is constrained by equality and skips the sort. But s is a prefix keypart, and the two rows matching ('ß' and 'ss', equal under the collation) have different truncated index values ('ß' and 's'), so index order does not match the ORDER BY order. The equality holds for full values, not for the stored prefixes that determine the scan order.

How to repeat:
CREATE TABLE t (
       id INT PRIMARY KEY,
       a INT,
       s VARCHAR(10) COLLATE utf8mb4_0900_ai_ci,
       d INT,
       KEY k(a, s(1), d)
) ENGINE=InnoDB;

INSERT INTO t VALUES (1, 1, 'ß', 1), (2, 1, 'ss', 2);

# Shows index scan with no filesort
EXPLAIN SELECT id FROM t FORCE INDEX (k) WHERE s = 'ss' ORDER BY a, d;

# 2, 1
SELECT id FROM t FORCE INDEX (k) WHERE s = 'ss' ORDER BY a, d;

# 1, 2 
SELECT id FROM t IGNORE INDEX (k) WHERE s = 'ss' ORDER BY a, d;

Hi Laurynas Biveinis,

Thank you for the test case. Verified as described.

Thank you for the verification. I wonder though, why did you edit the Version field to older versions? 9.7.4 -> 9.7.2, 8.4.13 -> 8.4.11, 26.10.0 -> 26.7.0?