Alexander Barkov added a comment - 2022-11-22 13:39 - edited The problem was caused by MDEV-25904 , by this chunk for Field_varstring (and a similar chunk for Field_blob) @@ -7880,8 +7854,12 @@ int Field_varstring::cmp_prefix(const uchar *a_ptr, const uchar *b_ptr, a_length= uint2korr(a_ptr); b_length= uint2korr(b_ptr); } - return cmp_str_prefix(a_ptr+length_bytes, a_length, b_ptr+length_bytes, - b_length, prefix_len, field_charset); + return field_charset->coll->strnncollsp_nchars(field_charset, + a_ptr + length_bytes, + a_length, + b_ptr + length_bytes, + b_length, + prefix_len / field_charset->mbmaxlen); } This change was OK for PAD SPACE collations and improved performance for VARCHAR/TEXT columns. But it appeared not to be correct for NOPAD collations - it changed the behaviour to wrong. It seems, to combine both performance and correct behavior, instead of one function strnncollsp_nchars() we need two separate functions: strnncollsp_nchars_char() strnncollsp_nchars_varchar() Both should truncate too long strings. However, only the CHAR version should (virtually) pad too short strings with trailing spaces.

Alexander Barkov added a comment - 2022-11-23 10:55 Also repeatable with TEXT: CREATE OR REPLACE TABLE t1 ( id INT , data TEXT, KEY data_id (data(10),id) ) COLLATE utf8mb3_unicode_nopad_ci ENGINE=MyISAM PARTITION BY RANGE COLUMNS (id) ( PARTITION p10 VALUES LESS THAN (20), PARTITION p20 VALUES LESS THAN MAXVALUE ); INSERT INTO t1 VALUES (30, 'ss ' ), (10, 'ß ' ); SELECT id FROM t1 WHERE data= 'ss ' ORDER BY id; SELECT id FROM t1 WHERE data= 'ss ' ORDER BY id DESC ; MariaDB [test]> SELECT id FROM t1 WHERE data='ss ' ORDER BY id; +------+ | id | +------+ | 30 | | 10 | +------+ 2 rows in set (0.002 sec)   MariaDB [test]> SELECT id FROM t1 WHERE data='ss ' ORDER BY id DESC; +------+ | id | +------+ | 10 | | 30 | +------+ 2 rows in set (0.002 sec)