Production engineering
Reveal answer and reasoning
Expression and partial indexes encode a particular expression or row predicate, which is more specific than saying an index exists on a nearby concept. The fixture indexes lower(name), and separately indexes price only for rows with active equal to one. Queries must satisfy the provider's matching requirements before those structures are candidates. A collation comparison can return similar ASCII matches without being the same indexed expression.
The external lab exposes actual SQLite plans for these cases; the companion checks only logical data selection. Neither program claims that optimizer behavior or text semantics are identical across databases and cultures.
Logical similarity does not promise expression-index matching, and a partial index cannot stand in for rows its predicate excludes. Rewriting an expression, changing collation or introducing a parameter can require a new provider observation. The fixture also has nullable and dynamically typed SQL behavior elsewhere; an application-normalized value is not automatically an enforced storage contract. Avoid converting a case-insensitive C# test into a claim about all SQLite Unicode text.
Keep result semantics and access-path investigation independent so a change meant to improve an index does not silently change which records are considered equal or active. The partial index is defined with active=1, while the companion uses a Boolean property; that correspondence is deliberately specified for these records rather than inferred for arbitrary storage values.
What a strong answer covers
- Known ASCII normalization selects1,2,5; active price order is2,4,1.
- Expression and partial indexes have exact provider matching and row-coverage contracts.
- Observed plan use does not establish cross-engine Unicode semantics or a speed ranking.
Common pitfall
Assuming logically similar text predicates always match the same expression index.
Follow-up questions
- Which Unicode fixtures would challenge the assumed equality rule?
- What query includes rows intentionally absent from the active-only index?
Self-review
Choose what to revisit. These marks are your own assessment and are separate from lesson completion.