The existing tests assert on filter results, which stay correct for ASCII values
even when the collation is never applied. Add a test which asserts on the
lookup's own compiled output, so that the mechanism failing open is caught
rather than passing silently.
Explain why the placeholder is compared literally: a field declaring its own
get_placeholder() compiles to something other than '%s', and splicing a COLLATE
clause into that is not safe, so any other right-hand side is left alone.
The documentation note described the folding as specific to the German
eszett. It is the common example rather than the rule: the collation treats a
character as equivalent to the sequence it expands to in upper case, which also
covers ligatures. Note too that a case-insensitive exact match on a collated
field may now return more than one object.
Django's PostgreSQL backend compiles icontains, iexact, istartswith and
iendswith as UPPER(col::text) LIKE UPPER(%s). UPPER() folds according to the
collation of its argument, and the two sides do not share one: the column folds
under its own collation while the parameter folds under the database default.
For a column using natural_sort, UPPER('ß') is 'SS' on the left and 'ß' on the
right, so searching for 'ß' matched nothing. This affects the name field of most
models and all four case-insensitive lookups, which are also exposed through the
REST API as __ic, __ie, __isw and __iew.
Apply the column's collation to the parameter as well, inside the UPPER() call,
so that both sides fold the same way. Matching on those fields becomes
bidirectional, so 'Strasse' finds 'Straße' and vice versa. Fields without the
collation are unchanged.
The lookups only collate a bare column compared against a simple value. An
expression which already carries an explicit collation, such as Collate() or
CollateAsChar(), would otherwise raise a collation mismatch error.
Jason Novinger
bctiemann
mburggraf
Artem Kotik
Jeremy Stretch
jeremystretch