Don't let the LLM write the query

Picture a store of chat conversations that people review after the fact. Each has a date, a summary, the transcript and tags such as escalation, and reviewers score it from 1 to 5 on things like safety: a first review is L1, and a second-level review that adjudicates on top of it is L2. An analyst who doesn’t know a query language gets an agent to search the store: a chat box backed by an LLM.

The conversations sit in a search index such as Elasticsearch or OpenSearch, one JSON document each, queried with a DSL: a nested JSON object saying which documents to match, in what order, and how many to return (SQL’s role in a relational database; most of this post applies to SQL too). The agent reaches the index through tool use: your code offers the model a function and a JSON schema for its arguments, the model replies with arguments, and your code runs the function. The obvious first design has one tool, search, whose argument is the whole query. The model writes the DSL.

She asks for “conversations tagged escalation that mention chest pain”, and the model sends this (hand-written, not a recorded generation):

{"query": {"bool": {"filter": [
  {"term": {"tags": "escalation"}},
  {"term": {"summary": "chest pain"}}
]}}}

term means “this field holds exactly this value”, which fits tags, a keyword field stored as exact strings. But summary is a text field: an analyzer lowercased it and split it into tokens (chest, pain, …), and no token is spelled chest pain. The model needed match, which analyzes the query text the same way. The query is valid, returns zero hits, and the agent reports none. The index holds 756.

Next she asks for L2-reviewed conversations from the last seven days with safety below 3, newest first, 20 per page. Page 2 means “same query, skip the first 20”, which only works if both requests order rows the same way. The model rewrites the query for page 2 and drops the sort: seven of its twenty rows repeat page 1, and eleven conversations that belonged in the first forty never appear. Every request returned HTTP 200.

A query planner turns a request into the steps that fetch it. Here the model is the planner, re-choosing syntax, sort order and where page 2 starts on every request, by sampling: page 2 runs a different plan than page 1, a cache keyed on the query rarely hits, and you can’t test a query nobody has seen. Even a stable query can be wrong: below, a schema description lists two spellings for the reviewer levels, a full scan finds eight, and a filter that follows the documentation returns 65.9% of the L2 reviews, every time.

The fix takes the planning back. The model fills a small typed form, a spec (date range, reviewer levels, score limits, tags, search words, sort, page size), and ordinary code, a compiler, turns each spec into the same query every time or rejects it.

The mechanism

The model became your planner

Only some of a DSL query’s decisions need language understanding: which fields, values and time window. The rest have one correct answer, set by how the index is built: filter (include or exclude only) or must (also feeds the relevance score), term or match, a nested wrapper for the reviews (stored as nested objects, hidden sub-documents reachable only through nested), the sort and its tie-breaker, whether now-7d is rounded to a day, and where page 2 starts.

Temperature 0 (always take the most likely next token) doesn’t make these stable: the Messages API reference says that “even with temperature of 0.0, the results will not be fully deterministic”, and on models released after Claude Opus 4.6 you can no longer set it to anything but 1.0. Thinking Machines ran one prompt 1,000 times at temperature 0 on an open model and got 80 unique completions, because GPU kernel results vary with batch size, which varies with server load.

So split the work: the model fills the spec, a small language you own, and everything after it is a compiler:

page 1   question → model (one tool, strict schema) → spec
         spec → validate → canonicalize → hash → compile → run
page 2+  cursor (spec hash + last sort values) → compile → run
         (no model call)

Canonicalizing rewrites the spec into one standard form, whose hash is the cache key. The invariant is one intent, one byte string, one total order: the same intent compiles to the same bytes, those bytes give every row exactly one position (no ties left for the engine to settle), and a cursor pins the spec for every later page. The model can still fill the spec differently on two runs, but the variation shrinks to a few typed fields you can diff and log.

Reproduce it

Two pipelines, one question

Step through the runs and watch the hit count, the page-2 line (rows repeated, rows never shown) and the cache key. The left column’s generations are hand-written to show how a DSL-writing model varies (not recorded outputs), executed by pagination.py against a seeded 6,000-document index. The right column compiles the spec each run would fill.

In question 1, run 2 moves the clauses to must and drops the sort, so every hit scores the same and order falls back to internal document order, while the unrounded now-7d lands on a different instant per request. Run 3 drops the nested wrapper and matches nothing. On the right, specs that differ only in bytes share one cache key.

The compiler

Compile, don’t generate

The spec is the smallest language that covers your questions

The reference spec in compiler.py has eight fields: date_from, date_to, reviewer_levels, scores, tags, text, sort, page_size. Each maps to one kind of clause. A question the spec can’t express is rejected, not approximated; count the rejections to learn which field to add next. The tool definition is generated from the schema, so its enums are exactly the vocabulary the compiler accepts:

# sketch of the call; not executed here (no API key in this repo)
tool = tool_definition()   # enums come from the schema and profile
tool["strict"] = True      # constrained sampling: fits the schema
resp = client.messages.create(
    model=MODEL, max_tokens=1024, tools=[tool],
    # auto, not forced: the newest models reject a forced tool_choice
    messages=[{"role": "user", "content": question}],
)
calls = [b.input for b in resp.content if b.type == "tool_use"]
if not calls:
    raise NoSpec(resp)  # no tool call is a failure path, not an answer
spec = calls[0]

Strict tool use “guarantees Claude’s tool inputs match your JSON Schema by constraining the model’s token sampling to schema-valid outputs”. Forcing a call is model-dependent: on Claude’s newest models, tool_choice of tool or any returns a 400, and the docs recommend auto plus strict mode, under which the model may call nothing (a failure path). The schema guarantees shape, not meaning (the structured outputs docs note it doesn’t even guarantee the capitalization of enum values), so the server validates regardless.

Validate, and return errors the model can act on

A rejected spec should name the field and the fix, because the error goes back to the model as the tool result for one retry. These are real messages from the reference validator:

reviewer_levels[0]
  "HUMAN_REVIEWER" is a stored spelling of L2; use "L2"
scores[0].metric
  unknown metric "saftey"; use one of: accuracy, completeness,
  empathy, safety (did you mean "safety"?)
scores
  no safety score satisfies these filters (needs >= 4 and <= 2)
sort
  sort "relevance" needs text; add text or use "newest" or "oldest"

The validator is a trust boundary, so fuzz it. compiler.py throws 5,000 specs with junk in random places at it: 4,817 are rejected with errors, 183 come out valid, and none crash.

Canonicalize before you hash

The canonical form erases everything that can’t change the result: case, whitespace, duplicates, list order. Defaults are filled in, and aliases like <= map to one operator name. Integer scores become a closed interval, so lt 3, lte 2 and < 2.5 all mean safety in [1, 2]. A no-op filter like safety >= 1 is dropped, and a contradiction is an error.

The cache key is a sha256 of the canonical form’s sorted-key JSON, prefixed with a compiler version (c1:009412b1350b706a for the question above). A property test generates 2,000 random specs with five surface variants each: every variant hashes identically and compiles to byte-identical DSL, and the 1,905 distinct canonical forms get 1,905 distinct hashes.

A canonicalizer can also be consistently wrong, so a second test checks the engine’s results for the compiled query against an independent evaluator that reads the raw spec: all 300 surface-varied specs match, while a control with an off-by-one planted in the compiled range bounds gets 105 wrong, invisible to the hash test because hashing happens before compilation.

Your own code must be deterministic too. CPython randomizes string hashing per process, so a compiler that builds a list by iterating a set emits different bytes in different processes. Across five PYTHONHASHSEED values the compiler gives one digest of its output; a control that builds its terms lists from sets gives five, each a silent miss in a cache shared across workers.

Compile from the mapping, not from the model’s guess

The compiler reads the field types and makes the choices the runs above got wrong:

if c["reviewer_levels"] or c["scores"]:
    inner = []
    if c["reviewer_levels"]:
        # a level expands to every stored spelling of it
        levels = _raw_levels(c, schema)
        inner.append({"terms": {"reviews.level": levels}})
    for m in sorted(c["scores"]):
        ...   # a range with only the non-trivial bounds
    # one nested clause: the same review must satisfy level AND scores
    filters.append({"nested": {"path": "reviews",
                               "query": {"bool": {"filter": inner}}}})
if c["text"]:   # text fields are analyzed: match, never term
    mm = {"multi_match": {"query": c["text"], "operator": "and",
                          "fields": ["summary^2", "transcript"]}}
...
# the unique key breaks every tie
sort = [{"created_at": d}, {"conversation_id": d}]

Text fields get match, never term: Elastic’s docs say “avoid using the term query for text fields”, and OpenSearch says the same. term_vs_match.py runs eight valid queries over four documents, and three return nothing: term with “chest pain” in either casing, and the .keyword sub-field (an unanalyzed copy of the text) when the case differs. Keyword fields compare exact bytes, so escalation also misses a document tagged Escalation.

Review conditions share one nested clause, so “an L2 reviewer scored safety below 3” means one review satisfied both. Without it, Elastic’s mapping docs explain, arrays of objects are flattened and a document “would incorrectly match” across objects: an L2 review with good safety next to an L1 review with bad safety would pass.

Dates become absolute, half-open bounds (gte the start, lt the day after the end), so adjacent windows never overlap. Elastic notes that queries using now “are typically not cacheable”.

A compiler can have the same bug, but in one place: fix it once and pin it with a test.

Edit the JSON as if you were the model, or load a preset, and watch the cache key and the compiled output.

live compiler · spec → search DSL / SQLJS port of compiler.py

– cache key –

search DSL SQL canonical spec

–

One IR, more than one target

The canonical spec is an IR (intermediate representation), so it can target other backends. The SQL tab compiles it for PostgreSQL, with an EXISTS subquery for the reviews, keyset pagination on (created_at, conversation_id), and every value bound as a parameter. Model output never becomes SQL text, which closes the injection path OWASP’s LLM05 names: “LLM-generated SQL queries are executed without proper parameterization”.

The math

Why pages drift

Offset pagination serves page k as rows [k·p, (k+1)·p) of some ordering, so every request must see the same ordering. The query can change it (the regenerated runs above), and so can ties, even when the query is identical.

In filter context every hit scores 0, so a filter-only query with no sort is one giant tie, and sorting on a 1-to-5 score leaves thousands of ties. Engines break ties by internal document id, and Elastic’s pagination docs warn that those ids “can be completely different across replicas”, the copies of each shard (a slice of the index) that a request can land on. pagination.py sends the same query for every page, routes each request to a random copy, and averages over 200 walks of a 1,553-row result set. (Real routing is adaptive, not uniform; Elastic recommends a user or session id as the preference to pin it.) Each cell is the share of rows never shown to a reader who pages through every result:

copies of the shard	integer sort, no tie-breaker: never shown	filter-only, no sort: never shown
1	0.0%	0.0%
2	16.8%	24.6%
3	21.7%	29.4%
5	25.0%	32.6%

Each page is still full, so every unseen row means another shown twice. If the g tied rows are reshuffled for every request, each page of p rows is a random draw, and a given row misses one page with probability 1 − p/g:

P(never shown) = ∏ over pages (1 − p_i / g)
               → (1 − p/g)^(g/p)
               → 1/e ≈ 36.8%   as g/p grows

For g = 1,553 and p = 20, the closed form gives 36.6% and the simulation 36.5%: more than a third of the rows never appear, and nothing errors. PostgreSQL documents the same trap for LIMIT/OFFSET: results are inconsistent “unless you enforce a predictable result ordering with ORDER BY”.

A total order still fails under offset paging when rows keep arriving. With the compiled sort and 3 new matching rows before each request, the walk shows 12 duplicates in 5 pages, one per insert per page boundary. search_after says “start after the row with these sort values” instead of “skip 40 rows”, so new rows can’t push old ones across a boundary: 0 duplicates, 0 misses. Across 200 random specs, 3 copies and random routing, every compiled search_after walk is exact, while the control (offset paging, no tie-breaker) breaks 23 of 200. Add a point in time (PIT) if every page must see one snapshot of the index.

The twist

Deterministic is not the same as true

A compiled query is stable, cacheable and correctly paginated, and it can still be wrong every time. The compiler has to know which stored strings each enum value stands for, and here it learned them from the schema description.

So “filter to L2” compiles to terms reviews.level ["HUMAN_REVIEWER"] and returns 1,284 of 1,947 L2 reviews (65.9%), every time. vocab_drift.py replays every counted review through the compiler and engine under both mappings, assuming L2_* and HUMAN_REVIEWER are L2 and the rest L1 (in a real system a person signs off that table).

Switch vocabularies and look for bars marked “should match, never returned”.

one keyword field, eight spellings · documented vs profiled vocabularyproduction counts, full scan

documented (2 values) profiled (8 values) filter to L2 L1 both

returned by the filter should match, never returned outside this filter

–

The documented L1 filter returns 76.5% of L1 work, and six of the eight spellings, 35,361 reviews (23.7%), can’t be reached by any level filter. A raw-DSL generator adds a failure the spec can’t have: of six spellings a model might plausibly emit (HUMAN_REVIEWER, human_reviewer, Human Reviewer, L2, LEGAL, ANNOTATOR_LEGAL), four return zero rows and no error. The validator rejects all but L2 with the fix (“use L2” or “use L1” for a stored spelling in any case, the allowed values for LEGAL) and compiles L2 to all three L2 spellings.

The fix keeps the compiler and changes where its vocabulary comes from:

1. The model picks L1 or L2 and never sees stored spellings.
2. A reviewed table maps each level to its spellings, and the compiler expands L2 to all of them.
3. A profiling job, at startup and on a schedule, runs an exact terms aggregation (each distinct value of a field with its count) over every enum-backed keyword field, and alarms on spellings the table doesn’t map and on mapped spellings that no longer occur.

{"size": 0,
 "aggs": {"reviews": {
   "nested": {"path": "reviews"},
   "aggs": {"level": {"terms": {"field": "reviews.level",
                                "size": 1000}}}}}}

sum_other_doc_count in the response (documents whose values didn’t make it into the returned buckets) must be 0. A terms aggregation returns “the top ten terms” by default, and with size set to 5 the same profile drops three spellings (136 rows) without complaint. When you need every term, the docs point you to a composite aggregation.

Reading code isn’t profiling (the audit’s mistake), and neither is sampling. ANNOTATOR_LEGAL sits on 1 row out of 149,420, so a uniform 2,000-row sample misses it with probability 98.7%, and on average misses 1.96 of the 8 spellings.

These counts are all-time, so they can’t say what share of recent L2 work used the documented spelling. If newer spellings dominate, the same filter can return close to nothing for a recent window; the drift alarm should compute that share per window.

Prior art

Where this sits

Having the model emit a logical form that code executes is an old idea in semantic parsing: Zelle and Mooney (AAAI 1996) learned parsers that map sentences “directly into executable queries”. The closest modern relative, Shin et al. (EMNLP 2021), has the model paraphrase into “a controlled sublanguage” that can be “automatically mapped to a target meaning representation”, with constrained decoding enforcing the grammar; strict tool schemas productize that constraint for JSON.

Raw query generation is still far from reliable. On the BIRD text-to-SQL benchmark, GPT-4 reached 54.89% execution accuracy against 92.96% for humans, and its authors argue models need “database value comprehension in addition to semantic parsing”, which is the vocabulary problem above. On Spider 2.0’s enterprise-scale schemas, an o1-preview agent solves 21.3% of tasks, against 91.2% on Spider 1.0 and 73.0% on BIRD. None of these measure whether a question compiles to the same query twice, whether page 2 agrees with page 1, or whether the value vocabulary still matches the data.

Semantic layers make the same split for metrics: dbt positions its own so that AI tools’ “answers use your governed metrics instead of guessing at raw tables”. They are usually sold on consistent definitions, but the operational case is as strong: the IR’s hash gives each query a cache key and a cursor that can’t drift.

Fixes

Fixes, ranked

fix	what it removes	what it costs
1. Model fills a spec; code compiles	the whole class: syntax, sort and paging choices leave the model	a spec you have to design and grow; rejected questions until you do
2. Canonicalize, hash, version	cache misses on equivalent specs; untestable output	a canonical form to maintain; a version bump invalidates the cache
3. Total order + search_after (+ PIT), page 2 from a bound cursor	duplicates and gaps between pages; model calls on page 2	cursor plumbing; PIT keep-alive cost
4. Compile from the mapping	term on text, cross-object matches, now drift	the compiler must load the mapping at startup
5. Profile enums into a reviewed table, alarm on drift	stable-but-wrong filters; silent vocabulary gaps	a scheduled aggregation and someone who owns the table
6. Keep raw DSL, but lint it	some cases: append the tie-breaker, rewrite now, reject term on text fields	a linter that chases the model’s creativity forever

Ship 1 to 3 together: they are one change, and the reference implementation is under 450 lines before its tests. Fix 5 is the one teams skip, because once the compiler exists every query looks correct. Use fix 6 only when you can’t change the architecture this quarter.

Keep the IR small, since every field is a clause type to compile, canonicalize and test; a spec that grows or groups, arbitrary field names and free-form ranges is the DSL again. For a field with too many distinct values to list, accept a string, check it against the profile on the server, and return the closest matches in the error.

Detect it

Detect it on day one

• Zero-hit rate by spec field. Log each zero-hit answer with its canonical spec and show the user “0 results for: L2, safety 1 to 2, Oct 12 to 19”. A spike on one field points at vocabulary.
• Spec-rejected rate by error path: the spec’s backlog, and an early warning when a prompt change starts producing junk.
• Distinct cache keys per question, replaying a fixed question set nightly. More than one is model instability, visible field by field.
• Unmapped-spelling rows from the profiling diff, per time window. Above zero is an alarm.
• A page-walk test in CI against a multi-replica index: no duplicates, count equal to total (_walk in compiler.py).
• A golden file of compiled output: changed bytes invalidate the cache, so golden.json fails the run until someone bumps the compiler version.

Try it

Run the experiments

Everything is stdlib Python 3.9 plus Node for the parity check, with seeded randomness and no API keys.

cd experiments/dont-let-the-llm-write-the-query
python3 -I compiler.py       # property tests, fuzz, golden file
python3 -I pagination.py     # page drift, ties across copies, inserts
python3 -I vocab_drift.py    # documented vs profiled vocabulary
python3 -I term_vs_match.py  # three valid queries that return nothing
node parity.mjs              # JS port vs compiler.py, byte for byte

minisearch.py is a small in-memory engine that reproduces only the behaviours this post depends on, so treat its numbers as a model of an engine, not a benchmark of one. The raw-DSL generations in canned.json were written by hand. Every other number comes from these scripts, the field notes or a linked source.