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
Owner

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-translated v.ToLower().StartsWith(qLower), which becomes SQL LOWER(). SQLite's LOWER() is ASCII-only.

So q=é does not match a stored Édith, and q=É 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

  • The fix must not regress to culture-dependent comparison. #578 established OrdinalIgnoreCase / ToLowerInvariant as load-bearing: an exhaustive sweep found 0 non-ASCII codepoints that OrdinalIgnoreCase-equal a printable ASCII character, versus 190 under InvariantCultureIgnoreCase (U+00AA→a, U+017F→s, the modifier letters). Read api.search-field-values-sources before touching the comparison.
  • MySQL's 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

  • Accented values are suggested for the EF-sourced fields on both providers (a stored Édith is reachable from q=é and from q=É) — SQLite via the new etv_upper fold (red-first test); MySQL already reached it, verified on a live 8.4
  • The fix does not introduce an unbounded scan or in-memory materialization of these tables — the added query is a bounded LIMITed prefix query of the same kind as the existing one, not a walk; no row budget, no accepted loss
  • Comparison stays culture-independent — no InvariantCultureIgnoreCase, no current-culture ToLower()/StartsWith; the fold is ToUpperInvariant and the final filter stays OrdinalIgnoreCase
  • Provider divergence is covered — with one honest caveat. SQLite: Ef_Sourced_Stored_Uppercase_Accent_Is_Reachable fails (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 live mysql: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-sources updated — 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 it
  • Adversarial review passed — five independent cold rounds, each re-reviewing the previous fix commit, ending MERGEABLE. No under-match, SQL, injection or lifecycle defect was ever found; every finding was a false or overstated claim in prose. Deviation: same-model-family, not cross-family (Codex quota exhausted); mitigated with cold review-only briefs in isolated worktrees

Context: #578, #434.

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-translated `v.ToLower().StartsWith(qLower)`, which becomes SQL `LOWER()`. **SQLite's `LOWER()` is ASCII-only.** So `q=é` does not match a stored `Édith`, and `q=É` 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 - The fix must not regress to culture-dependent comparison. #578 established `OrdinalIgnoreCase` / `ToLowerInvariant` as **load-bearing**: an exhaustive sweep found **0** non-ASCII codepoints that `OrdinalIgnoreCase`-equal a printable ASCII character, versus **190** under `InvariantCultureIgnoreCase` (`U+00AA`→`a`, `U+017F`→`s`, the modifier letters). Read `api.search-field-values-sources` before touching the comparison. - MySQL's `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 - [x] Accented values are suggested for the EF-sourced fields on **both** providers (a stored `Édith` is reachable from `q=é` and from `q=É`) — SQLite via the new `etv_upper` fold (red-first test); MySQL already reached it, verified on a live 8.4 - [x] The fix does not introduce an unbounded scan or in-memory materialization of these tables — the added query is a bounded `LIMIT`ed prefix query of the same kind as the existing one, not a walk; no row budget, no accepted loss - [x] Comparison stays culture-independent — no `InvariantCultureIgnoreCase`, no current-culture `ToLower()`/`StartsWith`; the fold is `ToUpperInvariant` and the final filter stays `OrdinalIgnoreCase` - [x] Provider divergence is covered — **with one honest caveat**. SQLite: `Ef_Sourced_Stored_Uppercase_Accent_Is_Reachable` fails (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 live `mysql: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. - [x] `api.search-field-values-sources` updated — 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 it - [x] Adversarial review passed — five independent cold rounds, each re-reviewing the previous fix commit, ending MERGEABLE. No under-match, SQL, injection or lifecycle defect was ever found; every finding was a false or overstated claim in prose. Deviation: same-model-family, not cross-family (Codex quota exhausted); mitigated with cold review-only briefs in isolated worktrees Context: #578, #434.
timothy added the bugapipriority: medium labels 2026-07-26 23:54:14 +02:00
timothy added the in-progress label 2026-07-27 19:45:10 +02:00
Author
Owner

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:

  • Open PRs referencing #668: none (only the three open Renovate dependency PRs #679/#680/#681)
  • Remote branches naming 668: none (git ls-remote --heads origin '*668*' empty)
  • Comments predating the label: none (0 comments)
  • Fresh git fetch origin main — base is aa1f504e0

If another session is already on this, say so here and I'll drop it.

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: - Open PRs referencing #668: none (only the three open Renovate dependency PRs #679/#680/#681) - Remote branches naming 668: none (`git ls-remote --heads origin '*668*'` empty) - Comments predating the label: none (0 comments) - Fresh `git fetch origin main` — base is `aa1f504e0` If another session is already on this, say so here and I'll drop it.
Author
Owner

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 when isSqlite && q contains a non-ASCII character, using a registered etv_upper (ToUpperInvariant) SQLite scalar via Dapper; every other case — all-ASCII q, and MySQL for all q — 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 predicate LOWER(v) LIKE 'q%' under-matched. An under-match is unrecoverable: the endpoint's in-memory OrdinalIgnoreCase filter 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:

  1. The defect was one-sided. The handler lowercases q with ToLowerInvariant before SQL, so both casings produce one pattern. A stored lowercase édith was always reachable from both q=é and q=É. This issue's body claimed q=É 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.)
  2. MySQL was never broken. This was a SQLite defect, not the symmetric provider-divergent one described here. MySQL's LOWER() is Unicode-aware and reaches the value unaided.

Decisions/conventions changed: Added api.search-field-values-unicode-fold, which narrows api.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 (hence Decisions-Edit: yes). Catalog regenerated. New provider static TvContext.RegisterUnicodeCaseFunctions, wired in both composition roots per ProviderStaticsWiringTests.

Reusable knowledge:

  • Probe the query the CODE runs, not one you type. A hand-typed SQL probe uses a literal where the code binds a parameter, and the collations differ. Measured on live MySQL 8.4: LOWER(name) LIKE 'é%' with a literal matches Édith AND Edith (accent-insensitive), but the executed EF query returns Édith alone, because MySqlConnector fixes the connection collation to utf8mb4_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.
  • OrdinalIgnoreCase is NOT "invariant-uppercase then ordinal." char.ToUpperInvariant('ſ') is 'S', yet "ſweet".StartsWith("S", OrdinalIgnoreCase) is false. The fold is safe because OrdinalIgnoreCase equality is a strict subset of invariant-uppercase equality — containment, not identity.
  • A non-trailer line voids the whole trailer block. Refs #668 has no colon, so it isn't a trailer, and its presence silently killed a valid Decisions-Edit: yes on the next line — a real decisions lifecycle red that looked like the known flake. Verify with git log -1 --format='%(trailers:key=…,valueonly,unfold)', not by eye.

Verification: Full ErsatzTV.Tests 1953 passed / 0 skipped with the live MySQL lane armed (1949/4-skipped without it); ErsatzTV.Architecture.Tests 7/7; scripts/tests 338; decisions_validate OK; format gate clean; no BOMs. CI green on f2d9c0dc8 with Build & test at 22m and Functional E2E at 17m — real runs, not fast paths. Guards were mutation-tested rather than asserted: removing the %/_ escaping fails Unicode_Fold_Escaping_Prevents_Limit_Crowding; removing the ordinal filter fails Unicode_Fold_Over_Match_Is_Discarded_By_The_Ordinal_Filter while the eight agreement cases stay green. New SearchFieldValuesProviderTests executed against a real mysql: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:

  • #688 — the decisions_validate ceiling 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 blocking script-tests job. This record was trimmed to exactly 60 rather than re-derive a corpus-wide constant inside a bugfix PR.
  • #689 — LibraryFolderDedupeMigrationTests on main claims "CI sets ETV_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.
  • The new MySQL lane skips in CI — arming it is #627's job, deliberately not re-armed here (it was removed as non-deterministic, and an intermittently-red gate is worse than none).

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.

## 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 when `isSqlite && q contains a non-ASCII character`, using a registered `etv_upper` (`ToUpperInvariant`) SQLite scalar via Dapper; every other case — all-ASCII `q`, and MySQL for all `q` — 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 predicate `LOWER(v) LIKE 'q%'` **under-matched**. An under-match is unrecoverable: the endpoint's in-memory `OrdinalIgnoreCase` filter 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: 1. **The defect was one-sided.** The handler lowercases `q` with `ToLowerInvariant` *before* SQL, so both casings produce one pattern. A stored **lowercase** `édith` was always reachable from both `q=é` and `q=É`. This issue's body claimed `q=É` 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.) 2. **MySQL was never broken.** This was a SQLite defect, not the symmetric provider-divergent one described here. MySQL's `LOWER()` is Unicode-aware and reaches the value unaided. **Decisions/conventions changed:** Added `api.search-field-values-unicode-fold`, which **narrows** `api.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 (hence `Decisions-Edit: yes`). Catalog regenerated. New provider static `TvContext.RegisterUnicodeCaseFunctions`, wired in **both** composition roots per `ProviderStaticsWiringTests`. **Reusable knowledge:** - **Probe the query the CODE runs, not one you type.** A hand-typed SQL probe uses a *literal* where the code binds a *parameter*, and the collations differ. Measured on live MySQL 8.4: `LOWER(name) LIKE 'é%'` with a **literal** matches `Édith` AND `Edith` (accent-insensitive), but the **executed EF query returns `Édith` alone**, because MySqlConnector fixes the connection collation to `utf8mb4_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. - **`OrdinalIgnoreCase` is NOT "invariant-uppercase then ordinal."** `char.ToUpperInvariant('ſ')` is `'S'`, yet `"ſweet".StartsWith("S", OrdinalIgnoreCase)` is **false**. The fold is safe because `OrdinalIgnoreCase` equality is a strict *subset* of invariant-uppercase equality — containment, not identity. - **A non-trailer line voids the whole trailer block.** `Refs #668` has no colon, so it isn't a trailer, and its presence silently killed a valid `Decisions-Edit: yes` on the next line — a real `decisions lifecycle` red that looked like the known flake. Verify with `git log -1 --format='%(trailers:key=…,valueonly,unfold)'`, not by eye. **Verification:** Full `ErsatzTV.Tests` **1953 passed / 0 skipped** with the live MySQL lane armed (1949/4-skipped without it); `ErsatzTV.Architecture.Tests` 7/7; `scripts/tests` 338; `decisions_validate` OK; format gate clean; no BOMs. CI green on `f2d9c0dc8` with `Build & test` at **22m** and Functional E2E at **17m** — real runs, not fast paths. Guards were **mutation-tested rather than asserted**: removing the `%`/`_` escaping fails `Unicode_Fold_Escaping_Prevents_Limit_Crowding`; removing the ordinal filter fails `Unicode_Fold_Over_Match_Is_Discarded_By_The_Ordinal_Filter` while the eight agreement cases stay green. New `SearchFieldValuesProviderTests` executed against a real `mysql: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:** - **#688** — the `decisions_validate` ceiling 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 blocking `script-tests` job. This record was trimmed to exactly 60 rather than re-derive a corpus-wide constant inside a bugfix PR. - **#689** — `LibraryFolderDedupeMigrationTests` on `main` claims "CI sets `ETV_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. - The new MySQL lane **skips in CI** — arming it is #627's job, deliberately not re-armed here (it was removed as non-deterministic, and an intermittently-red gate is worse than none). **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.
timothy removed the in-progress label 2026-07-27 23:09:33 +02:00
Sign in to join this conversation.
1 Participants
Notifications
Due Date
No due date set.
Dependencies

No dependencies set.

Reference: timothy/ersatztv#668