48 lines
2.4 KiB
Markdown
48 lines
2.4 KiB
Markdown
 
|
# SQLite source history: bm25() sign-convention change
|
||
|
|
|
||
|
|
## Metadata
|
||
|
|
|
||
|
|
| Field | Value |
|
||
|
|
|---|---|
|
||
|
|
| Institute | Molodetz Institute — Research & Retrieval Division |
|
||
|
|
| Subject | agent-memory-recency-tracking |
|
||
|
|
| Document type | Source Dossier |
|
||
|
|
| Tier | 1 |
|
||
|
|
| Source title | SQLite source/commit history entry concerning the bm25() auxiliary function |
|
||
|
|
| Source URL | https://sqlite.org/src/info/3ee7b5a9f987c269 |
|
||
|
|
| Source publisher / author | SQLite project (official source repository) |
|
||
|
|
| Source type | other (source control history entry) |
|
||
|
|
| Source date (published) | Unknown — not stated (historical commit; exact date not captured by this research pass) |
|
||
|
|
| Date researched | 2026-10-09 |
|
||
|
|
| Researched by | tai (agent), model: Claude Sonnet 5, on behalf of retoor |
|
||
|
|
| Research method | web search synthesis |
|
||
|
|
| Confidence | Medium |
|
||
|
|
| Status | Active |
|
||
|
|
|
||
|
|
## Summary
|
||
|
|
|
||
|
|
A SQLite source-history record documenting that the `bm25()` FTS5 auxiliary ranking function's result is multiplied by -1, so that a *lower* (more negative) score means a *better* match — meaning correct usage requires `ORDER BY rank` ascending, not descending, which is the opposite of the intuitive "higher score = better" assumption.
|
||
|
|
|
||
|
|
## Key findings
|
||
|
|
|
||
|
|
- `bm25()` scores are inverted relative to naive expectation: better matches produce lower (more negative) numbers.
|
||
|
|
- Consequently, ranking queries must sort ascending (`ORDER BY rank` with no `DESC`) to get best-matches-first, not descending.
|
||
|
|
|
||
|
|
## Pros / strengths
|
||
|
|
|
||
|
|
- Primary-source (SQLite's own project history) confirmation of a detail that is easy to get backwards and silently misrank results if assumed rather than checked.
|
||
|
|
|
||
|
|
## Cons / limitations / caveats
|
||
|
|
|
||
|
|
- This is a narrow, mechanical fact about FTS5's API, not itself evidence about recency tracking or write amplification — it's included here because it surfaced in the same research query about combining BM25 with recency-aware ranking, and is directly relevant to *correctly implementing* any future phase that blends `last_used` into search ranking.
|
||
|
|
|
||
|
|
## Relevance to tai
|
||
|
|
|
||
|
|
Tai's existing `fts_search()` code already sorts by `bm25(fts_docs)` ascending (confirmed independently in the codebase, not from this source) — this dossier exists so that if/when a future phase blends a recency signal into ranking, whoever implements it does not accidentally assume "higher bm25 score is better" and invert the ranking.
|
||
|
|
|
||
|
|
## Changelog
|
||
|
|
|
||
|
|
| Date | Change |
|
||
|
|
|---|---|
|
||
|
|
| 2026-10-09 | Initial dossier created. |
|