Search and table filters on SQLite ignore case only for ASCII: \"école\" does not find \"École\ #505

New issue

Closed

opened 2026-10-01 10:47:10 +00:00 by tiagoagueda · 0 comments

tiagoagueda commented

2026-10-01 10:47:10 +00:00

Owner

Found on 2026-09-29 in a read-only review of postulo at 95348908d, reading the search and table filter lookups and running them against SQLite with the snapshot's interpreter.

Related to #29.

Checked again on 2026-10-01 against main at 0a5cad587: still present. The code is now at jobs/forms.py:384 and :418 (the name__iexact duplicate-name checks); the other locations have not moved.

Global search and every text filter on a table use __icontains. On SQLite, the default database, Django turns that into LIKE, and SQLite's LIKE folds case for ASCII letters only. A query in lower case misses every record whose matching word has an accented or non-Latin capital, and the reverse. Postulo is translated into some sixty languages, most of which have such capitals.

Where: core/search.py:113-118, contains, and core/search.py:130-144, ranked, used by every search group; core/tables.py:563-569, Table.filter, for every text column; config/sqlite.py, which sets pragmas and the transaction mode but registers no Unicode-aware function; config/settings/base.py:134-138, where SQLite is the default. The same applies to the other __icontains / __iexact lookups in src/postulo (52 outside migrations), among them the duplicate-name checks name__iexact in jobs/forms.py:383 and :417.
What happens: with companies École Polytechnique, Škoda, Kraków Institut and Αθηναϊκή Εταιρεία, search finds nothing for école, škoda, KRAKÓW or αθηναϊκή, and finds them for ÉCOLE, ŠKODA, kraków. The Companies Name filter name=école returns an empty table. The duplicate-name check lets škoda through beside Škoda.
Why it matters: search says "Nothing matches" for a record the person can see on another page, and the filters give empty tables, for anyone whose data is not plain ASCII. PostgreSQL is not affected (its UPPER follows the database locale), so the behaviour differs between the two supported databases.
Fix: on SQLite, register a Unicode-aware like when a connection is created, in a connection_created receiver in core: connection.connection.create_function("like", 3, fn, deterministic=True), where fn(pattern, value, escape) translates the pattern (%, _ and the escape character) into a regular expression and matches it against value with re.IGNORECASE, or compares casefold() of both sides. Django's SQLite icontains, iexact, istartswith and iendswith all compile to LIKE … ESCAPE '\', so one function covers every lookup. Note the receiver in config/sqlite.py's docstring.

Done when

tests/test_search.py has a test that searching école, škoda and αθηναϊκή finds companies named École…, Škoda and Αθηναϊκή…, and tests/test_tables.py one that the Companies name filter does the same; both fail on SQLite today.

Found on 2026-09-29 in a read-only review of postulo at 95348908d, reading the search and table filter lookups and running them against SQLite with the snapshot's interpreter. Related to #29. Checked again on 2026-10-01 against `main` at `0a5cad587`: still present. The code is now at `jobs/forms.py:384` and `:418` (the `name__iexact` duplicate-name checks); the other locations have not moved. Global search and every text filter on a table use `__icontains`. On SQLite, the default database, Django turns that into `LIKE`, and SQLite's `LIKE` folds case for ASCII letters only. A query in lower case misses every record whose matching word has an accented or non-Latin capital, and the reverse. Postulo is translated into some sixty languages, most of which have such capitals. - **Where:** `core/search.py:113-118`, `contains`, and `core/search.py:130-144`, `ranked`, used by every search group; `core/tables.py:563-569`, `Table.filter`, for every `text` column; `config/sqlite.py`, which sets pragmas and the transaction mode but registers no Unicode-aware function; `config/settings/base.py:134-138`, where SQLite is the default. The same applies to the other `__icontains` / `__iexact` lookups in `src/postulo` (52 outside migrations), among them the duplicate-name checks `name__iexact` in `jobs/forms.py:383` and `:417`. - **What happens:** with companies *École Polytechnique*, *Škoda*, *Kraków Institut* and *Αθηναϊκή Εταιρεία*, search finds nothing for `école`, `škoda`, `KRAKÓW` or `αθηναϊκή`, and finds them for `ÉCOLE`, `ŠKODA`, `kraków`. The Companies *Name* filter `name=école` returns an empty table. The duplicate-name check lets *škoda* through beside *Škoda*. - **Why it matters:** search says "Nothing matches" for a record the person can see on another page, and the filters give empty tables, for anyone whose data is not plain ASCII. PostgreSQL is not affected (its `UPPER` follows the database locale), so the behaviour differs between the two supported databases. - **Fix:** on SQLite, register a Unicode-aware `like` when a connection is created, in a `connection_created` receiver in core: `connection.connection.create_function("like", 3, fn, deterministic=True)`, where `fn(pattern, value, escape)` translates the pattern (`%`, `_` and the escape character) into a regular expression and matches it against `value` with `re.IGNORECASE`, or compares `casefold()` of both sides. Django's SQLite `icontains`, `iexact`, `istartswith` and `iendswith` all compile to `LIKE … ESCAPE '\'`, so one function covers every lookup. Note the receiver in `config/sqlite.py`'s docstring. ## Done when - `tests/test_search.py` has a test that searching `école`, `škoda` and `αθηναϊκή` finds companies named *École…*, *Škoda* and *Αθηναϊκή…*, and `tests/test_tables.py` one that the Companies name filter does the same; both fail on SQLite today.

tiagoagueda added this to the 0.5.0 milestone

2026-10-01 10:47:10 +00:00

tiagoagueda added the

bug

internationalisation

labels

2026-10-01 10:47:10 +00:00

tiagoagueda referenced this issue from a commit

2026-10-04 10:04:58 +00:00

Fold the case of every script in SQLite's LIKE

tiagoagueda closed this issue

2026-10-04 10:04:58 +00:00