How to evaluate SQL queries that run successfully but return the wrong answer

Execution success vs. result correctness in text-to-SQL evaluation

Execution success as a necessary but incomplete check

Execution success records whether the generated SQL ran against the database without an error. A code-based execution check can capture failures such as invalid syntax or references to columns that do not exist.

A query can also complete successfully after filtering on a misspelled status value or summing revenue across duplicated join rows. The first query may return zero rows, and the second may return an inflated total, but both pass an execution check.

Result correctness against reviewed expected results

Result correctness compares the returned rows with the answer a reviewer has confirmed for the business question. Two queries with different structures, such as a subquery and a join, can receive the same correctness score when their results match under the agreed comparison rules.

Query comparison as a diagnostic signal

String-distance scores measure differences between generated and reference SQL, and the autoevals Sql scorer uses an LLM to judge the queries. Braintrust's text-to-SQL evaluation example records query-string and result scores separately.

When result correctness fails, comparing the generated SQL with the reference can help reviewers investigate filters, joins, and aggregations. A similarity score alone cannot identify the faulty clause, and a high score can overlook a consequential change to a single operator or value.

Common semantic errors in LLM-generated SQL

Wrong filter values and case mismatches

A model may copy a value from the question even when the database stores it differently. In Braintrust's text-to-SQL example, the generated query used a case-sensitive equality filter for "Thunder Bay Flyers (ushl)", but the stored value contained uppercase "USHL". That query returned zero rows. Adding sample column values to the prompt helped the model generate a filter that matched the stored value.

Filter errors also occur when a query uses a display label where the database stores a status code, selects the wrong similarly named column, or omits a required exclusion for test accounts or cancelled orders.

Date and time-period interpretation

Braintrust's NBA evaluation example uses dates stored as strings such as 10/29/14. An initial query misinterpreted the date format. After the prompt included a sample row, the generated query answered a question about wins in 2015 by matching dates ending in /15, selecting games played during that calendar year.

A query that reads the stored format correctly can still select the wrong period. A calendar year and an NBA season cover different sets of games, so the test question and reviewed answer should state which one the question means. Fiscal years, relative periods such as last quarter, time zones, and inclusive date boundaries require the same explicit definitions.

Aggregation, grain, and join errors

Aggregation tests need data that exposes mistakes in what the query counts, combines, or groups. Include cases covering the following errors.

• Counting the wrong entity: Counting order rows overstates the number of customers who placed orders when a customer has ordered more than once.
• Averages of averages: Averaging regional averages gives small and large regions equal weight, which can distort the average across all orders.
• Join fan-out: Joining orders to several matching line items repeats each order's total, inflating revenue when the query sums that total.
• Wrong grain: Grouping by month combines weeks that the question asks to compare separately. Grouping by account can similarly combine users whose results should remain separate.
• Dropped ties: When the question asks for every team sharing the highest total, sorting and keeping only the first row excludes other qualifying teams.

Result comparison policies for SQL evaluation

Row ordering

Preserve row order when the question explicitly requests a ranking or sorted answer. For an unordered answer, such as which regions exceeded a sales target, normalize both results into a consistent order before comparing them. Autoevals' JSONDiff scorer pairs list items by position, so harmless reordering can otherwise reduce the score.

Duplicate rows

Compare unordered results as multisets, which preserve how many times each distinct row appears. A set comparison collapses duplicates and could accept an order list where a join returns every order twice. Ignoring duplicate counts is appropriate only when the question asks for distinct values, such as which regions recorded any sales.

Column identity

Map generated columns to the expected business fields, allowing agreed aliases such as wins and total_wins. The NBA evaluation example removes column names and compares values by position, so reordered columns can affect its score. An explicit field mapping accommodates column-order changes and keeps each value associated with the correct measure. Require exact names or positions when downstream code depends on them, and specify whether extra columns are permitted.

Nulls and empty results

Keep a zero value, a NULL value, and an empty table distinct unless a reviewed business rule defines them as equivalent. Empty expected results need particular care because an overly restrictive query can match them accidentally. Pair a valid empty-result case with a closely related question that returns data to help expose filters that exclude too much.

Numeric tolerance and type normalization

Normalize equivalent numeric representations, such as counts returned as 175 and 175.0, before comparing them. Counts and exact financial totals need a pass/fail check, and floating-point ratios or averages may need a defined absolute or relative tolerance for rounding differences.

Autoevals' JSONDiff scorer uses NumericDiff for numbers by default, awarding approximately 0.947 to an output of 90 against an expected 100. For a business requirement that treats that discrepancy as an incorrect answer, configure the comparison to fail values outside the accepted tolerance.

Reference answers and database snapshots for reproducible SQL evals

Reviewed reference answers: Store each business question with the result a reviewer has confirmed and the reference SQL that produced it. In a Braintrust dataset, the expected field can hold the reference query and returned rows together, preserving the answer used for scoring and the SQL needed to investigate a failure.

A fixed database snapshot: Run evaluations against a read-only snapshot, a restored backup, or a fixture database populated from static data. Comparing against a changing production database can cause a correct query to disagree with a stored answer simply because records have been added or updated.

A pinned reference dataset version: Braintrust lets an evaluation load a specific dataset version. Record the database snapshot identifier in experiment metadata as well, so reviewers can identify both the test cases and the database contents used in each run.

An explicit reference date: Questions about "last month" or "the previous quarter" need a fixed date for interpreting the period. Store that date with the test case and supply it in the prompt context so repeated runs select the same time window.

Review after data or schema changes: When refreshing the database snapshot or changing its schema, rerun the reference queries and review any changed results before updating the expected answers. Evaluate both the baseline and candidate against the refreshed data so the comparison measures the prompt or model change consistently.

Fixture data that exposes incorrect interpretations: Include boundary dates, repeated join keys, nulls, and tied values that cause plausible SQL mistakes to produce different answers. For example, timestamps near midnight across multiple time zones can expose an incorrect date boundary that daytime records would conceal.

Score coverage for missing expectations and failed executions

Rows without reviewed expectations: Have the correctness scorer return None in Python or null in TypeScript when a question has no reviewed expected result. A None or null return skips the correctness score for that case, and an execution scorer can still assess whether the query ran. Assigning zero would count an unreviewed answer as incorrect and lower the average for a reason unrelated to the SQL assistant's performance.

Failed executions: Define whether a query error receives zero or is skipped by the result scorer. Braintrust's text-to-SQL cookbook assigns zero when the generated query fails, while its NBA cookbook returns None when output results are missing and records execution success separately. These policies produce averages with different meanings, so keep the chosen policy consistent across experiments.

Skipped rows change the average

When failed queries receive no correctness score, the average covers only queries that returned results. Consider this hypothetical comparison on the same 100 reviewed questions, using a pass/fail result comparison and skipping failed executions.

The candidate's correctness average rises four percentage points, but it produces nine fewer correct answers. Approving it on that average alone would overlook a decline in overall performance.

Review the execution success rate, correctness average, and number of scored cases together. For release decisions, add an end-to-end correctness score that awards one for an accepted result and zero for an incorrect result or failed execution, skipping only cases without reviewed expectations. Its average then measures the proportion of reviewed questions the assistant answered correctly, including the effect of execution failures.

Evaluating text-to-SQL result correctness in Braintrust

Developers run text-to-SQL evaluations in Braintrust from code or CI, and reviewers inspect the resulting experiments to determine whether a candidate prompt or model meets the application's correctness requirements before release.

Step 1: Record the generated SQL, results, and execution errors

Configure the evaluation task to return a structured output containing the generated query, returned rows, and any database execution error. Catch SQL execution errors and retain their messages so the execution scorer can assess failed queries alongside the other checks.

Opening a test case's trace shows its question, output, expected values, scores, metadata, and recorded spans. Reviewers can inspect the generated SQL and both result tables, then examine the model call that produced the query.

Step 2: Attach separate scorers to each test case

Add execution success, result correctness, query comparison, and end-to-end correctness scorers to the eval's scores array. Each score appears in its own experiment-table column, allowing reviewers to filter incorrect answers and check whether the query also failed to execute.

A basic equality scorer returns None when the expected value is missing and assigns one or zero to the remaining cases.

from typing import Any


def equality_scorer(
    input: Any,
    output: Any,
    expected: Any,
    metadata: dict[str, Any],
) -> float | None:
    if expected is None:
        return None

    return 1.0 if output == expected else 0.0

For SQL evaluation, replace the whole-output equality check with the reviewed comparison policy applied to output["results"] and expected["results"]. Whole-output equality also compares query text and error fields, potentially rejecting equivalent SQL that returns the correct answer. Handle execution failures before attempting the result comparison.

Step 3: Load a fixed reference dataset version

Load the reference dataset with init_dataset(). Use version for a specific transaction version or version_name for a named snapshot. An environment loads its currently assigned snapshot, so reassignment can change the records used by later runs. Ensure the baseline and candidate use the same resolved version.

from braintrust import Eval, init_dataset

# Load by environment
Eval(
    "My App",
    data=init_dataset(project="My App", name="My Dataset", environment="production"),
    task=lambda input: ...,
    scores=[],
)

# Load by version name
Eval(
    "My App",
    data=init_dataset(project="My App", name="My Dataset", version_name="version 1"),
    task=lambda input: ...,
    scores=[],
)

# Load by xact_id (less readable, but precise)
Eval(
    "My App",
    data=init_dataset(project="My App", name="My Dataset", version="8234923849293849..."),
    task=lambda input: ...,
    scores=[],
)

Supply the application's project, dataset, version, SQL task, and scorer functions. Record the database snapshot identifier, model, and prompt version in experiment metadata so reviewers can identify the configuration behind each run.

Step 4: Compare the candidate against a baseline

Select the reference run when you compare experiments. Braintrust matches test cases on input by default and displays per-score changes. Set a comparison key when inputs contain changing timestamps or session IDs, then filter the score columns to regressions.

In Braintrust's NBA example, a model change left execution success at 84.62%, with one improvement and one regression. Result correctness fell from 69.72% to 46.14%, with five regressions. The unchanged execution average concealed changes in which questions succeeded and a decline in result quality. A separate text-to-SQL example improved its result score by 10.33 percentage points but still regressed one case, showing why an improved average also needs individual review.

To read the comparison in a script, initialize the experiment with base_experiment and retrieve per-score changes through summarize().

from braintrust import init

experiment = init(
    project="My Project",
    experiment="new-experiment",
    base_experiment="baseline-experiment",  # or base_experiment_id="..."
)

summary = experiment.summarize()

for name, score in summary.scores.items():
    print(f"{name}: {score.improvements} improvements, {score.regressions} regressions, diff: {score.diff or 0}")

For pull-request checks, Braintrust's GitHub Action runs evaluations and posts a comment with the results on the pull request. Reporting alone does not fail the check, because bt eval returns a non-zero exit code only when an eval throws an exception. To fail the CI run when the candidate misses an agreed quality requirement, such as minimum end-to-end correctness or a required test case that must pass, define a custom Reporter() whose report_run returns false for those results, since that return value sets the process exit code the action reports on.

Step 5: Review regressions and update the evaluation dataset

Enable Diff and select Base -> Comparison to inspect changes in generated SQL and returned results. Use Expected -> Output to compare a run's output with its reference, and open a row for a character-level diff.

The built-in Errors view shows recorded trace errors. For database errors caught by the task and returned in output.error, filter that output field explicitly. Group results by question-category metadata to identify clusters of date, aggregation, or business-domain failures.

After review, add failed examples and edge cases to the dataset with corrected expected results and a category describing the issue. Review failures promoted from production logs before accepting their outputs as reference answers. Run the baseline and candidate against the updated dataset version to confirm that the fix resolves the case without introducing further regressions.

Notion runs the same kind of regression evaluation in Braintrust to assess model differences before deployment.

Evaluate SQL result correctness with Braintrust →

Also read: How to start text-to-SQL evals without a labeled dataset and How to turn LLM production failures into regression tests

FAQs about text-to-SQL evaluation (2026)

What is the difference between execution accuracy and execution success in text-to-SQL evaluation?

Execution success counts queries the database can run, and execution accuracy counts queries that produce the reference answer. Benchmark accuracy figures are comparable only when the evaluation uses compatible result-matching rules.

Should a failed SQL query receive a correctness score of zero?

Assign zero in any score used for release decisions, because a failed query leaves the user without an answer. A second score that skips failures helps diagnose result quality among completed queries, provided you report the execution success rate and the number of scored cases alongside it.

Why can a text-to-SQL correctness score rise while fewer questions get correct answers?

The set of scored questions may have changed. Excluding failed queries can leave easier cases contributing to the average, making performance appear stronger. Check the total correct-answer count and whether the same cases were scored in both runs.

How much numeric tolerance should a SQL result comparison allow?

Choose tolerance from the calculation's accepted precision, such as the required decimal places for a percentage. Test values just inside and outside the boundary, and require exact values for discrete quantities such as counts.

How should a text-to-SQL eval handle questions with more than one valid interpretation?

Clarify the intended date range, entity, or aggregation before labeling the answer. Keep ambiguous production questions as separate cases with agreed expected behavior, such as requesting clarification, and test each supported interpretation with explicit wording.

Can an LLM judge evaluate text-to-SQL correctness?

An LLM judge can assess whether SQL follows the question and business rules. Check its reliability on deliberately incorrect queries, and confirm correctness through execution and result comparison whenever reviewed answers are available.