Facet-value typeahead misses accented values on the EF-sourced fields (LOWER() is ASCII-only on SQLite)
#668
Closed
opened 2026-07-26 23:54:14 +02:00 by timothy
·
2 comments
No Branch/Tag Specified
main
renovate/dotnet-monorepo
renovate/meziantou.analyzer-3.x
renovate/lucene.net
renovate/sqlitepclraw.bundle_e_sqlite3-3.x
renovate/cliwrap-3.x
913-scripted-playout-401
release/v26.15.0-notes
issue-806-guard-populations
scratch/767b-poisoned
scratch/767b-control
release/v26.14.0-notes
release/v26.14.0
docs/510-skill-logo-bug-policy
fix/510-watermark-resolution-policy
fix/629-verdict-classifier-falseopens
fix/609-decisions-edit-token-scope
issue-135-clear-to-none
release/v26.12.0-notes
fix/409b-lastscan-api-parity
fix/401-updatechannel-mirror-422
fix/327-playlist-rename-validation
fix/410-scancancel-log-level
fix/409-447-librariesscreen-neverscanned
fix/338-zap-exit-code
fix/367-plex-budget-message
fix/310-debom-legacy-cs
ci/604-lane-rebalance
feat/388-design-mirror
feat/247-test-ownership
feat/247-primary-action
feat/357-player-owned-playback
feat/357-jellyfin-plugin-poc
fix/289-mcp-hardening
issue58-mcp
feat/244-channels-extract
ci/auto-bump-prod-compose
feat/multi-rerun-collections-api
feat/collections-api
feat/quick-wins
feat/185-docs-part2
feat/140-collections-screen
feat/146-channel-edit
feat/147-classic-ui-link
issue22-renovate-dashboard
feat/91-cutover
feat/63-composite-create
feat/65-library-browse
feat/85-epg
feat/86-schedule-editor
feat/109-dashboard-data
feat/99-session-tracking
fix/dockerfile-node-tag
feat/59-spa-foundation
docs/59-ui-redesign-brief
feat/102-json-guide
feat/111-schedule-durations
feat/104-artwork-upload
feat/103-media-sources-api
feat/playouts-read-api
feat/108-health-api
feat/105-picker-list-endpoints
issue-97-channel-state-api
issue42-jellyfin-musicvideos
issue46-rest-api-error-contract
dependabot/nuget/ErsatzTV.FFmpeg.Tests/multi-d307a2e06f
qsv-improvements
hdr-vulkan-cuda-test
v26.16.0
v26.15.0
v26.14.0
v26.13.0
v26.12.0
v26.11.0
v26.10.0
v26.9.0
v26.8.0
v26.7.0
blazor-final
v26.6.0
v26.5.0
v26.4.0
v26.3.1
v26.3.0
v26.2.0
v26.1.1
v26.1.0
v25.9.0
v25.8.0
v25.7.1
v25.7.0
v25.6.0
v25.5.0
v25.4.0
v25.3.1
v25.3.0
v25.2.0
v25.1.0
v0.8.8-beta
v0.8.7-beta
v0.8.6-beta
v0.8.5-beta
v0.8.4-beta
v0.8.3-beta
v0.8.2-beta
v0.8.1-beta
v0.8.0-beta
v0.7.9-beta
v0.7.8-beta
v0.7.7-beta
v0.7.6-beta
v0.7.5-beta
v0.7.4-beta
v0.7.3-beta
v0.7.2-beta
v0.7.1-beta
v0.7.0-beta
v0.6.9-beta
v0.6.8-beta
v0.6.7-beta
v0.6.6-beta
v0.6.5-beta
v0.6.4-beta
v0.6.3-beta
v0.6.2-beta
v0.6.1-beta
v0.6.0-beta
v0.5.8-beta
v0.5.7-beta
v0.5.6-beta
v0.5.5-beta
v0.5.4-beta
v0.5.3-beta
v0.5.2-beta
v0.5.1-beta
v0.5.0-beta
v0.4.5-alpha
v0.4.4-alpha
v0.4.3-alpha
v0.4.2-alpha
v0.4.1-alpha
v0.4.0-alpha
v0.3.8-alpha
v0.3.7-alpha
develop
v0.3.6-alpha
v0.3.5-alpha
v0.3.4-alpha
v0.3.3-alpha
v0.3.2-alpha
v0.3.1-alpha
v0.3.0-alpha
v0.2.5-alpha
v0.2.4-alpha
v0.2.3-alpha
v0.2.2-alpha
v0.2.1-alpha
v0.2.0-alpha
v0.1.5-alpha
v0.1.4-alpha
v0.1.3-alpha
v0.1.2-alpha
v0.1.1-alpha
v0.1.0-alpha
v0.0.62-alpha
v0.0.61-alpha
v0.0.60-alpha
v0.0.59-alpha
v0.0.58-alpha
v0.0.57-alpha
v0.0.56-alpha
v0.0.55-alpha
v0.0.54-alpha
v0.0.53-alpha
v0.0.52-alpha
v0.0.51-alpha
v0.0.50-alpha
v0.0.49-prealpha
v0.0.48-prealpha
v0.0.47-prealpha
v0.0.46-prealpha
v0.0.45-prealpha
v0.0.44-prealpha
v0.0.43-prealpha
v0.0.42-prealpha
v0.0.41-prealpha
v0.0.40-prealpha
v0.0.39-prealpha
v0.0.38-prealpha
v0.0.37-prealpha
v0.0.36-prealpha
v0.0.35-prealpha
v0.0.34-prealpha
v0.0.33-prealpha
v0.0.32-prealpha
v0.0.31-prealpha
v0.0.30-prealpha
v0.0.29-prealpha
v0.0.28-prealpha
v0.0.27-prealpha
v0.0.26-prealpha
v0.0.25-prealpha
v0.0.24-prealpha
v0.0.23-prealpha
v0.0.22-prealpha
v0.0.21-prealpha
v0.0.20-prealpha
v0.0.19-prealpha
v0.0.18-prealpha
v0.0.17-prealpha
v0.0.16-prealpha
v0.0.15-prealpha
v0.0.14-prealpha
v0.0.13-prealpha
v0.0.12-prealpha
v0.0.11-prealpha
v0.0.10-prealpha
v0.0.9-prealpha
v0.0.8-prealpha
v0.0.7-prealpha
v0.0.6-prealpha
v0.0.5-prealpha
v0.0.4-prealpha
v0.0.3-prealpha
v0.0.2-prealpha
v0.0.1-prealpha
Labels
Clear labels
ad-hoc
api
bug
ci-cd
content
dependencies
enhancement
frontend
in-progress
jellyfin
parked
priority: high
priority: low
priority: medium
review
security
One-off / ad-hoc work not tracked by a dedicated issue
REST API / HTTP endpoints
Something isn't working
Build, test, deploy pipeline
Channel content / schedules / playlists
Dependency updates (Renovate)
New feature or improvement
ChicoryTV React SPA frontend
Claimed by an active session — do not pick up
Jellyfin tuner / IPTV integration
Excluded from automatic queue pickup; work only when explicitly selected
Adversarial review finding
Security / vulnerability fix
Milestone
No items
No Milestone
Projects
Clear projects
No projects
No Assignees
Notifications
Due Date
No due date set.
Dependencies
No dependencies set.
Reference: timothy/ersatztv#668
Reference in New Issue
Block a user
Blocking a user prevents them from interacting with repositories, such as opening or commenting on pull requests or issues. Learn more about blocking a user.
Found during #578's cold review and called out explicitly by the implementer rather than left implicit. Predates #578 — the list-valued fix landed there does not cover this.
The gap
GetSearchFieldValuesHandler's EF-sourced fields —genre,studio,director,writer,actor,tag,artist's entity half, etc. — prefix-match through EF-translatedv.ToLower().StartsWith(qLower), which becomes SQLLOWER(). SQLite'sLOWER()is ASCII-only.So
q=édoes not match a storedÉdith, andq=Édoes not matchédith, for every one of those fields. In a music or European film library, accented values are common, not exotic.Why #578 didn't fix it
#578 fixed the list-valued columns (
SongMetadata.Artists/AlbumArtists) by making the SQL a deliberate superset and doing the exact, culture-independent prefix match in memory (api.search-field-values-sources). The EF-sourced fields never went through that path — they filter server-side, so the ASCII-only fold is the final word rather than a pre-filter.Fixing these means either client-side filtering of those tables (which reintroduces the unbounded-scan question #578 worked hard to bound) or a provider-aware collation strategy. That is a real design choice, which is why it was scoped out rather than bolted on.
Notes for whoever picks this up
OrdinalIgnoreCase/ToLowerInvariantas load-bearing: an exhaustive sweep found 0 non-ASCII codepoints thatOrdinalIgnoreCase-equal a printable ASCII character, versus 190 underInvariantCultureIgnoreCase(U+00AA→a,U+017F→s, the modifier letters). Readapi.search-field-values-sourcesbefore touching the comparison.LOWER()is Unicode-aware, so this is a provider-divergent defect: the same query behaves differently on SQLite and MySQL. Any test that runs only on SQLite structurally cannot see the MySQL side, and vice versa.Done-when
Édithis reachable fromq=éand fromq=É) — SQLite via the newetv_upperfold (red-first test); MySQL already reached it, verified on a live 8.4LIMITed prefix query of the same kind as the existing one, not a walk; no row budget, no accepted lossInvariantCultureIgnoreCase, no current-cultureToLower()/StartsWith; the fold isToUpperInvariantand the final filter staysOrdinalIgnoreCaseEf_Sourced_Stored_Uppercase_Accent_Is_Reachablefails (returns[]) if the ASCII-only fold returns. MySQL: query shape pinned by the existing dual-provider test, and a new executed lane (SearchFieldValuesProviderTests) run against a livemysql:8.4— 4 passed / 0 skipped, plus the skip and hard-fail paths exercised. Caveat: CI does not arm that lane, so it skips there; running MySQL fixtures against the live service was removed as non-deterministic (#627). Re-arming is that issue's job.api.search-field-values-sourcesupdated — its two now-false claims about #668 being open were corrected in place, and the resolved end state is recorded in a new record,api.search-field-values-unicode-fold, which narrows rather than supersedes itContext: #578, #434.
Claiming this — session started 2026-07-27, selected as the top-ranked eligible backlog issue by
scripts/select-queue.sh.Pre-claim checks (
process.parallel-session-claim), all clear at claim time:git ls-remote --heads origin '*668*'empty)git fetch origin main— base isaa1f504e0If another session is already on this, say so here and I'll drop it.
Closing record
Outcome: Shipped in #687 (merged
d4ea1584c). The EF-sourced facet fields now reach stored values whose prefix carries an uppercase non-ASCII character, on both providers, with no row budget and no accepted loss. Implementation: a second, additive query taken only whenisSqlite && q contains a non-ASCII character, using a registeredetv_upper(ToUpperInvariant) SQLite scalar via Dapper; every other case — all-ASCIIq, and MySQL for allq— runs the previous EF query unchanged. No migration, no model change, both provider snapshots untouched.Root cause: SQLite's
LOWER()folds ASCII only —lower('Édith')returns'Édith'unchanged — so the predicateLOWER(v) LIKE 'q%'under-matched. An under-match is unrecoverable: the endpoint's in-memoryOrdinalIgnoreCasefilter runs afterwards and can only remove rows, never reintroduce one SQL never returned.Two corrections to this issue's own framing, both measured rather than argued:
qwithToLowerInvariantbefore SQL, so both casings produce one pattern. A stored lowercaseédithwas always reachable from bothq=éandq=É. This issue's body claimedq=Éfailed againstédith— false; a test now pins that passing case beside the fixed one. (The Done-when box was stated correctly regardless, so scope was unaffected.)LOWER()is Unicode-aware and reaches the value unaided.Decisions/conventions changed: Added
api.search-field-values-unicode-fold, which narrowsapi.search-field-values-sources(#578) rather than superseding it — that record's "Known limitation inherited, not introduced" clause and its "do not describe it as fixed here" scoping note were the two now-false statements, corrected in place (henceDecisions-Edit: yes). Catalog regenerated. New provider staticTvContext.RegisterUnicodeCaseFunctions, wired in both composition roots perProviderStaticsWiringTests.Reusable knowledge:
LOWER(name) LIKE 'é%'with a literal matchesÉdithANDEdith(accent-insensitive), but the executed EF query returnsÉdithalone, because MySqlConnector fixes the connection collation toutf8mb4_bin. I wrote the literal-probe result into a handler comment, a decision record and the PR body before a mutation test exposed it.ToQueryString()does not help — it inlines parameters as literals, hiding exactly the variable that matters.OrdinalIgnoreCaseis NOT "invariant-uppercase then ordinal."char.ToUpperInvariant('ſ')is'S', yet"ſweet".StartsWith("S", OrdinalIgnoreCase)is false. The fold is safe becauseOrdinalIgnoreCaseequality is a strict subset of invariant-uppercase equality — containment, not identity.Refs #668has no colon, so it isn't a trailer, and its presence silently killed a validDecisions-Edit: yeson the next line — a realdecisions lifecyclered that looked like the known flake. Verify withgit log -1 --format='%(trailers:key=…,valueonly,unfold)', not by eye.Verification: Full
ErsatzTV.Tests1953 passed / 0 skipped with the live MySQL lane armed (1949/4-skipped without it);ErsatzTV.Architecture.Tests7/7;scripts/tests338;decisions_validateOK; format gate clean; no BOMs. CI green onf2d9c0dc8withBuild & testat 22m and Functional E2E at 17m — real runs, not fast paths. Guards were mutation-tested rather than asserted: removing the%/_escaping failsUnicode_Fold_Escaping_Prevents_Limit_Crowding; removing the ordinal filter failsUnicode_Fold_Over_Match_Is_Discarded_By_The_Ordinal_Filterwhile the eight agreement cases stay green. NewSearchFieldValuesProviderTestsexecuted against a realmysql:8.4(4 passed / 0 skipped), with its skip and hard-fail paths both exercised. Five independent cold review rounds, each re-reviewing the previous fix commit, ending MERGEABLE.Deferred:
decisions_validateceiling calibration test sits one line from red (main's p90 is 59 against a ceiling of 60), so any record ≥61 prose lines fails the blockingscript-testsjob. This record was trimmed to exactly 60 rather than re-derive a corpus-wide constant inside a bugfix PR.LibraryFolderDedupeMigrationTestsonmainclaims "CI setsETV_REQUIRE_MYSQL_TESTS=1"; nothing does. I copied that sentence into the new fixture and a review blocked on it; the copy is fixed, the original is not.Docs updated:
docs/decisions/records/api/search-field-values-unicode-fold.md(new),docs/decisions/records/api/search-field-values-sources.md(two false claims corrected),docs/decisions/README.md(regenerated catalog). No/api/*contract change, so no OpenAPI regeneration.Process note worth recording: across five review rounds, no correctness defect was ever found in the code — it has been right since the first implementation commit. All six defects were false or overstated claims in prose: a backwards
ſjustification, two guards that could not fail if what they named was deleted, a stale parenthetical, a copied false CI claim, and the MySQL over-match mechanism above (which recurred in a sixth place, my own follow-up issue). What caught them was mutation testing and grepping by subject; re-reading had already passed over every one.