Build a lexical retriever that asks SQLite to rank the versioned Python documentation chunks. Inspect the native scores and the term counts behind them, without implementing another ranking engine.
When you need this. Use it when exact API words should retrieve matching passages and you need to explain why one lexical match ranks ahead of another.
Prerequisites. Use the versioned corpus from the preceding module. The vector course supplies the embedding and vector-search prerequisites. The lab needs SQLite FTS5 and no third-party package.
Inspect the words the index actually stores
Lexical retrieval searches indexed terms. This lab loads the unchanged snapshot from the corpus-maintenance module into an FTS5 table with the unicode61 tokenizer. The source ID, chunk ID, version and URL stay attached as unindexed metadata; only the passage text contributes terms.
A displayed API name is not necessarily one indexed term. The native vocabulary table shows how SQLite tokenized punctuation and underscores in these summaries. Inspect that table before assuming that the query spelling write_text is a single lookup key.
This lab deliberately limits its query parser to 16 distinct ASCII letter-or-digit terms within 256 characters; these are application limits, not SQLite FTS5 limits. Spaces and underscores separate terms, so write_text searches for write or text. Each term is quoted separately and joined with OR through an SQL parameter. A multiword query can match any term; it does not require an exact phrase.
Unsupported query syntax. Punctuation outside that input policy raises ValueError. A reserved word such as OR is searched as a literal term, not treated as a caller-supplied operator. SQL parameterization and FTS query syntax are separate boundaries.
Read native BM25 scores in the right direction
BM25 ranks lexical matches using term frequency, document length and how many documents contain a term. Repeating a term has diminishing benefit; longer text changes the length adjustment. SQLite’s implementation uses fixed k1 and b settings recorded by the program, and applies its documented floor to the inverse-document-frequency component.
The result carries the native bm25() value. In SQLite, a smaller value sorts earlier. The adapter orders by that score and then by chunk ID to make exact ties stable. Do not reverse this ordering because another library presents BM25 as a positive higher-is-better score.
The driver queries real indexed terms such as text, newline and overwritten. The recorded rows expose each match’s term frequency, document length, average length and document frequency. For multiword queries, SQLite adds the term contributions; the response omits the single-term diagnostic fields. The saved records below remain the original single-term runs. A separate single-term explanation reconstructs the native result from those observed values as a diagnostic check; SQLite still produces the returned score and ranking.
Read the exact saved scores below. They describe this tokenizer, corpus and query; they are not probabilities of relevance.
Preserve explicit empty and no-match results
Empty query. Whitespace input returns empty_query and no results. A valid literal absent from the index returns no_match. Neither state asks a model to invent a passage.
A lexical match can still miss the intended operation or documentation version. The next lessons add a vector retriever and consistent metadata filters. They do not replace the existing vector retrieval course, which covers embeddings and vector-store internals.
Execute the local retrieval contract
Download the complete lab. It includes the README, fixtures, implementation, tests, dependency information and actual run record.
"""Native SQLite FTS5 BM25 over a validated local documentation snapshot."""
from collections import Counter
import math
import re
import sqlite3
from version_documents import validate_snapshot
class LexicalIndex:
def __init__(self, snapshot):
validate_snapshot(snapshot)
self.connection = sqlite3.connect(':memory:')
self.connection.row_factory = sqlite3.Row
try:
self.connection.execute("CREATE VIRTUAL TABLE passages USING fts5(text,chunk_id UNINDEXED,source_id UNINDEXED,version UNINDEXED,url UNINDEXED, tokenize='unicode61')")
for document in snapshot['documents']:
for chunk in document['chunks']:
self.connection.execute('INSERT INTO passages(text,chunk_id,source_id,version,url) VALUES(?,?,?,?,?)', (chunk['text'],chunk['chunk_id'],document['source_id'],document['version'],document['url']))
self.connection.execute("CREATE VIRTUAL TABLE vocabulary USING fts5vocab(passages,'instance')")
self.connection.execute("CREATE VIRTUAL TABLE terms USING fts5vocab(passages,'row')")
except sqlite3.OperationalError:
self.connection.close()
raise
self.snapshot_id=snapshot['snapshot_id']
def close(self):
self.connection.close()
def indexed_terms(self):
return [dict(row) for row in self.connection.execute('SELECT term,doc AS document_frequency,cnt AS occurrences FROM terms ORDER BY term')]
def search(self, query, limit=10):
if not isinstance(query,str) or not isinstance(limit,int) or isinstance(limit,bool) or not 1<=limit<=100:
raise ValueError('invalid_query')
query=query.strip().lower()
if not query:
return {'query':query,'status':'empty_query','results':[]}
# Bounded literal words; spaces and underscores separate terms, never operators.
if len(query)>256 or not re.fullmatch(r'[a-z0-9_ ]+',query):
raise ValueError('invalid_query')
tokens=list(dict.fromkeys(re.findall(r'[a-z0-9]+',query)))
if not tokens or len(tokens)>16 or any(len(term)>64 for term in tokens):
raise ValueError('invalid_query')
if len(tokens)>1:
return self._search_terms(query,tokens,limit)
query=tokens[0]
lengths={row['rowid']:0 for row in self.connection.execute('SELECT rowid FROM passages')}
frequencies=Counter()
for row in self.connection.execute('SELECT term,doc FROM vocabulary'):
lengths[row['doc']]+=1
if row['term']==query:frequencies[row['doc']]+=1
count=len(lengths);average=sum(lengths.values())/count if count else 0
df=len(frequencies)
idf=max(math.log((count-df+0.5)/(df+0.5)),1e-6) if count else 0
rows=self.connection.execute('SELECT rowid,text,chunk_id,source_id,version,url,bm25(passages) AS native_score FROM passages WHERE passages MATCH ? ORDER BY native_score ASC,chunk_id ASC LIMIT ?',('"'+query+'"',limit)).fetchall()
results=[]
for row in rows:
tf=frequencies[row['rowid']];length=lengths[row['rowid']]
length_factor=1-0.75+0.75*length/average
saturation=tf*(1.2+1)/(tf+1.2*length_factor)
results.append({key:row[key] for key in ('chunk_id','source_id','version','url','text','native_score')} | {'term_frequency':tf,'document_length':length,'average_document_length':average,'document_frequency':df,'idf':idf,'length_factor':length_factor,'saturation':saturation,'explained_score':-idf*saturation})
return {'query':query,'status':'ok' if results else 'no_match','results':results}
def _search_terms(self, query, tokens, limit):
expression=' OR '.join('"'+term+'"' for term in tokens)
rows=self.connection.execute(
'SELECT text,chunk_id,source_id,version,url,bm25(passages) AS native_score FROM passages WHERE passages MATCH ? ORDER BY native_score ASC,chunk_id ASC LIMIT ?',
(expression,limit)).fetchall()
# Single-term diagnostic fields are intentionally absent for a multi-term score.
return {'query':query,'status':'ok' if rows else 'no_match','results':[dict(row) for row in rows]}python3 run.py --output /tmp/bm25-result.json
python3 -m unittest -v test_bm25.py{
"execution_mode": "offline-native-sqlite-fts5",
"queries": [
{
"native_scores": [
-0.0000014220532319391636,
-0.000001409990574929312,
-0.000001320388349514563
],
"order": [
"path-read",
"path-read-3-13",
"path-write"
],
"query": "text",
"status": "ok"
},
{
"native_scores": [
-0.000001409990574929312,
-9.432534678436318e-7
],
"order": [
"path-read-3-13",
"path-write"
],
"query": "newline",
"status": "ok"
},
{
"native_scores": [
-0.7992166451067193
],
"order": [
"path-write"
],
"query": "overwritten",
"status": "ok"
},
{
"native_scores": [],
"order": [],
"query": "xyznotindexed",
"status": "no_match"
},
{
"native_scores": [],
"order": [],
"query": "",
"status": "empty_query"
}
],
"rejected": 4
}Inspect the complete saved result
{
"execution_mode": "offline-native-sqlite-fts5",
"sqlite_version": "3.53.4",
"snapshot_id": "0615d22a3315e97c0a88c6c566c546877304f5a28a97e83f68dd36881d4bbd0e",
"tokenizer": "unicode61",
"bm25": {
"k1": 1.2,
"b": 0.75,
"ordering": "native_score ASC, chunk_id ASC",
"scope": "single term, one indexed text column, no column weights"
},
"indexed_terms": [
{
"term": "13",
"document_frequency": 1,
"occurrences": 2
},
{
"term": "3",
"document_frequency": 1,
"occurrences": 2
},
{
"term": "8",
"document_frequency": 2,
"occurrences": 4
},
{
"term": "a",
"document_frequency": 3,
"occurrences": 7
},
{
"term": "accepts",
"document_frequency": 1,
"occurrences": 1
},
{
"term": "added",
"document_frequency": 1,
"occurrences": 1
},
{
"term": "an",
"document_frequency": 1,
"occurrences": 1
},
{
"term": "and",
"document_frequency": 3,
"occurrences": 4
},
{
"term": "as",
"document_frequency": 1,
"occurrences": 1
},
{
"term": "ascii",
"document_frequency": 1,
"occurrences": 3
},
{
"term": "by",
"document_frequency": 1,
"occurrences": 1
},
{
"term": "characters",
"document_frequency": 2,
"occurrences": 2
},
{
"term": "closes",
"document_frequency": 3,
"occurrences": 3
},
{
"term": "data",
"document_frequency": 1,
"occurrences": 1
},
{
"term": "decoded",
"document_frequency": 1,
"occurrences": 1
},
{
"term": "does",
"document_frequency": 1,
"occurrences": 1
},
{
"term": "dumps",
"document_frequency": 1,
"occurrences": 1
},
{
"term": "encoding",
"document_frequency": 4,
"occurrences": 6
},
{
"term": "ensure",
"document_frequency": 1,
"occurrences": 2
},
{
"term": "errors",
"document_frequency": 3,
"occurrences": 3
},
{
"term": "existing",
"document_frequency": 1,
"occurrences": 1
},
{
"term": "false",
"document_frequency": 1,
"occurrences": 1
},
{
"term": "file",
"document_frequency": 4,
"occurrences": 9
},
{
"term": "in",
"document_frequency": 2,
"occurrences": 3
},
{
"term": "is",
"document_frequency": 1,
"occurrences": 1
},
{
"term": "it",
"document_frequency": 2,
"occurrences": 2
},
{
"term": "its",
"document_frequency": 1,
"occurrences": 1
},
{
"term": "json",
"document_frequency": 1,
"occurrences": 2
},
{
"term": "leaves",
"document_frequency": 1,
"occurrences": 1
},
{
"term": "method",
"document_frequency": 2,
"occurrences": 2
},
{
"term": "newline",
"document_frequency": 2,
"occurrences": 3
},
{
"term": "non",
"document_frequency": 1,
"occurrences": 1
},
{
"term": "none",
"document_frequency": 2,
"occurrences": 5
},
{
"term": "not",
"document_frequency": 1,
"occurrences": 1
},
{
"term": "number",
"document_frequency": 1,
"occurrences": 1
},
{
"term": "obj",
"document_frequency": 1,
"occurrences": 1
},
{
"term": "of",
"document_frequency": 1,
"occurrences": 1
},
{
"term": "opens",
"document_frequency": 1,
"occurrences": 1
},
{
"term": "or",
"document_frequency": 1,
"occurrences": 1
},
{
"term": "overwritten",
"document_frequency": 1,
"occurrences": 1
},
{
"term": "parameter",
"document_frequency": 1,
"occurrences": 1
},
{
"term": "path",
"document_frequency": 3,
"occurrences": 3
},
{
"term": "python",
"document_frequency": 1,
"occurrences": 2
},
{
"term": "read",
"document_frequency": 2,
"occurrences": 2
},
{
"term": "reading",
"document_frequency": 1,
"occurrences": 1
},
{
"term": "returns",
"document_frequency": 4,
"occurrences": 4
},
{
"term": "select",
"document_frequency": 2,
"occurrences": 2
},
{
"term": "set",
"document_frequency": 2,
"occurrences": 2
},
{
"term": "setting",
"document_frequency": 1,
"occurrences": 1
},
{
"term": "string",
"document_frequency": 2,
"occurrences": 3
},
{
"term": "text",
"document_frequency": 3,
"occurrences": 6
},
{
"term": "that",
"document_frequency": 1,
"occurrences": 1
},
{
"term": "the",
"document_frequency": 4,
"occurrences": 7
},
{
"term": "to",
"document_frequency": 1,
"occurrences": 2
},
{
"term": "true",
"document_frequency": 1,
"occurrences": 1
},
{
"term": "used",
"document_frequency": 1,
"occurrences": 1
},
{
"term": "utf",
"document_frequency": 2,
"occurrences": 4
},
{
"term": "was",
"document_frequency": 1,
"occurrences": 1
},
{
"term": "when",
"document_frequency": 1,
"occurrences": 1
},
{
"term": "write",
"document_frequency": 2,
"occurrences": 2
},
{
"term": "writer",
"document_frequency": 1,
"occurrences": 1
},
{
"term": "writes",
"document_frequency": 1,
"occurrences": 1
},
{
"term": "written",
"document_frequency": 1,
"occurrences": 1
}
],
"queries": [
{
"query": "text",
"status": "ok",
"results": [
{
"chunk_id": "c737db948e8111ae00e3442d8a22c80c876e43446dc1ee176ffe8ce142a41900",
"source_id": "path-read",
"version": "3.12",
"url": "https://docs.python.org/3.12/library/pathlib.html#pathlib.Path.read_text",
"text": "Path.read_text(encoding=None, errors=None) opens a file, returns its text as a string and closes the file. Set encoding=\"utf-8\" when reading a UTF-8 file.",
"native_score": -1.4220532319391636e-06,
"term_frequency": 2,
"document_length": 30,
"average_document_length": 34.0,
"document_frequency": 3,
"idf": 1e-06,
"length_factor": 0.9117647058823529,
"saturation": 1.4220532319391637,
"explained_score": -1.4220532319391636e-06
},
{
"chunk_id": "bdaeff5ae3ab88b70b12b9b6d4a0a46de45ea14520b971a7756cb03bbd384f35",
"source_id": "path-read-3-13",
"version": "3.13",
"url": "https://docs.python.org/3.13/library/pathlib.html#pathlib.Path.read_text",
"text": "In Python 3.13, Path.read_text accepts encoding, errors and newline. The newline parameter was added in Python 3.13. The method returns decoded file text and closes the file.",
"native_score": -1.409990574929312e-06,
"term_frequency": 2,
"document_length": 31,
"average_document_length": 34.0,
"document_frequency": 3,
"idf": 1e-06,
"length_factor": 0.9338235294117647,
"saturation": 1.409990574929312,
"explained_score": -1.409990574929312e-06
},
{
"chunk_id": "c66643a4609c6ee157458bf4353f7446b9c6a200a3ef453846d6a18d5b5069a7",
"source_id": "path-write",
"version": "3.12",
"url": "https://docs.python.org/3.12/library/pathlib.html#pathlib.Path.write_text",
"text": "Path.write_text(data, encoding=None, errors=None, newline=None) writes text to a file and closes it. Set encoding=\"utf-8\" to select UTF-8. An existing file is overwritten. The method returns the number of characters written.",
"native_score": -1.320388349514563e-06,
"term_frequency": 2,
"document_length": 39,
"average_document_length": 34.0,
"document_frequency": 3,
"idf": 1e-06,
"length_factor": 1.1102941176470589,
"saturation": 1.3203883495145634,
"explained_score": -1.3203883495145633e-06
}
]
},
{
"query": "newline",
"status": "ok",
"results": [
{
"chunk_id": "bdaeff5ae3ab88b70b12b9b6d4a0a46de45ea14520b971a7756cb03bbd384f35",
"source_id": "path-read-3-13",
"version": "3.13",
"url": "https://docs.python.org/3.13/library/pathlib.html#pathlib.Path.read_text",
"text": "In Python 3.13, Path.read_text accepts encoding, errors and newline. The newline parameter was added in Python 3.13. The method returns decoded file text and closes the file.",
"native_score": -1.409990574929312e-06,
"term_frequency": 2,
"document_length": 31,
"average_document_length": 34.0,
"document_frequency": 2,
"idf": 1e-06,
"length_factor": 0.9338235294117647,
"saturation": 1.409990574929312,
"explained_score": -1.409990574929312e-06
},
{
"chunk_id": "c66643a4609c6ee157458bf4353f7446b9c6a200a3ef453846d6a18d5b5069a7",
"source_id": "path-write",
"version": "3.12",
"url": "https://docs.python.org/3.12/library/pathlib.html#pathlib.Path.write_text",
"text": "Path.write_text(data, encoding=None, errors=None, newline=None) writes text to a file and closes it. Set encoding=\"utf-8\" to select UTF-8. An existing file is overwritten. The method returns the number of characters written.",
"native_score": -9.432534678436318e-07,
"term_frequency": 1,
"document_length": 39,
"average_document_length": 34.0,
"document_frequency": 2,
"idf": 1e-06,
"length_factor": 1.1102941176470589,
"saturation": 0.943253467843632,
"explained_score": -9.432534678436319e-07
}
]
},
{
"query": "overwritten",
"status": "ok",
"results": [
{
"chunk_id": "c66643a4609c6ee157458bf4353f7446b9c6a200a3ef453846d6a18d5b5069a7",
"source_id": "path-write",
"version": "3.12",
"url": "https://docs.python.org/3.12/library/pathlib.html#pathlib.Path.write_text",
"text": "Path.write_text(data, encoding=None, errors=None, newline=None) writes text to a file and closes it. Set encoding=\"utf-8\" to select UTF-8. An existing file is overwritten. The method returns the number of characters written.",
"native_score": -0.7992166451067193,
"term_frequency": 1,
"document_length": 39,
"average_document_length": 34.0,
"document_frequency": 1,
"idf": 0.8472978603872037,
"length_factor": 1.1102941176470589,
"saturation": 0.943253467843632,
"explained_score": -0.7992166451067194
}
]
},
{
"query": "xyznotindexed",
"status": "no_match",
"results": []
},
{
"query": "",
"status": "empty_query",
"results": []
}
],
"rejected_queries": [
{
"query": "text OR newline",
"error": "invalid_query"
},
{
"query": "text*",
"error": "invalid_query"
},
{
"query": "\"",
"error": "invalid_query"
},
{
"query": "x; DROP TABLE passages;",
"error": "invalid_query"
}
]
}Compare the recorded behavior
Compare the native results for text and overwritten, then inspect their term frequencies and document frequencies. Try write_text through the public query interface: the adapter searches for write or text, rather than treating the API name as one term. Then try unsupported punctuation such as text* and expect an explicit input rejection. Use indexed_terms() to inspect the actual tokenizer output. Finally try whitespace and a valid missing term, and compare their distinct statuses.
Read the explanation
Keep each adapter's input, filtering and score contract explicit; a retrieved passage is not an answer-validation result.
What’s next
Continue with Add Vector Retrieval.
References
Official SQLite FTS5 BM25, FTS query syntax and vocabulary tables were checked on 23 October 2026. The run record names the actual SQLite version. Source summaries retain their original version-specific Python documentation links.