# How do you evaluate LLM-generated SQL beyond exact matches?

> Evaluate generated SQL against the business question across contrasting data cases, not just matching query text or one successful result.

Source: https://www.nodus-compute.ai/blog/evaluate-llm-generated-sql/
Build revision: 364dd57152437ff26b6a4ebad4f685ff5f4fcb5f

**Evaluate LLM-generated SQL by checking whether it answers the intended business question across deliberately contrasting data cases.** Exact query-text matching can reject valid alternatives. Matching a reference result on one database can accept a query that is wrong for other valid data. Use reviewed expected results, cases that separate plausible interpretations, and explicit rules for duplicates, missing values and ordering.

Keep correctness separate from permission to execute. A query can return the expected number while reading information it should not access. Conversely, a safely rejected request has no query result to compare.

Nodus can run an existing batch evaluation command and collect its outputs through [Jobs](https://www.nodus-compute.ai/docs/guides/jobs/). The evaluator and the business definitions remain yours. Successful execution is evidence that a command finished, not that its SQL answered the right question.

## Define the question before selecting the reference

A request such as “How many customers purchased last month?” leaves consequential choices unstated. Does a refunded purchase count? Does “customer” mean a person or an account? Which timezone defines the month? Are test purchases excluded?

Write down the intended answer and the allowed interpretation before inspecting model results. If the request is genuinely ambiguous, specify whether the assistant should ask a clarification question or follow a documented reporting convention. Do not silently choose whichever interpretation makes its output look correct.

The evaluation unit should include the request, the permitted data, the reporting definitions and the expected response behavior. Keep these fixed when comparing models. Otherwise, a changed business definition can masquerade as a model improvement.

Have someone who understands the reporting task review the reference. A polished reference query is still only a proposed interpretation.

## Distinguish three kinds of agreement

**Text agreement** asks whether the generated query resembles a chosen reference. This is useful for diagnosis, but it is not a general correctness test. Different expressions can answer the same question.

**Single-case result agreement** asks whether both produce the same result on one fixed data snapshot. It checks an observable outcome, but can hide a wrong date boundary, a missed exclusion or accidental double counting when that snapshot contains no separating example.

**Agreement across contrasting cases** asks whether the answer stays correct when valid business circumstances change. This is stronger evidence about the intended meaning, though no finite collection proves correctness for every possible database.

[Semantic Evaluation for Text-to-SQL with Distilled Test Suites](https://arxiv.org/abs/2010.02840), by Ruiqi Zhong, Tao Yu and Dan Klein, studies evaluating meaning using multiple database cases. The practical lesson here is narrower than adopting a research score: one matching result does not settle semantic correctness. Your business rules still determine which cases matter.

## Worked example: a revenue answer that passes by accident

Suppose the request is “What was net sales revenue last month?” The agreed definition counts completed sales, subtracts refunds associated with those sales, excludes tax and uses a specified reporting timezone.

The following numbers are illustrative, not a benchmark or a Nodus customer result. Each row is an independent case. Candidate A omits refunds; candidate B follows the agreed refund treatment. Both otherwise follow the stated definition.

|Case|Completed sales|Associated refunds|Expected net revenue|Candidate A|Candidate B|
|-|-|-|-|-|-|
|No refunds|$1,000|$0|$1,000|$1,000|$1,000|
|Partial refunds|$1,000|$200|$800|$1,000|$800|
|Fully refunded sales|$1,000|$1,000|$0|$1,000|$0|

The first case cannot distinguish these candidates. On that case alone, both pass. Across all three cases, A passes one and B passes three. That does not make B universally correct: the table isolates refund handling and says nothing yet about tax, timezone boundaries or permitted access.

Avoid treating the three rows as three independent user requests. They are evidence about one reporting requirement under different conditions. Report both request-level acceptance and the failed case categories so a long list of easy variants does not dominate the score.

## Choose cases that expose a different wrong answer

For each requirement, ask: what valid data would make a plausible mistake visible?

* **Time boundaries:** include activity on both sides of the reporting cutoff and resolve which timezone controls membership.
* **Counting rules:** include several purchases by the same customer so customer counts cannot accidentally equal purchase counts.
* **Duplicate effects:** include legitimate repeated activity and situations where the same sale could appear more than once in a result.
* **Missing information:** distinguish a recorded zero from an unknown amount or an absent record.
* **No qualifying activity:** define whether the correct response is zero, an empty result or an explanation that information is unavailable.
* **Scope restrictions:** include otherwise relevant information outside the permitted business unit or account.

Use synthetic or appropriately authorized test data, not a convenient copy of sensitive production records. Cases must remain valid under the business rules. An impossible example can punish a correct query and create false confidence in the alternative.

Start with known expensive mistakes and common reporting requests. Add new categories when reviewed failures reveal a missing distinction. Reserve untouched requests and cases for the final comparison instead of repeatedly adapting to the entire test collection.

## Make result comparison match the requested deliverable

Decide whether row order matters before scoring. A set of customers may be correct in any order; a request for the highest-revenue customers requires the right ranking and an agreed rule for ties.

Do not erase duplicate rows automatically. Sometimes repeated values represent distinct events; sometimes repetition is precisely the defect. Likewise, specify appropriate numeric tolerance and rounding for the quantity being reported. A tolerance that suits a measured estimate may be unacceptable for a financial count.

Compare the requested detail, not only a grand total. Two regional reports can share a total while assigning revenue to the wrong regions. Review empty results and errors explicitly rather than dropping them from the denominator.

Treat timeouts, execution errors, refusals and requests for clarification as separate outcomes. Their acceptability depends on the request, but none should disappear merely because result comparison was unavailable.

## Gate execution safety separately

Run evaluation only within an authorized, isolated testing context with appropriate limits. Define which information may be read and whether any changes are allowed. A correct answer must not excuse an unauthorized action.

Report safety failures separately from answer accuracy and decide which failures block release regardless of the average. Also review explanations: a correct table accompanied by a misleading claim about its scope can still produce the wrong business decision.

For a larger batch comparison, Nodus Jobs can provide execution, progress visibility and output retrieval for your existing evaluation process. They do not supply a built-in SQL correctness judge or establish that a reporting definition is right. To discuss running your evaluation batch, [book a conversation](https://nodus-compute.ai/book/).

## FAQ

### Should exact query matches count as correct?

Only if the reference itself is correct for the complete request. A match can be useful evidence, but it does not repair an ambiguous definition or an invalid reference.

### Is executing against one production snapshot enough?

No. It may miss important distinctions and introduces access and privacy concerns. Use authorized test cases designed to expose mistakes, with reviewed expected answers.

### Can an LLM judge replace execution-based checks?

A judge can help triage disagreements or inspect explanations. It should not substitute for checking observable results when those results are available, and its own decisions need validation.

### Does passing every test prove two queries are equivalent?

No. It establishes agreement on the tested cases under the stated comparison rules. Keep that claim distinct from equivalence across all valid data and from permission to run the query in production.
