Index on a NOPAD column loses rows for "col = const COLLATE utf8mb4_bin" (PAD SPACE binary collation)

When an indexed VARCHAR column has a NO PAD collation and an equality applies a PAD SPACE binary collation explicitly (COLLATE utf8mb4_bin), ref/range access on the index returns only the byte-identical row and silently drops every row that differs only by trailing spaces. Evaluated row by row, the server says those rows satisfy the predicate. The optimizer picks the index by itself; no hint is needed.

CREATE TABLE c4 (c VARCHAR(20) COLLATE utf8mb4_general_nopad_ci, KEY(c)) ENGINE=InnoDB;

INSERT INTO c4 VALUES ('a'), ('a '), ('a  '), ('a   ');

-- 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);

SELECT count(*) FROM c4 WHERE c = 'a' COLLATE utf8mb4_bin;

SELECT count(*) FROM c4 IGNORE INDEX(c) WHERE c = 'a' COLLATE utf8mb4_bin;

EXPLAIN SELECT count(*) FROM c4 WHERE c = 'a' COLLATE utf8mb4_bin;

Actual (13.2.0 main, d45cf75c):

+--------+------+

| c      | pred |

+--------+------+

| [a]    |    1 |

| [a ]   |    1 |

| [a  ]  |    1 |

| [a   ] |    1 |

+--------+------+

count(*) with the index ......... 1

count(*) with IGNORE INDEX(c) ... 4

+------+-------------+-------+------+---------------+------+---------+-------+------+--------------------------+

| id   | select_type | table | type | possible_keys | key  | key_len | ref   | rows | Extra                    |

+------+-------------+-------+------+---------------+------+---------+-------+------+--------------------------+

|    1 | SIMPLE      | c4    | ref  | c             | c    | 83      | const | 1    | Using where; Using index |

+------+-------------+-------+------+---------------+------+---------+-------+------+--------------------------+

Expected: 4 from both queries. Three matching rows are lost.

Scope (4 rows 'a', 'a ', 'a ', 'a '; correct answer 4)

column collation           comparison collation   with index   IGNORE INDEX

utf8mb4_general_nopad_ci   utf8mb4_bin                 1             4       WRONG

utf8mb4_nopad_bin          utf8mb4_bin                 1             4       WRONG

utf8mb4_unicode_nopad_ci   utf8mb4_bin                 1             4       WRONG

utf8mb4_0900_ai_ci (alias) utf8mb4_bin                 1             4       WRONG

utf8mb4_general_nopad_ci   utf8mb4_general_ci          4             4       ok

utf8mb4_general_ci         utf8mb4_bin                 4             4       ok

utf8mb4_bin                utf8mb4_bin                 4             4       ok

• InnoDB, MyISAM and Aria all return 1.
• A join whose ON clause carries the COLLATE loses the same rows:

  CREATE TABLE pl (c VARCHAR(20) COLLATE utf8mb4_general_nopad_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');

  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_general_ci as the comparison collation is correct even though it also pads – it is not a binary collation, so the index is not used. That is what localises the defect.

Cause

Field_longstr::cmp_to_string_with_stricter_collation() (sql/field.cc:7664 at d45cf75c), used by can_optimize_keypart_ref() and, for equality, by can_optimize_range(), allows an index whose collation differs from the comparison collation when the comparison collation is MY_CS_BINSORT:

  return (!cmp_is_done_using_type_handler_of_this(cond, item) ?

          Data_type_compatibility::INCOMPATIBLE_DATA_TYPE :

          (!charset()->eq_collation(cond->compare_collation()) &&

           !(cond->compare_collation()->state & MY_CS_BINSORT) &&

           !Utf8_narrow::should_do_narrowing(this, cond->compare_collation())) ?

          Data_type_compatibility::INCOMPATIBLE_COLLATION :

          Data_type_compatibility::OK);

The comment in can_optimize_keypart_ref() states the assumption: with a binary comparison "we're certain that will find the row with the identical binary". That is true for the identical row, but the residual condition is then evaluated only on the rows the lookup found. A PAD SPACE binary collation equates 'a' and 'a ', which a NO PAD index stores under different keys, so the lookup for key 'a' never reads the other matching rows.

Suggested fix

Do not take the MY_CS_BINSORT exception when the comparison collation pads and the column collation does not, i.e. additionally require

!(!(cond->compare_collation()->state & MY_CS_NOPAD) && (charset()->state & MY_CS_NOPAD))

or, for that combination, build the lookup as the range ['a', 'a' + trailing spaces] and keep the residual filter.

Workaround

IGNORE INDEX, or compare with a collation that has the same pad attribute as the column.

Related, not the same

MDEV-14350 and MDEV-27632 (LIKE on a nopad index), MDEV-38712 (strnncollsp_nchars for NOPAD), MDEV-39193 (utf8mb4_0900_ai_ci range with a bound longer than the column), MDEV-33812 (Not a Bug). None covers an equality with a PAD SPACE binary COLLATE against a NO PAD index.