Skip to content

Check semantic equivalence

There are many ways to write the same query. SemanticEquivalence checks whether the AI's SQL means the same thing as a gold query rather than whether the syntax is identical.

For example, in each row below the AI's SQL has the same meaning as the gold query:

Gold query AI's SQL Same meaning because
SELECT amount + 1 SELECT 1 + amount + is commutative
WHERE x IN (1, 2) WHERE x IN (2, 1) IN-list order doesn't matter
WHERE country = 'US' AND id > 1 WHERE id > 1 AND country = 'US' AND operands reorder freely
WHERE id > 1 WHERE 1 < id < is the converse of >

Two ways to establish equivalence

Equivalence can be shown two ways, with opposite strengths:

  • By reasoning about the queries — SemanticEquivalence compares their structure without running them. It proves a match holds on every dataset; but when it can't see why two queries match it returns unknown, never unequal — the next dataset might be the one that tells them apart.
  • By running the queries — ResultSetEquivalence executes both and compares the rows. It can refute a match with a diff, but a match observed on one dataset is evidence, not proof.

So SemanticEquivalence returns one of two verdicts per check — equivalent (confirmed) or unknown (could not confirm) — and can never falsely reject a correct query. Today it ships one check:

  • AstEquivalence — parses both queries, normalizes their syntax trees, and compares them. Matching trees mean the queries are equivalent, decided without running either query. Differing trees are inconclusive, so it returns unknown.

It runs its checks in order and stops at the first that confirms. If none confirm, the result is inconclusive, with the explanation "no semantic check could confirm equivalence".

The expected outcome must be a GoldQuery: equivalence compares one query against another, so there must be a reference query.

What the syntax check normalizes

AstEquivalence confirms equivalences that hold regardless of the data, by rewriting both trees into a canonical form before comparing. It normalizes:

  • commutative and associative operators (AND, OR, the bitwise operators, +, *) — operands are reordered and re-associated;
  • IN-list order;
  • comparison direction (1 < id becomes id > 1);
  • constant expressions (id > 2 - 1 folds to id > 1);
  • identifier casing.

Equivalences that depend on the query's shape rather than its operators fall through: wrapping a query in a CTE or a subquery, or a different join shape, produces a different tree even when the results are identical. The syntax check returns unknown on those.

Non-determinism

A query whose result is not a function of its inputs cannot be compared on syntax: two textually identical SELECT current_timestamp queries return different values each run. AstEquivalence detects non-deterministic calls — rand(), current_timestamp, uuid(), and similar — and returns unknown rather than risk a false confirmation.

Compose with execution: observed_equivalence

On its own, SemanticEquivalence either confirms or is inconclusive. To decide the cases it can't confirm, pair it with execution. observed_equivalence() does exactly that: it confirms by structure when it can, and otherwise runs both queries and diffs the results.

from evaldata import observed_equivalence

scorer = observed_equivalence()

observed_equivalence() is a FirstDecisive over two scorers:

  1. SemanticEquivalence — compares the queries; it confirms or returns unknown. A structural confirmation passes immediately and execution is skipped.
  2. ResultSetEquivalence — runs both queries and diffs the result sets under the case's ComparisonConfig. Equal result sets pass; a difference fails and carries the diff. This is the layer that can refute.

FirstDecisive is the generic combinator behind this: it runs member scorers in order and returns the first that passes, else the last (so the last member's diagnostics, such as a diff, surface). Note that assert_eval ANDs the scorers you pass it — a case passes only when every scorer passes. A "first that passes wins" fallback is the opposite shape, so it needs a combinator: pass observed_equivalence() (a single scorer) rather than the two scorers separately.

from evaldata import FirstDecisive, ResultSetEquivalence, SemanticEquivalence

# observed_equivalence() is shorthand for:
FirstDecisive([SemanticEquivalence(), ResultSetEquivalence()])

When you can't run the queries — no warehouse, or a result that depends on live data — its sibling judged_equivalence(model) keeps the structural confirmation but replaces the execution member with an LLM judge that grades whether the two queries are equivalent:

from evaldata import judged_equivalence

scorer = judged_equivalence("openai/gpt-4o-mini")

The result of a FirstDecisive carries a metadata["first_decisive"] trail — one {"scorer", "passed", "verdict"} entry per member that ran — so you can see which layer decided.

Comparison options

The execution member, ResultSetEquivalence, diffs result sets under the case's ComparisonConfig:

  • column_order — "ignore" (default) compares columns by name; "strict" also requires the same column order.
  • null_equality — "equal" (default) treats two NULLs as matching; "distinct" treats them as different (and requires a match_key).
  • float_tolerance — the absolute tolerance for numeric columns; values within it compare as equal.
  • match_key — when set, rows are aligned on these key columns and compared per column, reporting which columns differ; when empty, rows are compared as an unordered bag.

The syntax check ignores these options — it decides on query structure, not on data.

Write the eval

"""Semantic-equivalence example evals: AI SQL that differs syntactically but is semantically equivalent.

`SemanticEquivalence` compares the queries and confirms a match without running them;
`observed_equivalence()` adds a fallback that runs both queries and compares their results.
"""

import tempfile
from collections.abc import Iterator
from pathlib import Path

import duckdb
import pytest

from evaldata import CallableSolver, EvalCase, SemanticEquivalence, eval_case, observed_equivalence
from evaldata.platforms import duckdb_platform, resolve
from evaldata.scorers import AstEquivalence, QueryRunner, ScoreContext, Scorer
from evaldata.types import ScoreResult

_DB_PATH = Path(tempfile.mkdtemp(prefix="evaldata_ex01_sem_")) / "shop.duckdb"
_PLATFORM = duckdb_platform(name="examples-semantic-equivalence", path=str(_DB_PATH))


@pytest.fixture(scope="module", autouse=True)
def _seed_db() -> Iterator[None]:
    con = duckdb.connect(str(_DB_PATH))
    con.execute("CREATE TABLE customers (id INTEGER, name VARCHAR, country VARCHAR)")
    con.execute("INSERT INTO customers VALUES (1, 'Ada', 'GB'), (2, 'Bo', 'US'), (3, 'Cy', 'US')")
    con.close()
    yield


def _score(case: EvalCase, model_sql: str, scorer: Scorer) -> ScoreResult:
    """Score `model_sql` against the case's gold query with `scorer`.

    Args:
        case: The eval case, carrying the gold query and platform.
        model_sql: The model's SQL to judge against the gold query.
        scorer: The scorer to run: `SemanticEquivalence` (compares the queries) or composite `observed_equivalence`.

    Returns:
        The `ScoreResult`.
    """
    solver = CallableSolver(lambda _case: model_sql)
    output = solver.solve(case)
    sql = output.output
    assert sql is not None  # CallableSolver always produces SQL here
    dialect = case.platform.dialect or case.platform.kind
    runner = QueryRunner(resolve(case.platform), sql, dialect, None)
    context = ScoreContext(queries=runner)
    return scorer.score(case, output, runner.run(sql), context=context)


def _trail(result: ScoreResult) -> list[tuple[str, bool]]:
    """The `(scorer, passed)` of each member that ran under `observed_equivalence`, in order.

    Args:
        result: An `observed_equivalence` score result.

    Returns:
        One `(scorer, passed)` pair per member that ran.
    """
    return [(entry["scorer"], entry["passed"]) for entry in result.metadata["first_decisive"]]


def _verdicts(result: ScoreResult) -> list[tuple[str, str]]:
    """The `(method, equivalence)` of each verdict a `SemanticEquivalence` result recorded.

    Args:
        result: A `SemanticEquivalence` score result.

    Returns:
        One `(method, equivalence)` pair per check that ran.
    """
    return [(v["method"], v["equivalence"]) for v in result.metadata["verdicts"]]


@eval_case(
    input="Which US customers have an id above 1?",
    expected={"kind": "gold_query", "sql": "SELECT name FROM customers WHERE country = 'US' AND id > 1"},
    platform=_PLATFORM,
)
def test_ast_confirms_without_executing(case: EvalCase) -> None:
    """Reordered predicates and casing match after normalization; confirmed without running either query."""
    result = _score(case, "select NAME from customers where id > 1 and country = 'US'", observed_equivalence())
    assert result.passed
    # A structural confirmation skips execution, so only the first member ran.
    assert _trail(result) == [("semantic_equivalence", True)]


@eval_case(
    input="Name the US customers.",
    expected={"kind": "gold_query", "sql": "SELECT name FROM customers WHERE country = 'US'"},
    platform=_PLATFORM,
)
def test_execution_confirms_when_ast_is_inconclusive(case: EvalCase) -> None:
    """A CTE the syntax check can't match; the execution fallback runs both queries and confirms."""
    result = _score(
        case, "WITH us AS (SELECT * FROM customers WHERE country = 'US') SELECT name FROM us", observed_equivalence()
    )
    assert result.passed
    assert _trail(result) == [("semantic_equivalence", False), ("result_set_equivalence", True)]


@eval_case(
    input="Name the US customers.",
    expected={"kind": "gold_query", "sql": "SELECT name FROM customers WHERE country = 'US'"},
    platform=_PLATFORM,
)
def test_execution_refutes_wrong_query(case: EvalCase) -> None:
    """A wrong filter; the syntax check is inconclusive and execution refutes with a diff."""
    result = _score(case, "SELECT name FROM customers WHERE country = 'GB'", observed_equivalence())
    assert not result.passed
    assert _trail(result) == [("semantic_equivalence", False), ("result_set_equivalence", False)]
    assert result.diff is not None
    assert result.diff.missing_row_count == 2  # 'Bo', 'Cy' expected but absent
    assert result.diff.extra_row_count == 1  # 'Ada' returned but not expected


@eval_case(
    input="What time is it now?",
    expected={"kind": "gold_query", "sql": "SELECT current_timestamp AS t"},
    platform=_PLATFORM,
)
def test_ast_inconclusive_on_nondeterminism(case: EvalCase) -> None:
    """`current_timestamp` can't be compared on syntax; `SemanticEquivalence` is inconclusive."""
    result = _score(case, "SELECT current_timestamp AS t", SemanticEquivalence([AstEquivalence()]))
    assert not result.passed
    assert result.verdict == "inconclusive"
    assert result.explanation == "no semantic check could confirm equivalence"
    assert _verdicts(result) == [("ast", "unknown")]

The composite cases read back the metadata["first_decisive"] trail to show which layer decided; the case that only compares the queries reads back metadata["verdicts"]:

  • Confirmed by syntax, no query run — the AI reorders the AND predicates and changes casing; the trees match and only ("semantic_equivalence", True) is in the trail.
  • Confirmed by execution — the AI wraps the query in a CTE; the syntax check returns unknown and the execution member confirms, so the trail is semantic_equivalence (inconclusive) then result_set_equivalence (pass).
  • Refuted by execution — the AI filters on the wrong country; the syntax check returns unknown and the execution member refutes with a diff of the differing rows.
  • Inconclusive on non-determinism — current_timestamp cannot be compared on syntax; with SemanticEquivalence alone, nothing decides and the result is inconclusive.

Run it

uv run pytest test_semantic_equivalence.py -q

Run it from a clone

This is the bundled examples/01_deterministic/test_semantic_equivalence.py example. If you've cloned the repo, run it directly with uv run pytest examples/01_deterministic/test_semantic_equivalence.py.

Choose the checks

Scorer What it does Use when
SemanticEquivalence() Compares query structure; confirms or returns unknown You want structural proof only, no execution
observed_equivalence() Structure first, else runs both queries and diffs the results Grading AI SQL against a gold query (the usual choice)
judged_equivalence(model) Structure first, else an LLM judge; runs no query Execution isn't available

Plan equivalence (future tier)

A logical-plan check is a planned addition. Spark's DataFrame.sameSemantics is the model: it canonicalises the logical plan a DataFrame compiles to and compares those plans. That is a different layer from AstEquivalence, which canonicalises the SQL syntax tree — a plan check sees through more rewrites (a CTE, a pushed-down filter) that leave the syntax tree different. It would slot in as another SemanticEquivalence check.

Next steps