A query layer that cannot represent its own limitations will misrepresent them as confidence. This chapter establishes what “correct” means, why benchmarks miss it, and the algebra underneath.
Someone on your revenue team asks the assistant a question:
“How much ARR is at risk from accounts that raised churn concerns this quarter?”
Twelve seconds later:
$7.2M across 12 accounts. The largest exposures are Acme Corp ($1.4M), Northwind ($900K), and Initech ($740K). Three of these have open P0 tickets with no linked opportunity, suggesting a gap in account coverage.
That is a good answer. It is specific, actionable, has the shape of something a competent analyst would produce, and it is going to be pasted into a Slack channel with the CRO in it.
Every step that produced it was valid. The vector search returned results. The SQL parsed, ran, and returned rows. The join found matches. Nothing errored, nothing timed out, no exception got swallowed. If you had a test suite over this pipeline, it would be green.
The number is wrong five separate ways:
Each is a different failure with a different fix, and four of the five were named in the database literature before the phrase “vector database” existed. The rest of this book is about building a query layer where none of these can happen silently.
But first: some evidence that this is not a model-capability problem that will be solved by the next release, and the small amount of formal machinery you need to see why.
Text-to-SQL looked essentially solved in 2023. On Spider 1.0, the standard academic benchmark, agent frameworks built on frontier models reported north of 90% execution accuracy. The narrative was that this was a mopping-up exercise.
Then Spider 2.0 arrived (Lei et al., ICLR 2025). Same research group, same idea, but the problems are drawn from real enterprise workflows: hundreds of columns, dialect-specific functions, multi-step transformations.
| Benchmark | Best agent framework |
|---|---|
| Spider 1.0 | 91.2% |
| BIRD | 73.0% |
| Spider 2.0 | 21.3% |
A seventy-point drop. The more useful read comes from BIRD’s own ablation: BIRD ships each question with an “external knowledge” hint — business context a human analyst would have. With it, GPT-4 scores 54.89%. Without it, 34.88%. Twenty points of accuracy live in knowing what the business means by these columns, and none of it is in the schema.
Human experts on the same set: 92.96%.
The gap is not reasoning horsepower. It is that the model is asked to infer semantics that were never written down — which grain a table is at, whether a measure can be summed, which join path is sanctioned, what “active customer” means this quarter.
And then a finding that should unsettle anyone shipping on these benchmarks: a 2025 study (FLEX) had human experts re-adjudicate BIRD results and found execution accuracy agreed with expert judgment only about 62% of the time. We have been optimizing hill-climbing curves against an oracle that is wrong nearly four times in ten.
Execution accuracy vs. semantic correctness
Execution accuracy asks: did the query run and return the same rows as the reference query? It cannot ask: is this number meaningful? Those are different questions, and the second one is where agents fail in production, silently, at scale. This book is entirely about the second question.
You know SQL. What you may not have is an explicit mental model of the properties SQL operations guarantee — the properties that, when violated, produce exactly the five failures above. This section states them.
A relation is a set of tuples. Every tuple in the relation exists; every tuple not in the relation does not exist. This is the closed-world assumption — the absence of a fact is the assertion of its negation.
Under this assumption, NOT EXISTS is sound: if a tuple is missing from the relation, it genuinely doesn’t exist. The moment a relation is incomplete — some qualifying tuples are absent because retrieval was approximate, or because the user lacks permission to see them — the closed-world assumption fails, and NOT EXISTS becomes a lie.
SQL operates on bags (multisets) rather than sets, which adds multiplicity. A tuple can appear more than once, and aggregation sums over the multiplicity. When a join introduces spurious multiplicity — because the grain doesn’t match — SUM inflates. Not because the SQL is wrong, but because the algebraic precondition for SUM to mean what the caller thinks it means was never checked.
Every relational operator has a precondition — a property the input must satisfy for the output to mean what it claims. When we state them explicitly, the five failures of the opening example become five contract violations:
| Operator | Contract | What breaks |
|---|---|---|
| Selection (\(\sigma\)) | Predicate well-defined over the schema | Rarely fails in practice |
| Projection (\(\pi\)) | Projected attributes exist | Rarely fails in practice |
| Join (\(\bowtie\)) | Join key uniquely identifies tuples in at least one input (no spurious fan-out) | Chapter 3: grain mismatch, 3.4× inflation |
| Aggregation (\(\gamma\)) | Aggregate function is valid for the measure’s additivity class over the grouping dimensions | Chapter 3: summing a stock across time |
| Difference (\(-\)) | Both inputs are complete for the querying principal | Chapter 5: fabricated absence |
These are not new observations. They are the content of classical relational theory, stated in a way that makes the connection to agent query failures visible.
Consider the query: SELECT account_id FROM tickets WHERE account_id NOT IN (SELECT account_id FROM opportunities). Under what conditions on the opportunities relation does this query produce a sound answer? Under what conditions does it produce a fabricated absence?
The query is sound if and only if opportunities is complete for the querying principal — every opportunity that exists and is relevant to the predicate is present in the relation as visible to this user. If rows have been filtered by row-level security, the result will include accounts that do have opportunities (the user simply cannot see them), which is a fabricated absence — the assertion “this account has no opportunity” when the truth is “this account has an opportunity you cannot see.”
When you compose operators — join two relations, filter, then aggregate — the output inherits the weakest guarantee from its inputs. This is the meet rule, and it is the single most important principle in this book:
The meet rule
If operator \(f\) takes inputs \(A\) and \(B\), and each input carries a guarantee level from an ordered lattice, then the output’s guarantee is:
\[ \text{guarantee}(f(A, B)) = \text{guarantee}(A) \sqcap \text{guarantee}(B) \]where \(\sqcap\) is the meet (greatest lower bound). In plain terms: the weakest input wins. An exact warehouse fact joined with a top-k retrieval result produces a top-k result, always, regardless of how precise the warehouse side is.
This principle prevents laundering — an inexact result being promoted to exactness by association with an exact one. It applies identically across all five annotation types we will build (completeness, grain, additivity, confidence, authorization guarantee), which is why we later unify them under a single algebraic framework.
An inverted index maps terms to posting lists — the set of documents containing each term. A Boolean query over an inverted index is exact and complete for its terms: if a document contains the term, it appears in the posting list. If it doesn’t appear, it genuinely doesn’t contain the term.
This exactness comes with a constraint: the query must be expressible in the vocabulary of the index. You can ask “which documents contain the word churn” and get a complete answer. You cannot ask “which documents discuss churn risk” — that is a semantic query, and the inverted index has no semantics.
The properties that matter for composition:
COUNT(*) the output.These are precisely the properties that vector search trades away.
An ANN index maps dense vectors to their approximate nearest neighbors. The “approximate” is load-bearing: the index uses a data structure (HNSW graphs, IVF clusters, product quantization) that trades recall for speed. The contract is:
And critically: ANN search returns results ranked by similarity, not by relevance. A document can be the nearest neighbor in embedding space without being relevant to the user’s intent, and a relevant document can be missed because it’s not near in the embedding geometry.
When you feed ANN results into a relational pipeline — joining them with warehouse data, filtering, aggregating — you are composing an incomplete, unstable, ordered output with operators that assume complete, stable, unordered inputs. Every relational operator downstream inherits the incompleteness. But nothing in the pipeline says so.
This is the fundamental tension, and it is why the completeness lattice of Chapter 2 exists: to make the incompleteness visible in the type of the result, so that downstream operators can respect it or reject it, rather than silently assuming it away.
The count that changes with k
Suppose you run a semantic search for “churn risk” conversations with k=50, extract account IDs, and count distinct accounts. You get 12.
Now run the same search with k=200. You get 23 distinct accounts.
With k=500: 31 accounts.
The “true” count (exhaustive nearest-neighbor search over all documents) is 38. But you cannot know that without running the exhaustive search, which defeats the purpose of the index.
The number 12 is not wrong — those 12 accounts genuinely raised churn concerns. But reporting “12 accounts” as though it were a count is wrong, because it implies completeness. The honest statement is: “at least 12 accounts, from the top 50 matching conversations.”
You have two data sources for a query: a warehouse table (complete, exact) and a vector search result (top-k, k=100). You join them on account_id. What is the completeness of the joined result? What if you then UNION ALL the joined result with a second warehouse table?
The join: by the meet rule, exact ⊓ top_k = top_k. The joined result is top-k. The union: top_k ⊓ exact = top_k. Once any input is incomplete, the aggregate result is incomplete. The only way to recover exactness is to remove the incomplete input entirely (which may mean refusing the query).
Look at the five failures of the opening example through the lens of this section:
SUM applied to a semi-additive measure over a dimension it cannot be summed across. Contract violation on the aggregation operator.None of these is “the model generated bad SQL.” The SQL is valid. The operations ran. The contracts were violated before the SQL was ever generated, at the layer where the pipeline was assembled from components with mismatched guarantees.
This means the fix lives at the same layer. Not in the prompt. Not in the model. In the type system over the query plan — a set of annotations that travel with every intermediate result, composition rules that propagate them through operators, and gates that reject or qualify the output when an annotation signals unsoundness.
The remaining seven chapters build that type system, one annotation at a time, then unify them into a single algebra and show you how to implement the whole thing as a query-plan validator sitting between the LLM and execution.
What we are building
| Failure | Annotation | Composition rule |
|---|---|---|
| Top-k counted as exact | completeness | Meet across joins |
| Semi-additive summed across time | additivity | Error on illegal re-aggregation |
| Fan-out double counting | grain | Error on aggregate over mismatched join |
| Fuzzy identity | confidence | Min along the link path |
| Permission-inverted negation | guarantee | Meet across leaves; reject on negation |
Notice what the fix does not involve:
All of these help with the first-order problem: generating plausible SQL for a given question. None touches the second-order problem: that the pipeline has no representation of its own trustworthiness, and a fluent narrator will smooth over every gap with confident English.
The semantic-layer research community has shown that making business semantics explicit — in a metric registry, a semantic layer, a compiled IR — lifts text-to-SQL accuracy by 17–23 points on benchmarks. That is real and valuable. But even a perfectly generated query can be semantically unsound if the pipeline doesn’t enforce the contracts above. The metric registry tells the model what to generate. The annotation system tells the pipeline when to reject.
Both are necessary. This book is about the second.
ResultSet that carries a completeness annotation. The type should have values exact, sampled, top_k(k), truncated, and unknown. Implement a meet method that takes two ResultSets and returns the weaker completeness.-- conversations: stored in vector index, retrieved via ANN search
-- monthly_account_arr: warehouse table, grain = (account_id, month)
-- orders/order_items: warehouse, grain as named
-- opportunities: warehouse, RLS filters by user role
-- entity_links: cross-system identity, scored edges