fix: Unicode-aware case-insensitive search on SQLite; PostgreSQL search-path JSON operator fixes - #29186
fix: Unicode-aware case-insensitive search on SQLite; PostgreSQL search-path JSON operator fixes#29186BLACKTHOMAS wants to merge 1 commit into
Conversation
…ch-path JSON operator fixes
b0d2a34 to
e62b556
Compare
|
Tested this branch against a SQLite install with non-ASCII content. The core fix works: of 16 Unicode cases I tried, 12 go from "no match" to "match" — ß, unaccented Greek, NFD vs NFC, fullwidth letters, fi ligature, long ſ, Cyrillic, Roman numerals, circled digits, superscripts, fractions, ㎡. Turkish İ/ı and accented Greek stay unmatched, which looks correct to me. Three edge cases where the fold has side effects. None are blockers, but the first is a wrong result rather than a missed match. 1) NFKC produces LIKE metacharacters. Ten codepoints fold into LIKE wildcards or the escape character: So searching for a literal ％ matches every row instead of none: Call sites that escape wildcards (e.g. 2) The BLOB contract differs from the builtin. The docstring says BLOBs pass through unchanged "like the builtin", but the builtin converts them to text: Either behaviour is defensible; the comment just does not match what SQLite does today. 3) Length-changing folds break single-character wildcards. Inherent to casefold expansion, probably acceptable — worth a line in the docstring. And a question rather than a finding: casefold makes SQLite more permissive than PostgreSQL. Happy to share the test script if useful. |
Before submitting, make sure you've checked and filled out the following:
Relates to #29102(also covers the still-reproducing prompt-tag case from closed issue: SQLite: Prompt tag filter fails for Cyrillic tags #23381).devbranch. PRs targetingmainwill be immediately closed.dev, and contains no unrelated commits.Changelog Entry
Description
What breaks, and for whom. On SQLite the dialect compiles
ilike()tolower(x) LIKE lower(?). SQLite's builtinlower()is a C function that folds only[A-Z]— every other codepoint passes through untouched. Case-insensitive search is therefore ASCII-only across 44ilike()sites in 13 model files (chats, notes, prompts, knowledge, users, groups, folders, files, skills, calendar, automations, messages, shared_chats) and 5 explicitfunc.lower()sites. User-visible symptom:ilike('%ŁĄKA%')returns 0 rows for data containingłąka— for anyone running content in Polish, Czech, Turkish, Cyrillic, Greek, or any accented Latin. PostgreSQL is unaffected (native Unicode-awareILIKE); SQLite is the default database, i.e. the default install is the broken one.Why the obvious fix is off the table. Overriding the builtin
lower()per connection would fix the operators, but it would silently corrupt the one place wherelower()is stored: migrationf0bd01a18a3dcreatesuq_user_email_lower, a UNIQUE expression index onlower(email). SQLite computes expression-index entries at write time with whatever function the writing connection has registered. Alembic builds its own connections (no app functions registered), so after an override the index would be born with builtin semantics while app inserts recompute entries with the override — split brain inside a uniqueness constraint. Every explicitfunc.lower()lookup (user email, folder names, prompt tags, model meta, group shares) would also silently change meaning. Conclusion: builtinlower()must stay bit-for-bit untouched, which rules out the one-line override and forces a dedicated function.The change.
unilower(x), registered per connection on all four SQLite paths: sync engine, async engine (aiosqlite), the SQLCipher creator, and the alembic migration engine. Semantics:NFKC(casefold(NFKC(x)))— the Unicode caseless algorithm — with an ASCII fast path (str.lower()is identical to casefold for every ASCII codepoint). Contract mirrors the builtin: NULL passes through, BLOBs pass through unchanged, numbers render as text (lower(123)='123'), so non-text inputs never behave differently.deterministic=Trueis set (SQLite ≥ 3.8.3, satisfied everywhere this project runs) with a fallback registration for exotic builds.str.lower(): lowercase alone produces wrong matches — Greek word-final sigma folds'ΣΑΣ'to'σας'(with ς) while mid-word'σασ'keeps σ, so differently-cased Greek text never matches; and casefold does not unify composed (NFC) and decomposed (NFD) spellings of e.g. Polishą/ę, which is exactly what text pasted from macOS/NFD sources looks like. Folding both the column and the pattern with the same algorithm makes'ΣΑΣ'↔'σασ'and NFC↔NFD match, plus fullwidth variants via NFKC.@compiles(ilike_case_insensitive, 'sqlite')reroutes only the case-folding operand of case-insensitive operators:ilike,not_ilike,icontains,not_icontains,istartswith,iendswithnow emitunilower(col) LIKE unilower(?). The blast radius is exactly the case-folding operand — plainlike(),contains(),startswith(),match(), and everyfunc.lower()call compile exactly as before. Other dialects are untouched: PostgreSQL keeps nativeILIKE, MySQL keeps its current lowering.@compileshook is process-global, so a future migration usingilike()must not hitno such function: unilower.Why stored state cannot diverge.
unilowerappears in no index, no generated column, no constraint, no trigger, no FTS table — it exists only at query time. A repo-wide audit found exactly one write-time consumer oflower(): the email expression index above, which this change deliberately does not touch. Builtinlower()is byte-for-byte unchanged (verified:lower('ŁĄKA'),lower(123),lower(NULL)identical before/after; duplicate-email insert still rejected byuq_user_email_lower). Alembic migrations contain noilike()and nounilower(). Consequences:create_all) and existing installs (schema via migrations), including the SQLCipher encrypted path.Blast radius — every surface this change touches.
ilike()sites across 13 model files (query-time matching only).unilower()in this PR: the prompt-tag filter (Prompts.search_prompts— the still-reproducing bug from closed issue: SQLite: Prompt tag filter fails for Cyrillic tags #23381), the SQLite message-content matcher (chat_search_message_content_match_sql), and the model meta-tag filter (Models.search_models). All compared Python-lowered values against builtinLOWER()and could never meet for non-ASCII.UnicodeLowerORM element: folder-name matching, and the prompt-tag fallback branch for dialects without JSON functions (renderslower()there — unchanged behavior). The user-email lookups stay on builtinfunc.lower().unilower. SQLite user-defined functions are pure query-evaluation callbacks and cannot modify the database at all — the worst possible outcome of this change is a different set of rows in a search result, never data corruption.Security & integrity review.
uniloweris a pure, deterministic, in-process string function — no I/O, no dynamic evaluation. The compile override emits bound-parameter placeholders (unilower(?)); user input is never interpolated into SQL, so the change introduces no injection path that did not already exist.LIKEwith a leading wildcard does a full scan before and after this change, so no query regresses from an index-behavior change.Measured, not assumed.
get_chats_by_user_id_and_search_text(incl.chat_search_termsASCII fallbacks),get_chat_list_by_user_id,Prompts.search_promptsandModels.search_modelswithfilter.tag(uppercase Polish and uppercase Greek tags — the issue: SQLite: Prompt tag filter fails for Cyrillic tags #23381 scenario plus the final-sigma parameter edge),Folders.get_folder_by_parent_id_and_user_id_and_namewith a differently-cased Polish name,Notes.search_notes,Knowledges.search_knowledge_bases,Users.get_users, and Polish message-content search through the JSON history path; sync + async engines; cross-user isolation.prompts.py/models.py(uppercase Greek tags could never match on PostgreSQL).LIKE '%..%'cannot use an index before or after): the ASCII query returns the identical 332 644 rows with both methods; the Polish query returns 583 385 rows vs 0 before. Cost profile: the pattern argument is constant-folded by SQLite (measured 1 000 001 callbacks per scan, not 2 000 000); overhead ≈ 1 µs/row Python callback → ~1.5 s vs ~0.35 s per million scanned rows. Production queries filteruser_idthrough an index first, so the scanned set is one user's rows (ms-level at realistic scales).PostgreSQL cross-verification. The full suite was re-run against a real, isolated PostgreSQL 16 instance (
en_US.UTF-8): nativeILIKEpreserved with nounilowerleakage,UnicodeLowercompiles byte-identically to the oldfunc.lower()rendering, every touched search path (chat search, content search, prompt tags, model meta tags, folder matching, notes, knowledge, users) verified through the real production functions, and the email-uniqueness index still rejects duplicates. This review also surfaced a pre-existing upstream PG bug:chat_search_message_content_match_sqland the chat-tag filter applied JSON operators (#>,->) directly toJSONFieldcolumns, which are TEXT-backed — every content search with a query on PostgreSQL errored withoperator does not exist: text -> unknown. Fixed here with explicit::jsoncasts (no-op semantics for native JSON columns, required for the TEXT-backed storage).Deliberately not addressed here: the 5
func.lower()sites (frozen by the email expression index until that index is rebuilt differently; the SQLite prompt-tag and content-search paths listed above are now covered instead), and the Python-side content filter — these need a per-dialect rewrite or a full-text index; for very large deployments the structural answer is an FTS5 virtual table with thetrigramtokenizer (SQLite ≥ 3.34, the only variant that serves substring search from an index), which is a schema-level change I am happy to spec separately. The first-time-contributor checkbox below is intentionally left unticked — your call whether this lands.Added
Changed
Deprecated
Removed
Fixed
ilike()-based searches (issue: SQLite — every ILIKE search is case-sensitive for non-ASCII text (Cyrillic, Greek, accented Latin) #29102).operator does not exist: text -> unknown(JSON operators were applied to TEXT-backedJSONFieldcolumns).Security
Breaking Changes
Additional Information
fix/sqlite-unicode-ilike, rebased on the latestdev; backend-only (the frontend workflow is skipped forbackend/**paths).memoriesandchannelshave no SQL text-search path and were audited as unaffected.Screenshots or Videos
Contributor License Agreement
Note
Deleting the CLA section will lead to immediate closure of your PR and it will not be merged in.