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 normalizes and compares SQL abstract syntax trees (ASTs) without running them. It confirms matches for the rewrites it understands; when it can't see why two queries match, it returns unknown, never unequal. A future dataset might tell the queries 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). It can never falsely reject a correct query. Today it ships one check:

  • AstEquivalence. Parses both queries, normalizes their ASTs into canonical forms, and compares those forms. Matching canonical ASTs confirm semantic equivalence without running either query. Differing ASTs 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 AST normalization handles

AstEquivalence confirms equivalences that hold regardless of the data, by rewriting both ASTs into canonical forms 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 more than these AST rewrites fall through: wrapping a query in a CTE or a subquery, or using a different join shape, can produce a different canonical AST even when the results are identical. The AST check returns unknown on those.

Non-determinism

A query whose result is not a function of its inputs cannot be confirmed by AST normalization: two textually identical SELECT current_timestamp queries return different values each run. AstEquivalence detects non-deterministic calls including rand(), current_timestamp, and uuid(). It 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() tries semantic equivalence first, then 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: normalizes and compares the queries' SQL ASTs; it confirms or returns unknown. A semantic-equivalence 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 runs member scorers in order. It returns the first that passes, or the last result if none pass; the last result includes diagnostics such as a diff. assert_eval ANDs the scorers you pass it, so a case passes only when every scorer passes. Pass observed_equivalence() as one scorer rather than passing its two scorers separately.

from evaldata import FirstDecisive, ResultSetEquivalence, SemanticEquivalence

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

When no warehouse is available, or results depend on live data, use judged_equivalence(model). It keeps the semantic-equivalence confirmation and replaces execution 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. It contains 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 AST check ignores these options. It decides on normalized SQL ASTs, not 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, SolverSuccess

_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)
    assert isinstance(output, SolverSuccess)
    sql = output.output
    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 AST normalization, no query run. The AI reorders the AND predicates and changes casing; the canonical ASTs match and only ("semantic_equivalence", True) is in the trail.
  • Confirmed by execution. The AI wraps the query in a CTE; AST normalization 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; AST normalization returns unknown and the execution member refutes with a diff of the differing rows.
  • Inconclusive on non-determinism. current_timestamp can't be confirmed by AST normalization; 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() Normalizes and compares SQL ASTs; confirms or returns unknown You want semantic equivalence without execution
observed_equivalence() Semantic equivalence first, else runs both queries and diffs the results Grading AI SQL against a gold query (the usual choice)
judged_equivalence(model) Semantic equivalence 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 AST. A plan check sees through more rewrites (a CTE, a pushed-down filter) that leave the canonical AST different. It would slot in as another SemanticEquivalence check.

Next steps