Description:
When an indexed VARCHAR column has a NO PAD collation (any utf8mb4_0900_*
collation) and an equality applies a PAD SPACE binary collation explicitly
(COLLATE utf8mb4_bin), the index access returns only the row that matches
byte-for-byte and silently drops every row that differs only by trailing spaces.
Evaluated row by row, the server says those rows satisfy the predicate.
utf8mb4_bin is PAD SPACE, so under it 'a ' = 'a' is TRUE.
utf8mb4_0900_ai_ci is NO PAD, so the index on the column orders 'a' and 'a '
as different keys.
The plan uses the index on its own (no hint), looks up the single key 'a', and
the residual filter cannot bring back rows the range scan never read:
-> Aggregate: count(0)
-> Filter: (c4.c = <cache>(('a' collate utf8mb4_bin)))
-> Covering index range scan on c4 using c over (c = 'a')
Mechanism. comparable_in_index() (sql/range_optimizer/range_optimizer.cc:1226
at 3b99be40; also used for ref access from sql/sql_optimizer.cc:7352) normally
refuses an index whose collation differs from the comparison collation, but makes
an exception for = / <=> when the comparison collation is binary-sorted:
if ((field->result_type() == STRING_RESULT &&
field->match_collation_to_optimize_range() &&
value->result_type() == STRING_RESULT && itype == Field::itRAW &&
field->charset() != cond_func->compare_collation() &&
!((comp_type == Item_func::EQUAL_FUNC ||
comp_type == Item_func::EQ_FUNC) &&
cond_func->compare_collation()->state & MY_CS_BINSORT)))
return false;
The comment explains the assumption: a binary comparison is stricter than the
column's collation, so every row it accepts lies inside the index range for the
same key ("WHERE latin1_swedish_ci_column = 'a' COLLATE latin1_bin"). That holds
only if the binary collation does not equate strings that the column's collation
distinguishes. utf8mb4_bin is PAD SPACE: it equates 'a' and 'a ', which a
NO PAD column collation keeps apart -- so here the "stricter" collation is in
fact looser, and the range [c = 'a'] misses the matching rows.
Scope measured on 26.10.0 (4 rows 'a', 'a ', 'a ', 'a '; correct answer 4):
column collation comparison collation with index IGNORE INDEX
utf8mb4_0900_ai_ci utf8mb4_bin 1 4 WRONG
utf8mb4_0900_as_cs utf8mb4_bin 1 4 WRONG
utf8mb4_0900_ai_ci utf8mb4_general_ci 4 4 ok
utf8mb4_general_ci utf8mb4_bin 4 4 ok
utf8mb4_bin utf8mb4_bin 4 4 ok
latin1_swedish_ci utf8mb4_bin 4 4 ok
utf8mb4_general_ci as the comparison collation is correct even though it also
pads, because it is not MY_CS_BINSORT and so does not get the exception -- which
is what localises the defect to the binary-collation shortcut.
The same happens in a join whose ON clause carries the COLLATE, and with a
prepared-statement parameter (= ? COLLATE utf8mb4_bin).
How to repeat:
Stock server, default settings.
CREATE DATABASE IF NOT EXISTS t078; USE t078;
CREATE TABLE c4(c VARCHAR(20) COLLATE utf8mb4_0900_ai_ci, KEY(c)) ENGINE=InnoDB;
INSERT INTO c4 VALUES ('a'), ('a '), ('a '), ('a ');
ANALYZE TABLE c4;
-- utf8mb4_bin is PAD SPACE; every row satisfies the predicate:
SELECT concat('[',c,']') AS c, (c = 'a' COLLATE utf8mb4_bin) AS pred
FROM c4 IGNORE INDEX(c);
+--------+------+
| c | pred |
+--------+------+
| [a] | 1 |
| [a ] | 1 |
| [a ] | 1 |
| [a ] | 1 |
+--------+------+
SELECT count(*) FROM c4 WHERE c = 'a' COLLATE utf8mb4_bin; -- 1
SELECT count(*) FROM c4 IGNORE INDEX(c) WHERE c = 'a' COLLATE utf8mb4_bin; -- 4
Expected: 4 from both queries.
Actual (26.10.0, 9.7.2, 8.4.11): 1 from the first -- three matching rows are not
returned. SHOW WARNINGS after the query is empty.
SELECT concat('[',c,']') AS c FROM c4 WHERE c = 'a' COLLATE utf8mb4_bin;
+------+
| c |
+------+
| [a] |
+------+
EXPLAIN SELECT count(*) FROM c4 WHERE c = 'a' COLLATE utf8mb4_bin;
-> Aggregate: count(0) (cost=0.69 rows=1)
-> Filter: (c4.c = <cache>(('a' collate utf8mb4_bin))) (cost=0.46 rows=1)
-> Covering index range scan on c4 using c over (c = 'a') (cost=0.46 rows=1)
Control in the same table -- a PAD SPACE comparison collation that is not binary:
SELECT count(*) FROM c4 WHERE c = 'a' COLLATE utf8mb4_general_ci; -- 4
Prepared statement, same result:
SET @k = 'a';
PREPARE s FROM 'SELECT count(*) FROM c4 WHERE c = ? COLLATE utf8mb4_bin';
EXECUTE s USING @k; -- 1
Join, same rows lost:
CREATE TABLE pl(c VARCHAR(20) COLLATE utf8mb4_0900_ai_ci, KEY(c)) ENGINE=InnoDB;
CREATE TABLE pr(c VARCHAR(20) COLLATE utf8mb4_bin, KEY(c)) ENGINE=InnoDB;
INSERT INTO pl VALUES ('a'),('a '),('a ');
INSERT INTO pr VALUES ('a');
ANALYZE TABLE pl, pr;
SELECT count(*) FROM pl JOIN pr ON pl.c = pr.c COLLATE utf8mb4_bin; -- 1
SELECT count(*) FROM pl IGNORE INDEX(c) JOIN pr IGNORE INDEX(c)
ON pl.c = pr.c COLLATE utf8mb4_bin; -- 3
utf8mb4_0900_as_cs as the column collation gives the same 1 vs 4.
Workaround: IGNORE INDEX, or compare with a collation of the same pad attribute
as the column.
Suggested fix:
In comparable_in_index(), only take the "binary comparison collation may use a
differently-collated index" exception when the binary collation cannot equate
strings that the column collation distinguishes -- concretely, not when the
comparison collation is PAD SPACE and the column collation is NO PAD:
(cond_func->compare_collation()->state & MY_CS_BINSORT) &&
!( (field->charset()->pad_attribute == NO_PAD) &&
(cond_func->compare_collation()->pad_attribute == PAD_SPACE) )
Alternatively, for that combination, build the range as the prefix interval
['a', 'a' followed by spaces] rather than the single key 'a', and keep the
residual filter.
© 2026, Oracle Corporation and/or its affiliates
Description: When an indexed VARCHAR column has a NO PAD collation (any utf8mb4_0900_* collation) and an equality applies a PAD SPACE binary collation explicitly (COLLATE utf8mb4_bin), the index access returns only the row that matches byte-for-byte and silently drops every row that differs only by trailing spaces. Evaluated row by row, the server says those rows satisfy the predicate. utf8mb4_bin is PAD SPACE, so under it 'a ' = 'a' is TRUE. utf8mb4_0900_ai_ci is NO PAD, so the index on the column orders 'a' and 'a ' as different keys. The plan uses the index on its own (no hint), looks up the single key 'a', and the residual filter cannot bring back rows the range scan never read: -> Aggregate: count(0) -> Filter: (c4.c = <cache>(('a' collate utf8mb4_bin))) -> Covering index range scan on c4 using c over (c = 'a') Mechanism. comparable_in_index() (sql/range_optimizer/range_optimizer.cc:1226 at 3b99be40; also used for ref access from sql/sql_optimizer.cc:7352) normally refuses an index whose collation differs from the comparison collation, but makes an exception for = / <=> when the comparison collation is binary-sorted: if ((field->result_type() == STRING_RESULT && field->match_collation_to_optimize_range() && value->result_type() == STRING_RESULT && itype == Field::itRAW && field->charset() != cond_func->compare_collation() && !((comp_type == Item_func::EQUAL_FUNC || comp_type == Item_func::EQ_FUNC) && cond_func->compare_collation()->state & MY_CS_BINSORT))) return false; The comment explains the assumption: a binary comparison is stricter than the column's collation, so every row it accepts lies inside the index range for the same key ("WHERE latin1_swedish_ci_column = 'a' COLLATE latin1_bin"). That holds only if the binary collation does not equate strings that the column's collation distinguishes. utf8mb4_bin is PAD SPACE: it equates 'a' and 'a ', which a NO PAD column collation keeps apart -- so here the "stricter" collation is in fact looser, and the range [c = 'a'] misses the matching rows. Scope measured on 26.10.0 (4 rows 'a', 'a ', 'a ', 'a '; correct answer 4): column collation comparison collation with index IGNORE INDEX utf8mb4_0900_ai_ci utf8mb4_bin 1 4 WRONG utf8mb4_0900_as_cs utf8mb4_bin 1 4 WRONG utf8mb4_0900_ai_ci utf8mb4_general_ci 4 4 ok utf8mb4_general_ci utf8mb4_bin 4 4 ok utf8mb4_bin utf8mb4_bin 4 4 ok latin1_swedish_ci utf8mb4_bin 4 4 ok utf8mb4_general_ci as the comparison collation is correct even though it also pads, because it is not MY_CS_BINSORT and so does not get the exception -- which is what localises the defect to the binary-collation shortcut. The same happens in a join whose ON clause carries the COLLATE, and with a prepared-statement parameter (= ? COLLATE utf8mb4_bin). How to repeat: Stock server, default settings. CREATE DATABASE IF NOT EXISTS t078; USE t078; CREATE TABLE c4(c VARCHAR(20) COLLATE utf8mb4_0900_ai_ci, KEY(c)) ENGINE=InnoDB; INSERT INTO c4 VALUES ('a'), ('a '), ('a '), ('a '); ANALYZE TABLE c4; -- utf8mb4_bin is PAD SPACE; every row satisfies the predicate: SELECT concat('[',c,']') AS c, (c = 'a' COLLATE utf8mb4_bin) AS pred FROM c4 IGNORE INDEX(c); +--------+------+ | c | pred | +--------+------+ | [a] | 1 | | [a ] | 1 | | [a ] | 1 | | [a ] | 1 | +--------+------+ SELECT count(*) FROM c4 WHERE c = 'a' COLLATE utf8mb4_bin; -- 1 SELECT count(*) FROM c4 IGNORE INDEX(c) WHERE c = 'a' COLLATE utf8mb4_bin; -- 4 Expected: 4 from both queries. Actual (26.10.0, 9.7.2, 8.4.11): 1 from the first -- three matching rows are not returned. SHOW WARNINGS after the query is empty. SELECT concat('[',c,']') AS c FROM c4 WHERE c = 'a' COLLATE utf8mb4_bin; +------+ | c | +------+ | [a] | +------+ EXPLAIN SELECT count(*) FROM c4 WHERE c = 'a' COLLATE utf8mb4_bin; -> Aggregate: count(0) (cost=0.69 rows=1) -> Filter: (c4.c = <cache>(('a' collate utf8mb4_bin))) (cost=0.46 rows=1) -> Covering index range scan on c4 using c over (c = 'a') (cost=0.46 rows=1) Control in the same table -- a PAD SPACE comparison collation that is not binary: SELECT count(*) FROM c4 WHERE c = 'a' COLLATE utf8mb4_general_ci; -- 4 Prepared statement, same result: SET @k = 'a'; PREPARE s FROM 'SELECT count(*) FROM c4 WHERE c = ? COLLATE utf8mb4_bin'; EXECUTE s USING @k; -- 1 Join, same rows lost: CREATE TABLE pl(c VARCHAR(20) COLLATE utf8mb4_0900_ai_ci, KEY(c)) ENGINE=InnoDB; CREATE TABLE pr(c VARCHAR(20) COLLATE utf8mb4_bin, KEY(c)) ENGINE=InnoDB; INSERT INTO pl VALUES ('a'),('a '),('a '); INSERT INTO pr VALUES ('a'); ANALYZE TABLE pl, pr; SELECT count(*) FROM pl JOIN pr ON pl.c = pr.c COLLATE utf8mb4_bin; -- 1 SELECT count(*) FROM pl IGNORE INDEX(c) JOIN pr IGNORE INDEX(c) ON pl.c = pr.c COLLATE utf8mb4_bin; -- 3 utf8mb4_0900_as_cs as the column collation gives the same 1 vs 4. Workaround: IGNORE INDEX, or compare with a collation of the same pad attribute as the column. Suggested fix: In comparable_in_index(), only take the "binary comparison collation may use a differently-collated index" exception when the binary collation cannot equate strings that the column collation distinguishes -- concretely, not when the comparison collation is PAD SPACE and the column collation is NO PAD: (cond_func->compare_collation()->state & MY_CS_BINSORT) && !( (field->charset()->pad_attribute == NO_PAD) && (cond_func->compare_collation()->pad_attribute == PAD_SPACE) ) Alternatively, for that combination, build the range as the prefix interval ['a', 'a' followed by spaces] rather than the single key 'a', and keep the residual filter.