Verifying an AI generated query before anyone quotes the answer

The exhibition sales director asks how much booth revenue is contracted for the 2026 edition. The box returns 4,284,000. She pastes it into an email to the chief executive at 08:12 and by 09:00 it is in a slide.

The correct figure was 4,046,000. The query ran perfectly. Verifying an AI generated query is the work that has to happen in the forty seconds between the answer appearing and the number leaving the building, and it comes down to showing the reader which rows were counted.

Two ways to be wrong, and only one of them is visible

Yale released Spider in 2018 with 10,181 questions across 200 databases covering 138 domains, and the reason it is still the reference point is the pair of metrics that came with it. The first is exact set match without values, which decomposes the SQL into clauses and compares them as sets against a reference query. The second is execution with values, which runs both queries and compares what comes back.

The two catch different failures, and the gap between them is the whole subject of this post.

Exact set match is strict about form. Write a correct query with the joins in a different order, or with a subquery where the reference used a window function, and it scores zero. Zhong, Yu and Klein measured how often that happens and reported in 2020 that "the current Spider metric leads to a 2.5% false negative rate on average and 8.1% in the worst case". So a system marked at 70 per cent by exact set match might be at 72 or 78 in truth, and the metric penalises exactly the behaviour you want, which is a model writing SQL its own way.

Execution accuracy is the one that matters operationally, and it has the opposite weakness. It is generous about form and blind about population. Two queries that return the same result set on the database in front of you are called equivalent, whether or not they would agree on next Tuesday's data.

A booth revenue question that runs and lies

Here is the 2026 edition, in numbers small enough to follow.

There are 612 exhibitor contracts on file, totalling 4,284,000 in contracted space value. Of those, 541 are active and worth 3,787,000, 34 were cancelled after signature and are worth 238,000, and 37 are pending countersignature at 259,000. The three components add to the 4,284,000, which is the only sum anybody in the room can do from memory.

The question was how much booth revenue is contracted. The intended population is everything except the cancellations, which is 578 contracts at 4,046,000.

The generated query summed contract value from the exhibitor contract table joined to the event instance table, with a filter on edition and no filter on status at all. It returned 4,284,000 across 612 rows. It ran in 40 milliseconds. Nothing about the output looked wrong, because 4,284,000 is a perfectly plausible booth revenue figure for a show of that size, and it is 238,000 too high, which is 5.9 per cent over the correct answer.

Now the second version of the same error, which is subtler and more common. Suppose the model had written a status filter. There are two obvious ones: exclude cancelled, or include only active. The first returns 578 contracts at 4,046,000. The second returns 541 contracts at 3,787,000. Both queries run. Both return a defensible number. They differ by 259,000, which is 6.4 per cent, and the difference is entirely the pending contracts that one query counts and the other does not.

No amount of staring at 3,787,000 tells you which query produced it. The row count does, in one glance, because the sales director knows roughly how many contracts she has signed.

Worth noticing which of the three candidate answers a reasonable person would defend. All of them, depending on the meeting. Gross contracted value is the right figure for a capacity conversation, because those 612 contracts each occupy floor space until somebody releases it. Net of cancellations is the right figure for a revenue forecast. Active only is the right figure for cash collection. The English question distinguished none of them, and a system that picks one and prints it without saying which has made a commercial judgement on behalf of a reader who did not know a judgement was available. Curating those definitions into the semantic layer the question runs against is Q1's work, and it is what stops the same ambiguity arriving fresh every morning.

What should sit beside every generated answer?

Four things, and they cost nothing to render because the system already has all of them.

The SQL itself, in a panel the reader can open. Most readers will never open it. The two who do are the ones who catch things, and their presence changes how carefully everyone else treats the number.

The row count, always, next to the figure. This is the single highest-value element on the list. A person who runs a show carries approximate counts in their head: about 600 exhibitors, about 18,000 registrations, about 40 sessions. A row count that is 6 per cent off from the number in their head produces a raised eyebrow within seconds, and a raised eyebrow is the entire mechanism.

The filter set, written in English, one line per predicate. Edition: 2026. Status: all. Registration type: all. Rendering "status: all" in plain words is what turns an invisible omission into a visible choice, and it is the element most systems leave out because it requires parsing the WHERE clause back into prose.

The source tables and the run timestamp. Which tables were read and when. A figure computed before this morning's load is a different figure, and attaching that lineage to the number itself is Q5's subject.

Why is a query that returns rows the dangerous one?

Because everything in the interface is telling the reader the system worked.

A query that errors produces a visible failure, somebody looks at it, and the cost is a minute of annoyance. A query that returns zero rows is also fine, because zero is obviously suspicious and people investigate it. The dangerous case is the query that returns a full-looking result set that happens to be the wrong population, and event data generates that case constantly: an inner join where a left join was meant, drops every registration with no matching company record. A date filter on created timestamp where the question meant the edition's registration window, moves the boundary by weeks. A join through a status dimension that only holds rows for records that reached a certain stage, silently excludes everything earlier in the funnel.

Each of those is one keyword. None of them throws an error. All of them return a number that looks like a number.

I would go further and say the fluency of the surrounding interface makes this worse. A generated query arrives with a chart, a title and a confident sentence, and the visual grammar of that presentation is identical whether the query was right or wrong. The presentation carries no signal, so the signal has to be added deliberately.

Building a fixture that can tell two queries apart

Zhong, Yu and Klein's answer to the equivalence problem was to distil small test databases with high code coverage, so that two queries that differ semantically actually produce different results when run. That idea transfers directly and cheaply.

Build a fixture database for your own schema: a few hundred rows, hand-constructed, containing at least one row of every awkward kind you know about. One registration with a null company. One exhibitor contract in every status including the rare ones. One person who registered for two editions under different email addresses. One badge scan with no matching registration. One contract cancelled and reinstated. One edition whose dates moved across a calendar year boundary.

Then, when the system generates a query for a question you care about, run the generated query and your hand-written reference against the fixture and compare result sets. On a fixture built this way, the two status filters above give different answers, because you deliberately put pending contracts in it. On a fixture scraped from a random sample of production, they might well agree, and you would learn nothing.

Two hundred rows is enough. The value is in the awkward rows, and you already know what they are, because they are the ones your team argues about.

Where this stops

Everything above verifies that a query returned the population somebody intended. It says nothing about whether the intention was right.

If the sales director wanted net revenue after discount and asked for contracted revenue, a perfectly verified query gives her a perfectly wrong answer to the question she meant, and the row count agrees with her mental model because the row count was never the problem. Verification catches translation errors. It cannot catch specification errors, and specification errors are more common in event reporting than anyone admits, because words like contracted, confirmed and booked have four meanings inside a single organisation.

The second limit is that the checks only work while people read them. A row count shown 200 times a week becomes furniture by week three. This is why I would attach the display to the transition points, when a number is copied, exported or put into a report, and keep the everyday view lighter. Measuring whether any of it is working at all needs a graded question set with known answers, which is Q10, and calibrating expectations against what the published benchmarks report is Q2.

Take the last generated answer anybody in your team quoted and write out its filter set in English, one line per predicate. If any line reads "status: all" or "date: created", you have found the shape of the problem in your own reporting layer, and the fix is a display change rather than a model change.