Why Correct SQL Gives Wrong Answers

The query parsed. It executed in under a second. It returned a number with no warnings, no nulls, and no errors — and the number was wrong.

Not wrong in the way a broken query is wrong. Wrong in the way a confident answer to a slightly different question is wrong. The monthly revenue figure was 3% high because one join multiplied rows. The active-customer count was 2,000 short because a filter moved from ON to WHERE. The quarter came in under plan because the last day of the quarter never made it into the range.

A valid query proves the SQL is well-formed and executable. It says nothing about whether the result means what your business means by the question. Those are separate properties, and only the first one has an automatic check. Wrong joins, misplaced filters, and date boundaries are the three places where meaning changes without an error being raised. They are also why correct SQL produces wrong results that nobody flags.

A valid query is a claim about structure, not meaning

Parsing and execution are structural checks. The parser asks whether the tokens form a legal statement against this schema. The engine asks whether the operations can run against the data present. Neither asks whether the resulting rows represent the business concept that was requested.

The academic literature has a name for the gap. In Brass and Goldberg's taxonomy of SQL semantic errors, a semantic error is a legal query that does not, or does not always, produce the intended result — and is therefore incorrect for the task, despite being valid SQL. Their catalogue runs through missing and erroneous join conditions, redundant or omitted grouping, and incorrect handling of NULL values. Every entry is a query that runs.

This is not a rare corner. A static-analysis study of 191,994 production queries found that roughly 19% carried at least one semantic defect. Those queries executed. They returned data. Nearly one in five had a meaning problem.

The practical consequence is uncomfortable: an empty error log is not a correctness report. It is a syntax and runtime report. SQL gives wrong results in ways the error log never sees.

Joins: when one row becomes three

Every table has a grain — a statement of what one row represents. One row per order. One row per order line item. One row per customer per day. Joins combine tables, and when the two sides sit at different grains, the join changes the grain of the result set. That is not a malfunction; it is what joins do. The problem starts when an aggregate is computed over a result set whose grain no longer matches the question.

Fan-out multiplies the measure

Consider orders with one row per order and order_items with one row per line item. One order has three line items.

The join multiplied the order row once per line item, and the sum counted the same 100 three times. The true answer is 100. The query returned 300, and nothing in the execution path objected, because the query did exactly what it was asked to do.

This is a fan-out, and the dbt join documentation describes the same mechanics plainly: fan-out joins and chasm joins both produce more output rows than the source, and aggregating over the inflated set multiplies the values. Snowflake's engineering team documents the same behavior in its guidance on joins across star schemas, noting that an incorrect join setup leads to undercounting, data loss, or inflated results.

The related shape is the chasm trap: two fact tables that both join to a shared dimension. Each row from one fact pairs with every matching row from the other, and the result behaves like a Cartesian product across the two measures — fan-out with two multiplication paths instead of one.

What makes fan-out dangerous is that the wrong number is plausible. It is inflated by an integer factor — 2×, 3×, sometimes 4× — but if the query also filters to a subset, the final figure can land close enough to expectation that nobody questions it. There is a total that is exactly three times too large, and it looks like a good month.

The diagnostic signature of fan-out is a row count. Compare the row count of the joined result against the row count of the table whose grain you intended to preserve. If the joined result is larger, every aggregate computed over it is inflated.

That test is cheap, and it catches the failure class that no syntax check will ever see.

Filters: the condition that deleted your rows

Outer joins exist to preserve rows that have no match. A LEFT JOIN keeps every row from the left table and adds matching columns from the right where they exist, filling in NULLs where they do not. That is the contract.

The contract breaks when the predicate moves. Place a condition on the right-hand table in the WHERE clause, and the query now requires that column to be non-null — which is precisely what the outer join was preserving. Every NULL-extended row is eliminated, and the LEFT JOIN has become an inner join without the word changing in the query text. Move the same predicate into the ON clause and the rows survive. Both versions are valid SQL. They return different populations.

This mechanism deserves separate attention from fan-out because it fails by subtraction. Fan-out gives you a number that is too big. A misplaced filter gives you a number that is too small, because rows you intended to count are simply gone. Row totals, customer counts, and coverage metrics all drift downward, and a low number is much easier to rationalize than a high one. A soft month is read as a soft month. It is rarely read as a bug.

For any outer join, the review question is not whether the query runs. It is which side's population this query guarantees to preserve — and whether the filters are placed so that guarantee actually holds.

Dates: where a boundary becomes a business number

Date logic fails differently again, because the errors happen at the edges of a period rather than across the whole result set. The subtlest of them is a single character.

BETWEEN is a closed interval in essentially every SQL dialect. Writing x BETWEEN a AND b means x >= a AND x <= b. DuckDB's documentation states the expansion explicitly — the upper bound is included, not excluded.

The trap appears when the column holds a timestamp and the bounds are bare dates. A date literal resolves to midnight, so ts BETWEEN '2026-08-01' AND '2026-08-31' includes records up to exactly 2026-08-31 00:00:00. Everything that happened during the final day of August is excluded. The query is valid. The month is short by a day, and the discrepancy is small enough to look like normal variance.

The convention that avoids this is the half-open interval: ts >= period_start AND ts < next_period_start. One inclusive lower bound, one exclusive upper bound, and no ambiguity about where a record belongs. Adjacent periods cannot overlap, and no record can fall into a gap.

That fix handles the syntax trap. Four other boundary problems in the same family are less well covered, and each one changes a business number rather than a row count:

• Timezone conversion. If timestamps are stored in UTC and the business reports a local calendar day, records near midnight land in the wrong day. The conversion has to happen at the boundary, not after aggregation.

• Daylight saving time. Local days are 23 or 25 hours long twice a year in DST-observing zones, so adding 24 hours to a boundary is not the same as advancing one local day.

• Fiscal periods. Fiscal months, quarters, and years do not align to calendar boundaries, so hard-coded calendar dates attribute transactions to the wrong reporting period even though every individual row is correct.

• Partial-period comparison. Comparing a month-to-date figure against a full prior month produces a growth rate that is arithmetic rather than meaningful.

"Through the end of the month" is a business statement, not a SQL condition. It has to be translated into an interval convention — start inclusive, end exclusive — before it becomes a predicate.

Why SQL gets worse when a model writes it

These mechanisms are not new. Engineers have made join-grain mistakes since the first star schema. What has changed is volume and invisibility.

When a model generates SQL from a business question, it makes all three decisions — join paths, filter placement, boundary conventions — on every question, at a rate no human review process was designed for. And it makes them plausibly. The output looks like SQL a careful analyst would write, because it is. It is also written without knowing that this organization defines revenue net of returns, that the fiscal year starts in April, or that fct_orders and stg_orders are not interchangeable.

The benchmark evidence on how quickly this degrades is worth reading carefully. The Spider 2.0 benchmark, published at ICLR 2025, was built from real enterprise SQL workflows rather than clean academic schemas. The authors' agent framework scored 91.2% on Spider 1.0, 73.0% on BIRD, and 21.3% on Spider 2.0.

The detail that matters most is what those numbers measure: execution accuracy. A query that executes and returns the wrong business concept counts the same as one that returns the right answer. Execution accuracy is structurally blind to the entire failure class this article describes — a high score tells you the SQL runs, in the same way a green build tells you the code compiles.

There is a second effect worth naming. Generation is not deterministic. The same question can produce different SQL on different runs, which means the same dashboard can show two different numbers on two different days with nothing in the underlying data changing. A business user sees a dashboard, not a query history.

None of this is a verdict on any particular model. It is a property of the arrangement: generating SQL against raw schema, at question volume, without a governed definition of what the question means.

What actually catches these errors

The useful way to think about validation is as a ladder, ordered by how much evidence of correctness each rung actually provides.

The first two rungs are automatic and catch almost none of the failures described above. The last rung catches all of them, and it depends entirely on whether the business meaning of each term has been written down somewhere a reviewer — or a machine — can check against.

That is the structural fix, and it is a data-modeling task rather than a prompt-engineering one. When governed definitions live with the data — as they do in an AI-native lakehouse that keeps metrics, join paths, and time logic alongside the tables they describe — a question can resolve against the definition of "revenue" rather than against a guess about which column happens to be named closest to it. A natural-language layer such as an Analytics Agent then has something to resolve against, and the reviewer has something to audit.

It is equally important to be clear about what this does not fix. A governed layer will not correct a properly defined metric applied to the wrong period. It will not resolve a business rule that is genuinely contested internally — encoding the ambiguity just makes the argument traceable. It will not settle a question whose grain is ambiguous in the original ask, such as revenue by region where order date and ship date give different answers. And it will not maintain itself when the business changes.

The counterargument: better models, better prompts

The strongest objection to everything above is that this is a transient condition. Models are improving quickly. Schema context injection, retrieval over table metadata, and few-shot grounding all measurably lift first-attempt accuracy. Give it two more generations of capability and the mechanisms described here will be marginal.

Part of that is true. Model quality has improved substantially on exactly this task, and supplying schema context does help — a model that can see column descriptions and sample values makes better choices than one working from column names alone.

The rebuttal is that these are two different problems. Choosing the wrong join path is a semantic decision, not a syntactic one. A model that writes flawless SQL and joins two fact tables through a shared dimension has not failed to write SQL; it has answered a question that the data model never disambiguated. Better generation does not resolve an ambiguity that was never resolved anywhere the model can see. The benchmark pattern supports this reading: Spider 1.0 is close to saturated, Spider 2.0 sits an order of magnitude lower, and the gap between them is on the same underlying task. The variable that moved was schema realism, not model quality.

So the honest position is this. Better models raise the floor on questions whose meaning is already unambiguous. They do not raise the ceiling on questions whose meaning is contested, undocumented, or organization-specific — and those questions are the ones that end up in board decks. The variable that moves the number for those is definition coverage.

How to review a number that valid SQL produced

For anyone responsible for the numbers a platform produces, the mechanisms above suggest a shift in what review is for. Syntax review is largely automated already. The remaining exposure is semantic, and it shows up in questions that are cheap to ask and hard to answer:

• What grain is this result set at, and is that the grain the question implied?

• Which population do these filters guarantee, and which rows did they silently remove?

• Which interval convention defines this period, and does it handle the final day and the timezone correctly?

• Who owns each business definition, where does it live, and is it the version this query used?

• Is an ungoverned but running query a source, or a draft?

None of those questions is exotic. They are, however, questions nobody asks when a query returns a clean result quickly — which is exactly why the failure class survives.

Next steps

If you are auditing a reporting surface — or standing up a natural-language analytics layer — the first artifact worth requesting is not an accuracy figure. It is the definition coverage: which metrics are governed, where those definitions live, who owns each one, and what the system does when a question has no governed answer.

A useful reference to bring into that conversation is the platform architecture overview, which shows how definitions, storage, and consumers are separated — the structural precondition for everything described here.

Valid SQL proves the query ran. It never proved the number was right. Singdata builds the governed layer that makes the difference between the two auditable.