Wrong Joins, Wrong Grain, Invented Columns: LLM SQL Errors by Type

LLM-generated SQL doesn't fail the way normal software fails. There's no stack trace, no red log line, no timeout. The query runs. The database hands back rows. The number on the dashboard looks like a real number. And it's wrong.

This kind of failure is well documented: a query joins two tables on the wrong key, the database runs it without complaint, and because one customer can have several orders, the join multiplies rows before the totals get added up. The final number comes back badly inflated. No error. No warning. Just a wrong answer sitting in a spreadsheet, waiting for someone to make a decision based on it.

This is a different kind of problem than the hallucination people worry about with chatbots. A chatbot that hallucinates makes up a fact. A model writing SQL doesn't need to make up a fact, it just needs to make up a structural detail: a column name, a join key, a filter value, a grouping choice. Each one sounds plausible. None of them get checked against what the business actually means by "refund" or "customer" or "last quarter." The database only checks two things: does this identifier exist, and do the data types match up. It does not check whether you picked the right table, and it definitely does not check whether your join produces one row per order or three rows per order. Running successfully is close to the weakest possible signal that a query is correct, because almost every wrong query still runs.

Catching these errors starts with knowing what each one looks like on its own. So this piece breaks them down one at a time: made-up schema, wrong joins, wrong grain, and business logic that gets lost in translation.

How the gap between benchmark scores and production reality sets up the taxonomy

Benchmark scores for text-to-SQL models look great. Production tells a much messier story.

Take the Spider 2.0 benchmark, built from real enterprise workflows instead of tidy classroom-style databases. On the older Spider 1.0 benchmark, OpenAI's o1-preview scored 91.2%. The ICLR 2025 study behind Spider 2.0 found the same model scored 21.3%. That's a significant dip: a model going from "looks basically solved" to "wrong more often than right." That's a model going from "looks basically solved" to "wrong more often than right."

Why the cliff? Production schemas are nothing like benchmark schemas. Benchmark databases are small, clean, and well labeled. Real company databases have years of history baked in, columns named things like flag_2, two different tables that both look like they hold "customers," and business rules written down once, in a Confluence page nobody has opened since 2023.

A January 2026 paper by Jin, Choi, Zhu, and Kang went back and checked the reference answers used to grade these benchmarks, and it gets worse from there: a lot of them were wrong. A January 2026 paper by Jin, Choi, Zhu, and Kang went back and checked the reference answers used to grade these benchmarks. Turns out a lot of them were wrong. Annotation error rates hit 52.8% in BIRD Mini-Dev and 62.8% in Spider 2.0-Snow. When the researchers re-graded sixteen open-source agents against corrected answers, relative performance shifted by as much as 31 percentage points, and some models moved up or down as many as nine spots on the leaderboard. That means a chunk of what the field has been calling "solved" was measured against broken yardsticks.

And when researchers dug into why queries fail in the first place, the picture points away from syntax. A 2025 analysis by Shen and colleagues looked at 4,602 incorrect queries across four different systems on Spider and BIRD. Schema-level problems caused over 80% of execution failures. Roughly 81% of all errors traced back to a misunderstanding of the schema or the meaning behind it.

Putting that together shows that the errors that actually show up in production aren't the errors benchmarks were built to catch. That's why it helps to break the errors down by type.

Invented columns and tables: schema hallucination as the most diagnosable failure

The simplest failure to describe: the model asks for something that doesn't exist. It writes customers.lifetime_value when the real column is customers.ltv_cents. It invents a subscriptions table the database never had.

Researchers have given this a name: schema-based hallucination, where the model picks the wrong table or column during the schema-linking step. It happens because LLMs learn by pattern-matching against huge piles of training text. If "revenue" and "products" tend to appear near each other in documentation the model has seen, it assumes they live in the same table. That assumption breaks constantly against real schemas, especially ones shaped by legacy systems, company acquisitions, or naming conventions nobody outside the team would guess. In one dataset of over 50,000 queries, wrong table or column references turned out to be the single most common mistake.

This is actually the friendliest error type on the list. Asking for a column that doesn't exist usually causes the database to throw it back immediately: unknown identifier, query rejected. Loud failure. Easy to catch.

The trouble comes from what happens next. An AI agent that gets an "unknown column" error often doesn't stop to re-read the schema, it just guesses again. And again. Burning through attempts until something resolves, whether or not that something is actually correct. The error was loud, but the fix wasn't smart, it was lucky.

There's a quieter cousin of this problem too: filtering on a value that doesn't exist in the data, like WHERE pet = 'rabbit' when no rabbit has ever been logged in that column. Researchers call this content-based hallucination. Catching it means knowing every possible value a column can hold, which is rarely something you have on hand before the query runs.

Either way, this category has one saving grace: a schema lookup can catch it. The next two categories aren't so simple, because catching them means understanding what the person actually meant to ask.

Wrong joins: structurally valid queries that silently multiply or drop rows

Here the model picks the right tables but connects them the wrong way. It skips a table it should have gone through, joins on the wrong key, or flips a relationship backward.

A common shape: joining customers straight to products using something like c.id = p.customer_id, when no such direct link exists in the schema. The real path runs through orders and order_items. The model sees two tables that look related and invents a join key that looks plausible on the surface, joining them directly anyway.

This is where row multiplication sneaks in. Join orders to order_items before summing revenue, and a one-to-many relationship turns into a fanout: each order line multiplies the order row before the totals get added. "Total revenue by month" comes back overstated. It's a real number, calculated from real rows in the real database. It's just too big.

Mismatched entity keys make this worse. If user_id in one table maps to customer_id in another, and the model doesn't have the context to know they're the same entity, it can end up double-counting or joining the wrong records together.

This category is more dangerous than invented columns for one reason: the number moves in a direction people expect. Revenue looks like it's up. Nobody double-checks a number that already matches their gut feeling, so the flawed query stays in production, quietly wrong every time it runs.

Qu and colleagues describe a related class here too: logic-based hallucination, where every table and column is correct, but the underlying logic contradicts what was actually asked, like flipping "higher than" into "lower than" somewhere inside the SQL. Spotting that kind of quiet inversion without actually running the query and checking results by hand is genuinely hard. A 2025 technique called SQLHD uses nine "metamorphic" tests, systematic tweaks to comparison words, ranges, and even the underlying data, to catch these logic errors without needing a known-correct answer to compare against. It reports F1 scores spanning a wide range, which is solid but still leaves a real share of errors slipping through.

Wrong grain: aggregation at the wrong level of detail

This is where the query gets aggregated at the wrong zoom level: grouped by the wrong dimension, summed before duplicates get removed, or filtered too loosely.

One documented pattern: SQL applies a math function like SUM or AVG, or a comparison like MAX, MIN, or ORDER BY, to a column that was never meant for math, like a unique ID or a status code stored as a number. When that conversion fails silently, the result can silently collapse to a meaningless value. The query still runs. The number still looks like a number. It's just meaningless.

A company's schema includes an is_internal flag that separates staff activity from real customer activity. The model has no way of knowing that flag exists or matters, so it counts every row. Monthly active users comes out inflated by somewhere in the range of 15 to 20%, and nobody notices until someone cross-checks it against a separate analytics tool.

Filters cause a quieter version of the same problem. A WHERE status = 'active' clause runs fine, even if "active" isn't a real value in that column. Filter on a value that doesn't match what's actually stored, and the query returns zero rows, which a dashboard renders as a confident, clean zero, arguably worse than an error message, because a zero looks like an answer instead of a failure.

Other common shapes of this same problem: a join condition that accidentally filters out every row, a GROUP BY on the wrong column, a date range that misses the period someone actually meant, a LIMIT that quietly chops off the results that mattered. In every case, the database is happy. It thinks the number is correct.

This is the most dangerous category on this list, precisely because the output looks reasonable. An analyst who expects revenue to be up sees revenue up, and moves on. Grain errors are also the hardest to catch before the query runs, because catching them means knowing what level of detail the question was asking for, and that's a question about intent, not something written down anywhere in the schema.

Semantic and business-logic errors: when the SQL is correct but answers a different question

This is the trickiest category, because the SQL is fine. The tables are right. Right joins. Right grain. The model just misunderstood what a column means in business terms.

Take a query that calculates total order value using SUM(oi.unit_price * oi.quantity). Sounds right. unit_price might reflect the price at the time the item was listed, while the price the customer actually paid lives somewhere else, say oi.price_paid. The query runs. It returns a number. It probably even passes a quick glance in code review. It's still wrong.

The root problem is missing context. "Active customer" might be defined three different ways across three different teams. A refund pipeline might mark something "closed" at three different points depending on which system you ask. LLMs work off whatever's in the prompt, and if there's no single, clear, machine-readable definition of these terms, the model guesses, and different guesses produce different numbers for what's supposed to be the same metric.

The 2025 Shen study found semantic misunderstanding, where the model reads the schema fine but misreads the actual question, accounted for 36.1% of errors in the BIRD benchmark specifically. And catching these errors after the fact is genuinely hard: a 2025 benchmark called NL2SQL-Bugs, built specifically to test error detection, used a large set of expert-labeled examples across dozens of error types. Even the best-performing detection models only caught a majority of them. That means roughly one in four semantic errors slips past even tools built specifically to catch semantic errors.

The deepest issue here is tribal knowledge. Business rules that live in an engineer's head, an old dashboard, or a Confluence page are invisible to a model that's only ever seen the schema. Without that knowledge, the model picks the column that looks right instead of the column that is right. In a sense, every error type covered so far comes from the same root cause: a failure to pull business meaning out of raw schema structure. Semantic errors just make that failure obvious at the business-rule level instead of hiding it in the schema-linking step.

What the benchmark and detection research implies about how often these errors slip through

None of the detection tools built so far close the gap completely.

The SQLHD metamorphic testing method mentioned earlier catches schema and logic errors with F1 scores spanning a wide range, without needing a known-correct query to compare against. That's genuinely useful, and reportedly better than having a model grade its own work. It still leaves a real slice of errors undetected.

A separate study from the Cube research team, run by Rumiantsau and Fokeev, tested three leading LLMs against 100 natural-language questions over a retail dataset. Without any written documentation about what the business terms meant, accuracy landed somewhere between 45.5% and 50.5%. All three models performed about the same, statistically speaking, and swapping one frontier model for another didn't fix the underlying problem.

Then they added a short document, about 4 kilobytes of hand-written markdown, spelling out what each metric meant and how to handle ambiguous cases. Accuracy jumped by a substantial margin, landing between 67.7% and 68.7%. And again, the three models performed about the same as each other once that document was in place. The researchers found that writing down business meaning explicitly fixes the dominant category of error because it changes what question the model is actually being asked to answer.

On the high end, one benchmark called Arcwise-Plat-Full, which fixed known errors in its questions and reference queries, saw a model called ReViSQL-235B-A22B hit 93.78% execution accuracy, edging past a human-performance baseline of 92.96% measured on BIRD. Separately, ad-hoc analytics queries where the schema (including foreign key relationships) was handed to GPT-4o directly hit a 94% first-try success rate. Both numbers look great. Both come from clean, well-documented conditions that don't look much like a real production database.

The honest takeaway: even in the best case, there's a leftover error rate. Multiplying that by the volume of queries running through a real company every day adds up to a steady drip of wrong numbers making their way into real decisions.

Why production schemas make every error type worse than benchmarks suggest

Benchmark schemas are built to be read easily: small, tidy, documented. Production schemas are the opposite of all three.

Each error type gets worse against a messy real-world schema, for its own specific reason:

• Invented columns and tables: legacy systems and old acquisitions bring naming conventions nobody could pattern-match against, so the model's training-data guesswork fails constantly.
• Wrong joins: nullable foreign keys, deprecated tables that never got dropped, and join paths that only make sense if you remember a migration from years ago.
• Wrong grain: business logic baked directly into how tables were designed, plus columns like customer_id that quietly mean three different things depending on which system they came from.
• Semantic errors: definitions and rules that live only in a Confluence page, a dashboard, or an engineer's memory, invisible to a model that's only ever seen the schema.

The further a schema drifts from anything the model has seen in training, the more likely it writes something that runs cleanly and means nothing. And the drop-off isn't gradual. Remember that fall from 91.2% to 21.3% on Spider 2.0. That's what happens when real-world messiness gets introduced. None of these four error types is a theoretical worry. Each one has a specific, predictable schema condition in production that sets it off.

Practical detection approach for each error type before queries reach the database

Catching these errors early means splitting checks into two buckets: before the query runs, and after.

Pre-execution checks are cheap and fast, because they don't require scanning a single row of real data:

• Invented columns and tables: check every table and column name against the actual schema before the query ever touches the database. This is the easiest check to build and the highest-value one, since it stops the most common error type cold.
• Wrong joins: check that every join key referenced actually matches a real foreign key relationship in the schema, instead of just matching by name or by guesswork.
• Wrong grain: harder to catch before execution, since it depends on understanding what level of detail the question intended, something the schema alone can't tell you. This is where documenting expected grouping levels and known aggregation pitfalls for key tables pays off.
• Semantic errors: also hard to catch structurally, since the SQL is often technically fine. The clearest fix isn't a smarter check, it's a written, canonical definition of business terms (what counts as "active," how refunds get tracked, which price field is the real one) that the model can reference instead of guess.

Post-execution checks matter too, but they catch different things: results with zero rows, wildly duplicated counts, numbers that don't match a known baseline. None of these substitute for stopping bad SQL before it runs. But together, a pre-execution schema check and a clear, written source of business definitions close off the two most common paths these errors take, before a wrong number ever reaches someone's dashboard.