NLQueries GitHub

Blog  / 

How to Compare AST-Validated NL2SQL with Raw LLM SQL: Measuring Schema Validity, Policy Enforcement, and Answer Correctness

Published . Written for nlqueries-core 0.4.

evaluation validation sql

Picture a team that has run a raw "prompt in, SQL out" wrapper for a year and wants to replace it with a pipeline that parses and validates every statement before it touches the database. The proposal deck has one slide that matters: the new pipeline "fixes most of the bad SQL." Then a security reviewer asks two questions. Bad how? And fixed by what: the parser, the richer prompt, the retry loop, or the fact that the new setup also runs under a tighter database role?

Those questions are the whole problem with "validated vs. raw" comparisons. A pipeline like this changes several things at once, so a single before-and-after number can't tell you which change did the work. Worse, that number usually merges three outcomes that fail in completely different ways. This post sets out how to measure them separately, with a fixture small enough to reason about by hand and an ablation design that keeps each variable isolated. It's a measurement plan, not a scoreboard: this is a protocol and synthetic illustration, not measured evidence that an AST-validated system outperforms raw model SQL.

Three outcomes that get blurred into one

When people say a query is "valid," they could mean any of three things, and you need all three as separate columns.

Schema validity asks whether the statement refers to tables and columns that exist and compiles against the real schema. A hallucinated column like c.tenant fails here.

Policy enforcement asks whether the statement is something your application is willing to run at all. This is where an AST check lives. An abstract syntax tree (AST) is the parsed structure of a statement, so an AST-based policy judges what the query is made of, such as its root statement type, the functions it calls and the tables it reads, rather than matching text patterns. A DELETE should be refused here even though it's perfectly well-formed.

Answer correctness asks whether the rows returned answer the business question. This is the one that matters to the person asking, and it's the one no parser can judge. A query that forgets status = 'PAID' is schema-valid and policy-clean, and it still gives you the wrong revenue.

NLQueries Core is a useful example of keeping these apart, because its source makes the separation explicit. Its SQL policy module parses each generated statement with sqlglot in the connector's dialect and refuses anything not on a per-dialect allow-list of constructs and functions. It also re-parses the rendered form and refuses statements whose reading changes on a round trip (sql_policy.py). That's a statement about structure. Nothing in it claims the query computes the right number, and the project's own hardening guide is blunt that a validator "is a program with bugs in it" and that the database's permission system is the only boundary that doesn't depend on the validator being correct (Hardening the database NLQueries reads).

The same split applies to any validated pipeline. Before comparing anything, write down which of the three outcomes each safeguard is supposed to affect. If a component's job is structural, don't credit it with correct answers.

A fixture small enough to check by hand

Before you measure anything at scale, it helps to see the three outcomes diverge on data you can verify in your head. The fixture below is synthetic. It has two tenants, six orders and an order_items table with two rows for order 101, put there on purpose to expose join fan-out. One order has a NULL amount, and one sits exactly on the November boundary.

The running question is: for tenant A, what were paid-order revenue and paid-order count in October 2026? Revenue ignores the NULL amount, but that order still counts, so the expected answer is 30,000 cents across 3 orders.

CREATE TABLE customers(id INTEGER PRIMARY KEY, tenant_id TEXT NOT NULL, region TEXT);
CREATE TABLE orders(id INTEGER PRIMARY KEY, customer_id INTEGER REFERENCES customers(id),
                    order_date TEXT, amount_cents INTEGER, status TEXT);
CREATE TABLE order_items(id INTEGER PRIMARY KEY, order_id INTEGER REFERENCES orders(id), sku TEXT);
INSERT INTO customers VALUES(1,'A','East'),(2,'A','West'),(3,'B','North');
INSERT INTO orders VALUES
(101,1,'2026-10-01',10000,'PAID'),(102,1,'2026-10-02',5000,'CANCELLED'),
(103,2,'2026-10-31',20000,'PAID'),(104,3,'2026-10-02',70000,'PAID'),
(105,1,'2026-11-01',40000,'PAID'),(106,2,'2026-10-15',NULL,'PAID');
INSERT INTO order_items VALUES(1,101,'a'),(2,101,'b'),(3,103,'c');

-- Reference answer: expected (30000, 3)
SELECT SUM(o.amount_cents) AS revenue_cents, COUNT(*) AS paid_order_count
FROM orders AS o JOIN customers AS c ON c.id = o.customer_id
WHERE c.tenant_id = 'A' AND o.status = 'PAID'
  AND o.order_date >= '2026-10-01' AND o.order_date < '2026-11-01';

Against this fixture, the reference query returns (30000, 3) on SQLite 3.45.3. One caution: the tenant_id = 'A' predicate is expected-answer arithmetic, not an authorization boundary. Testing tenant isolation needs access controls enforced separately, in the database.

Now feed a scorer six hand-written "frozen" statements: the reference, three plausible mistakes, a hallucinated column and a write. The script below is a small custom harness. It isn't part of NLQueries or any other product. It checks schema validity by asking SQLite to compile each statement with EXPLAIN against a schema-only copy, which reads no rows. For policy it uses a deliberately crude stand-in that requires a single SELECT root. Both checks run on every statement, so each column is always filled, and a statement is executed for the answer check only if it passes both. The SCHEMA and DATA strings carry the same fixture as the block above, so the script runs on its own.

# three_outcomes.py — custom illustration harness; not NLQueries code.
# Requires: Python 3.12, sqlglot (30.12.0 was installed). SQLite ships with Python.
import sqlite3
import sqlglot
from sqlglot import exp

SCHEMA = '-- Synthetic fixture (SQLite syntax). Expected answer: 30000 cents, 3 orders.\nCREATE TABLE customers(id INTEGER PRIMARY KEY, tenant_id TEXT NOT NULL, region TEXT);\n\nCREATE TABLE orders(id INTEGER PRIMARY KEY, customer_id INTEGER REFERENCES customers(id),\n                    order_date TEXT, amount_cents INTEGER, status TEXT);\n\nCREATE TABLE order_items(id INTEGER PRIMARY KEY, order_id INTEGER REFERENCES orders(id), sku TEXT);'   # the three CREATE TABLE statements above
DATA = "\n\nINSERT INTO customers VALUES (1,'A','East'),(2,'A','West'),(3,'B','North');\n\nINSERT INTO orders VALUES\n  (101,1,'2026-10-01',10000,'PAID'), (102,1,'2026-10-02',5000,'CANCELLED'),\n  (103,2,'2026-10-31',20000,'PAID'), (104,3,'2026-10-02',70000,'PAID'),\n  (105,1,'2026-11-01',40000,'PAID'), (106,2,'2026-10-15',NULL,'PAID');\n\nINSERT INTO order_items VALUES (1,101,'a'),(2,101,'b'),(3,103,'c');"     # the INSERT statements above
GOLD = [(30000, 3)]
BASE = ("SELECT SUM(o.amount_cents), COUNT(*) FROM orders o "
        "JOIN customers c ON c.id = o.customer_id ")
FROZEN = {
    "reference": BASE + "WHERE c.tenant_id='A' AND o.status='PAID' "
                 "AND o.order_date >= '2026-10-01' AND o.order_date < '2026-11-01'",
    "no_status_filter": BASE + "WHERE c.tenant_id='A' "
                 "AND o.order_date >= '2026-10-01' AND o.order_date < '2026-11-01'",
    "inclusive_end_date": BASE + "WHERE c.tenant_id='A' AND o.status='PAID' "
                 "AND o.order_date BETWEEN '2026-10-01' AND '2026-11-01'",
    "fan_out_join": BASE + "JOIN order_items i ON i.order_id = o.id "
                 "WHERE c.tenant_id='A' AND o.status='PAID' "
                 "AND o.order_date >= '2026-10-01' AND o.order_date < '2026-11-01'",
    "missing_column": BASE + "WHERE c.tenant='A' AND o.status='PAID'",
    "write_statement": "DELETE FROM orders WHERE status='CANCELLED'",
}

def schema_ok(sql, schema_db):
    try:
        schema_db.execute("EXPLAIN " + sql)   # compiles against the schema, reads no rows
        return True
    except sqlite3.Error:
        return False

def policy_ok(sql):                           # stand-in; use your real policy here
    try:
        trees = sqlglot.parse(sql, read="sqlite")
    except sqlglot.errors.ParseError:
        return False
    return len(trees) == 1 and isinstance(trees[0], exp.Select)

schema_db = sqlite3.connect(":memory:"); schema_db.executescript(SCHEMA)
data_db = sqlite3.connect(":memory:"); data_db.executescript(SCHEMA + DATA)
for name, sql in FROZEN.items():
    s, p = schema_ok(sql, schema_db), policy_ok(sql)
    rows = data_db.execute(sql).fetchall() if (s and p) else None
    answer = "n/a" if rows is None else ("correct" if rows == GOLD else "WRONG")
    print(name, "pass" if s else "FAIL", "pass" if p else "REFUSE", answer, rows)

With Python 3.12.9, SQLite 3.45.3 and sqlglot 30.12.0, this exact script prints the following. Each line gives the statement name, the schema check, the policy check, the answer verdict and the rows returned:

reference pass pass correct [(30000, 3)]
no_status_filter pass pass WRONG [(35000, 4)]
inclusive_end_date pass pass WRONG [(70000, 4)]
fan_out_join pass pass WRONG [(40000, 3)]
missing_column FAIL pass n/a None
write_statement pass REFUSE n/a None

Three of the four statements that cleared both checks give the wrong answer. The missing status filter picks up the cancelled order. The inclusive BETWEEN drags in the order dated exactly November 1. The fan-out join is the nasty one: the count still looks right because order 106 drops out of the inner join while order 101 is counted twice, so a quick glance at the count column won't catch it.

The last two rows show why schema and policy need separate columns. The hallucinated column sails through a structural check and fails schema compilation. The DELETE compiles fine and gets refused by policy. Merge those into one "valid" flag and you lose the ability to say which safeguard caught what. REFUSE on the write is the expected outcome. It's the control doing its job, so your harness should score it as a pass for the refusal metric, not log it as an error. Keep genuine harness failures, such as a crashed connection or a script exception, in a separate bucket so they can never masquerade as refusals.

That's six hand-written strings, not model output, so it says nothing about how often any model makes these mistakes. It does show the shape your real results table needs.

Freeze the SQL, then change one thing at a time

The core of a fair comparison is an ablation: you change one variable per lane and keep everything else fixed. Four lanes cover it.

Lane A is the raw baseline: the same model, the same fixed schema context and a plain prompt, with each output stored. Lane B takes exactly lane A's stored SQL and passes it through the schema and policy checks. Nothing is regenerated, so any difference between A and B comes from validation alone. Lane C adds bounded repair, meaning a retry after a rejection, but only if your pipeline supports it and you can instrument each attempt. Lane D is the full NLQueries pipeline, treated as a separate system comparison rather than a parser effect.

That last distinction matters because the full pipeline changes far more than validation. Its prompt assembly keeps the knowledge-base schema in a stable block and adds per-question dynamic context: relevant-table hints, retrieved example "capsules" and any extra_dynamic_context you pass, which is appended as guidance rather than replacing the schema (prompt_assembly.py). It also routes questions and has an agent-scoped semantic cache (Architecture). If lane D beats lane A, you've learned that the system beat the baseline. You haven't learned that the AST check did it.

For lane B, NLQueries exposes its policy as a plain function, evaluate(sql, dialect), which returns a PolicyDecision carrying allowed, a tuple of reasons, the policy_version and a SHA-256 digest of the exact bytes evaluated. It never raises: unparseable SQL is refused (sql_policy.py). That makes it a drop-in replacement for the crude stand-in above. It's a policy check only, so keep your schema check separate.

For lane D, the Python entry point takes an explicit execution policy. ExecutionPolicy.generate_only() produces SQL without executing it, and the docstring calls it "the default for evaluation." There's one documented exception: literal grounding may read stored values of compared columns through the agent's connector, and setting NLQ_LITERAL_GROUNDING=false gives you a mode that opens no connector at all (execution.py). For controlled lanes, switch grounding and the cache off, and record that you did.

# Lane B and lane D capture — sketch against NLQueries Core 0.4.1 (commit 2fbf276).
# Exercised only with a scripted model and a synthetic fixture agent; no provider was called.
# Assumes nlqueries-core 0.4.1 is installed and an agent ("shop_eval") is already
# configured with a knowledge base and connector. Run with NLQ_LITERAL_GROUNDING=false.
from nlqueries.sql_policy import evaluate
from nlqueries.execution import ExecutionPolicy
from nlqueries.orchestrator.sync_runner import run_query_sync

def lane_b(frozen_sql: str) -> dict:
    decision = evaluate(frozen_sql, "sqlite")
    return {"allowed": decision.allowed, "reasons": list(decision.reasons),
            "policy_version": decision.policy_version, "digest": decision.sql_digest}

def lane_d(question: str) -> dict:
    r = run_query_sync(question, "shop_eval", dialect="sqlite",
                       execution=ExecutionPolicy.generate_only(),
                       use_cache=False, explain=True)
    return {"sql": r.sql, "sql_is_valid": r.sql_is_valid,
            "execution_mode": r.execution_mode, "from_cache": r.from_cache,
            "provenance": r.provenance}   # route, capsules, KB parts, timings

shop_eval is a placeholder for your own agent ID. These two functions were exercised against Core 0.4.1 with Python 3.11.16 and sqlglot 30.24.0, using a scripted model in place of a provider and a synthetic agent built on the fixture above. Given the reference SQL, lane_b returned allowed=True with no reasons, policy version 6 and a digest. lane_d returned the scripted statement as a preview with sql_is_valid=True, execution_mode='generate_only' and from_cache=False, and nothing was executed. That confirms the field names and the preview path. It says nothing about what a real model would generate, and component checks like this don't establish a working deployment or a measured advantage for either lane.

The result object's sql_is_valid field exists because, as a source comment explains, a caller could otherwise only infer validity from sql_result is None, which is also what a generation-only run and a rejected statement look like (sync_runner.py). That's precisely the ambiguity a scoring harness has to avoid, so log the field explicitly. explain=True populates provenance, including the capsules and KB parts injected and the cache hit or miss, which is how you'll later attribute lane D differences to context rather than to policy.

Pin the parser, too. Core 0.4.1 declares only a floor, sqlglot>=23.0 (pyproject.toml), so the sqlglot version in a given environment comes from whatever resolved at install time. The two runs above already differ: the harness used 30.12.0 and the Core check used 30.24.0. Because a policy that judges parse trees depends on how the parser reads each statement, record the sqlglot version and policy_version with every lane B result, and don't compare lanes run under different parser versions.

Never weaken the database to make a baseline

It's tempting to give the raw lane a permissive login, so that "raw" really means unguarded and the contrast looks sharper. Don't. A raw baseline that can write to your database isn't a baseline, it's an incident waiting for a test run. Every lane should execute, if it executes at all, against a restricted synthetic database under the same least-privilege role.

That isn't only caution. It also keeps the measurement honest. The hardening guide points out that even a PostgreSQL read-only transaction still permits advisory locks, pg_sleep, file-reading functions if the role holds the privilege, and SELECT on anything the role can reach. In its words, "read-only is not the same as least-privilege" (Hardening the database NLQueries reads). If lanes run under different grants, some of what looks like a policy effect is really a permissions effect, and you've confounded the comparison you were trying to make.

Build a workload that keeps the labels independent

The fixture shows the shape. A real workload needs volume and variety, and above all labels written before anyone sees model output. For each question, record three things independently: the correct answer, the database objects the question legitimately needs, and whether the requested action is allowed. Someone other than the person tuning prompts should adjudicate the answers.

Three families of cases earn their place. Missing identifiers: questions that tempt the model to invent a column or table, which should fail schema validity. Denied actions: requests to delete, update or read a table outside scope, which should be refused by policy and never reach the answer column. Valid wrong aggregates: the dangerous middle, including missing status filters, inclusive date boundaries, fan-out joins, NULL handling and tenant predicates left out. Variants like removing the date or tenant, naming a missing column, or changing one amount before repeating a question all belong here.

Score answers by comparing result multisets, preserving duplicates, NULLs and integer currency, unless the question asks for an order. Defog's SQL-Eval follows a similar pattern: it runs both the gold query and the generated query, compares the two result sets with an exact and a subset match, and logs those outcomes alongside metrics such as tokens and latency. Whatever you use, freeze the dataset, engine and harness revision before you compare anything. An empty or tiny fixture can make wrong SQL look right, which is exactly why the six-row example above is a smoke test and not a benchmark.

Report with separate denominators

This is where the results go once the runs exist. The structure is fixed now, before any data, so nobody picks the framing after seeing the numbers.

Measure Lane A (raw) Lane B (A's SQL + checks) Lane C (+ repair) Lane D (full system)
Schema-valid rate — — — —
Forbidden-action refusal rate — — — —
False refusals of allowed questions — — — —
Correct answers, all requests in denominator — — — —
Retries, duration, provider-reported tokens — — — —

Reserved. No values until paired runs exist and their raw, redacted logs have been checked.

Two denominator rules do most of the work. End-to-end correctness counts every request, including refusals, timeouts and crashes, because a pipeline that refuses hard questions can look accurate by answering only easy ones. With invented numbers: a lane that answers 12 of 20 questions correctly and refuses 5 scores 12/20, not 12/15. False refusals get their own row, because a policy that blocks everything scores perfectly on forbidden-action refusal.

If you want a single scorecard, weight it explicitly. One possible starting point is answer correctness 35%, forbidden-action refusal 25%, schema validity 20%, and false refusals plus operational cost 20%. These weights are an editorial proposal, not measurements, so adjust them before the runs, not after. Score each item on a 0–4 rubric defined in advance. Unknown cells stay unknown rather than zero, and an incomplete card isn't totalled.

When lane C or D improves on A, read the provenance before you write the sentence. A repair that fixes a schema error is a retry effect. A capsule that supplies the right filter is a context effect. Only a lane A to lane B difference on identical SQL is a validation effect. Plan for repeats as well: run each lane more than once, report the spread, and don't read a gap smaller than that spread as a difference. And keep the residual wrong answers in the write-up: they're the cases where every check passed and the number was still wrong.

What you'll be able to claim afterwards

If you run this properly, you can make bounded, honest statements. Validation refused this share of forbidden statements at this false-refusal cost. The full system produced correct answers on this share of a fixed workload, under these model and context settings. Some wrong answers survived every check. What you can't claim, from any of this, is that AST validation makes answers correct or makes a deployment secure. Structure checks and database permissions are separate safeguards, and neither one reads the business question.

Your next step is concrete. Copy the fixture and the harness, write twenty questions with answer, object and permission labels decided up front, and run lane A once with the outputs stored. Then pass those frozen strings through your real policy, NLQueries' evaluate or whatever you use, for lane B. That single A-to-B comparison on identical SQL will tell you more about what your validator actually does than any end-to-end demo.

References

All posts · RSS feed · Documentation