LIKE 'prefix%' through an index on a utf8mb4_bin or utf8mb4_nopad_bin column misses values whose character after the prefix is above U+FFFF

The range the optimizer builds for b LIKE 'a%' on an indexed utf8mb4_bin (or utf8mb4_nopad_bin) column ends below every value that continues the prefix with a supplementary character (U+10000 to U+10FFFF), so those rows are not returned. The default plan is affected (a range scan on the index), not only FORCE INDEX. 'a' followed by U+FFFF is found; 'a' followed by U+10000 is not. Patterns starting with a wildcard, NOT LIKE, and patterns without a wildcard are not affected.

SET NAMES utf8mb4;

CREATE TABLE t1 (id INT PRIMARY KEY, b VARCHAR(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin,

  n VARCHAR(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_nopad_bin, KEY (b), KEY (n));

-- 2: 'a' U+1F600, 3: 'ab' U+1F600 'c', 4: 'a' U+65E5, 6: 'a' U+10000, 7: 'a' U+FFFF

INSERT INTO t1 (id, b) VALUES (1, 'a'), (2, _utf8mb4 0x61F09F9880), (3, _utf8mb4 0x6162F09F988063),

  (4, _utf8mb4 0x61E697A5), (5, 'ab'), (6, _utf8mb4 0x61F0908080), (7, _utf8mb4 0x61EFBFBF);

UPDATE t1 SET n = b;

SELECT GROUP_CONCAT(id ORDER BY id) FROM t1 WHERE b LIKE 'a%';

SELECT GROUP_CONCAT(id ORDER BY id) FROM t1 IGNORE INDEX (b) WHERE b LIKE 'a%';

SELECT GROUP_CONCAT(id ORDER BY id) FROM t1 FORCE INDEX (b) WHERE b LIKE 'ab%';

SELECT GROUP_CONCAT(id ORDER BY id) FROM t1 IGNORE INDEX (b) WHERE b LIKE 'ab%';

SELECT GROUP_CONCAT(id ORDER BY id) FROM t1 FORCE INDEX (n) WHERE n LIKE 'a%';

SELECT GROUP_CONCAT(id ORDER BY id) FROM t1 IGNORE INDEX (n) WHERE n LIKE 'a%';

EXPLAIN SELECT GROUP_CONCAT(id ORDER BY id) FROM t1 WHERE b LIKE 'a%';

DROP TABLE t1;

Leads to, on 12.3.3 and 13.0.2 alike:

b LIKE 'a%'                      1,3,4,5,7

b LIKE 'a%', IGNORE INDEX (b)    1,2,3,4,5,6,7

b LIKE 'ab%', FORCE INDEX (b)    5

b LIKE 'ab%', IGNORE INDEX (b)   3,5

n LIKE 'a%', FORCE INDEX (n)     1,3,4,5,7

n LIKE 'a%', IGNORE INDEX (n)    1,2,3,4,5,6,7

EXPLAIN: id 1, SIMPLE, t1, range, possible_keys b, key b, key_len 83, rows 5, Using where; Using index

Expected: the same rows with and without the index (1,2,3,4,5,6,7 for 'a%', 3,5 for 'ab%').

With the same rows, utf8mb4_general_ci and utf8mb4_uca1400_ai_ci columns give the right result through the index; a utf8mb4_unicode_ci column loses the same rows (MDEV-20884 reports that collation, with a prefix index). In MySQL 8.4.13, with values of the same kinds, utf8mb4_0900_bin gives the right result through the index and utf8mb4_bin loses the same rows.

From the code, the cause seems to be max_sort_char: it is 0xFFFF in my_charset_utf8mb4_bin and my_charset_utf8mb4_nopad_bin (strings/ctype-utf8.c), and my_like_range_mb() pads the upper end of the LIKE range with it. The range for 'a%' therefore ends at 'a' followed by U+FFFF characters, and in these binary collations every character from U+10000 to U+10FFFF sorts above U+FFFF. A max_sort_char of 0x10FFFF would match the repertoire of the utf8mb4 binary collations.