Question

Text-to-SQL Is Not a Prompting Problem — 7 Ways an LLM Silently Returns the Wrong Number

A text-to-SQL feature that throws a syntax error is harmless. Somebody sees a red box, files a ticket, and you fix it.

The one that will hurt you is the query that parses, runs in 40 milliseconds, and returns 1,284,391.22 — a number that is confidently, invisibly wrong. It goes into a slide. Somebody makes a decision with it. Nobody finds out for a quarter, and when they do, the trust you lose isn't in the query — it's in the whole product.

This is why "natural language to SQL" keeps looking solved in demos and keeps failing in deployments. The demo database has eight tables with honest names. Yours has four hundred, three generations of naming conventions, a status column with seven magic integers, and a deleted_at that half your queries forget.

The benchmark numbers say the same thing, if you read them carefully. BIRD — 12,751 question-SQL pairs over 95 real databases totalling 33.4 GB across more than 37 professional domains — measures execution accuracy: does the generated query actually run and return the right rows? BIRD's own human baseline, established by data engineers and database students, is 92.96. At the time of writing, the top entry on its main leaderboard sits at 81.95 on the test set. Not a rounding error — a double-digit gap, on a benchmark specifically built because the older academic sets were too clean to be predictive.

I teach AI engineering at Cursuri-AI.ro, Eastern Europe's AI education platform, and the pattern I see in text-to-SQL reviews is consistent: teams treat this as a prompting problem and spend weeks tuning instructions, when almost every real failure is a data modeling problem or a permissions problem wearing a prompting costume. Here are the seven that produce silently wrong numbers, and the architecture that contains them.

1. The schema the model never saw

The first failure happens before generation starts.

You cannot put a 400-table schema in the prompt — and even if the context window allows it, you shouldn't: the model's job gets harder, not easier, when the right three tables are buried among 397 wrong ones. So everyone builds schema retrieval, usually embedding-based, and inherits every retrieval failure mode along with it. If the retriever hands the model orders but not order_line_items, the model will not error out. It will answer your revenue question from the table it was given, using a column that means something adjacent, and produce a number.

Three things make this worse than ordinary RAG failure:

• Abbreviated column names. cst_stat_cd carries no semantics. Nothing in the schema tells the model that 3 means "churned."
• Near-duplicate tables. users, users_v2, dim_user. One is live, one is a migration artifact, one is the warehouse copy that lags by six hours. All three embed almost identically.
• The join path is invisible. Foreign keys tell you that two tables relate. They don't tell you which of the four available paths between customers and revenue is the one your finance team considers correct.

The fix isn't a better retriever. It's not giving the model the raw schema at all — more on that below.

2. The join that quietly multiplies your rows

This is the single most common source of a plausible wrong number, and it's the one an LLM is structurally bad at catching.

Join orders to order_items and every order appears once per line item. Now SUM(orders.total) doesn't sum orders — it sums each order once per item it contains. A three-item order is counted three times. The query is syntactically perfect. The result is inflated by a factor that varies with your product mix, so it doesn't even look consistently wrong.

-- Looks right. Is wrong.
SELECT c.region, SUM(o.total_amount) AS revenue
FROM customers c
JOIN orders o        ON o.customer_id = c.id
JOIN order_items oi  ON oi.order_id   = o.id     -- fan-out
WHERE oi.product_category = 'hardware'
GROUP BY c.region;

A human who has been burned by this reaches for SUM(DISTINCT ...) — which is also wrong — or restructures with a subquery. The model has no scar tissue. It joins because the question mentioned a product category, and the category lives on the line item.

The generalized rule: any join whose cardinality is 1:N invalidates aggregates on the "1" side. Nothing in the schema DDL states cardinality, so nothing in the prompt does either.

3. NULL semantics, which nobody in the conversation is thinking about

Three-valued logic produces wrong answers that never look wrong:

• COUNT(column) skips NULLs; COUNT(*) doesn't. "How many customers have a phone number" and "how many customers are there" differ by one character in the query.
• AVG(discount) averages only rows where discount IS NOT NULL. If NULL means "no discount," your average discount is systematically overstated — by exactly the share of full-price orders.
• WHERE region NOT IN (SELECT region FROM excluded) returns zero rows if excluded contains a single NULL. Not an error. An empty result set, which reads as "there are none" to whoever asked.

The model doesn't know whether NULL means "zero," "unknown," or "not applicable" in your schema, because your schema doesn't say. Only your data dictionary does — assuming you have one.

4. Business definitions that live in people's heads

Ask five people at your company what "active user" means and you will get three definitions and two arguments.

The LLM will get one: the one that's most common in its training data, silently applied to your business. Same for "revenue" (gross? net of refunds? recognized or booked?), "churn" (thirty days? ninety? cancellation event or inactivity?), and "new customer" (first order? first paid order? first non-refunded paid order?).

This isn't a hallucination in the usual sense. The model is producing a reasonable answer to an underspecified question. The problem is that nothing surfaces the ambiguity — the user asked in English, got a number in return, and no part of that exchange flagged that a contested definition was chosen for them.

5. Time zones and the boundary problem

Timestamps produce a whole family of off-by-a-day errors:

• timestamp vs timestamptz — a column without a time zone means whatever the session's setting is, and the answer to "how many orders yesterday" changes depending on which one you got.
• Whose day? The user's local day, the server's UTC day, or the finance team's fiscal day that starts at 03:00?
• Interval boundaries. BETWEEN '2026-08-01' AND '2026-08-31' silently excludes almost all of August 31st, because the date literal becomes midnight. Half-open intervals (>= start AND < next_start) are correct; the model uses BETWEEN because that's what's idiomatic in the corpus.

Every one of these produces a number that's nearly right. Nearly right is the worst possible outcome, because it survives the sanity check.

6. Soft deletes and the tenant scope nobody mentioned

Your application code has never once written SELECT * FROM orders without AND deleted_at IS NULL AND tenant_id = $1. That predicate lives in your ORM's default scope, in a base repository class, in a Rails default_scope — somewhere the model cannot see.

Generated SQL has no such default. It queries the table.

The consequences run in both directions, and the second one is the serious one:

• Wrong numbers: soft-deleted rows inflate counts and totals.
• Cross-tenant leakage: in a multi-tenant database, a query without a tenant predicate returns other customers' data. If your text-to-SQL feature is user-facing, that is a data breach with a natural-language interface on top of it.

Row-level security in the database is a genuine mitigation here, and unlike a prompt instruction it can't be talked out of. But RLS you configured for your application's connection does nothing if the text-to-SQL feature connects as a different, more privileged role — which, in most implementations I've reviewed, it does.

7. Excessive agency: the query that writes, and the query that takes the database down

Two failures, one root cause: the feature was given more capability than the task requires.

The query that writes. Given a database connection with write permissions, a model can be talked into using them. Not usually by accident — usually through content. A user asks a question about a support ticket, and the ticket body contains text engineered to look like an instruction. This is OWASP's LLM01:2025 Prompt Injection meeting LLM06:2025 Excessive Agency, and no amount of "you may only generate SELECT statements" in a system prompt is a control. It's a request.

The query that takes the database down. A cross join on two large tables, an unbounded scan, a GROUP BY over a billion rows. No malice required; the model simply has no notion of what your hardware can absorb. In PostgreSQL, statement_timeout defaults to 0 — the timeout is disabled — so out of the box there is nothing at all standing between a generated query and an unbounded execution.

Both of these are solved in the same place, and it isn't the prompt.

The architecture: don't let the model near the database

Everything above collapses into one design principle: the model's output is untrusted input to a system that must be safe regardless of what the model produces. OWASP calls the general case LLM05:2025 Improper Output Handling — treating model output as if it had been validated. Generated SQL executed on a privileged connection is the textbook instance.

Five layers, cheapest first.

Layer 1 — a curated semantic layer, not the raw schema

Stop exposing tables. Build a small set of views that encode your business definitions and your default scopes:

CREATE VIEW analytics.v_orders AS
SELECT
    o.id,
    o.tenant_id,
    o.customer_id,
    o.total_amount            AS revenue_gross,
    o.total_amount - COALESCE(r.refunded, 0) AS revenue_net,
    (o.placed_at AT TIME ZONE 'UTC')::date   AS placed_date_utc,
    o.status IN (2, 5)        AS is_completed
FROM orders o
LEFT JOIN (
    SELECT order_id, SUM(amount) AS refunded FROM refunds GROUP BY order_id
) r ON r.order_id = o.id
WHERE o.deleted_at IS NULL;

COMMENT ON COLUMN analytics.v_orders.revenue_net IS
  'Gross order total minus all refunds. Finance-approved revenue definition.';

That one view neutralizes failure modes 1, 3, 4, 5, and half of 6. The soft delete is baked in. revenue_net has a single meaning. The date is explicitly UTC. is_completed replaces magic integers with a boolean. And COMMENT ON is not decoration — it's schema metadata your prompt builder can read programmatically and feed to the model as documentation that cannot drift from the schema, because it lives in the schema.

Pre-aggregating the common fan-out joins into the view also removes failure mode 2 from the model's reach entirely. This is dull, unglamorous data modeling work, and it beats every prompt engineering technique that exists. If your warehouse isn't in a state where this is feasible, that gap is the actual project — it's the ground we cover in Data Engineering for AI, and it's the prerequisite that most text-to-SQL efforts skip.

Layer 2 — a role that physically cannot write

Not a prompt instruction. A grant.

CREATE ROLE nl_query_role LOGIN PASSWORD '...';

REVOKE ALL ON ALL TABLES IN SCHEMA public FROM nl_query_role;
GRANT USAGE ON SCHEMA analytics TO nl_query_role;
GRANT SELECT ON ALL TABLES IN SCHEMA analytics TO nl_query_role;

ALTER ROLE nl_query_role SET statement_timeout = '10s';
ALTER ROLE nl_query_role SET idle_in_transaction_session_timeout = '30s';
ALTER ROLE nl_query_role SET default_transaction_read_only = on;

Three of those ALTER ROLE lines exist because PostgreSQL's defaults are permissive: statement_timeout and idle_in_transaction_session_timeout both default to 0 (disabled). Setting them per-role rather than in postgresql.conf is deliberate — the PostgreSQL documentation explicitly notes that setting statement_timeout in postgresql.conf "is not recommended because it would affect all sessions."

Then run every generated query inside an explicitly read-only transaction:

BEGIN READ ONLY;
  -- generated SELECT here
COMMIT;

The PostgreSQL docs spell out what that buys you: in a read-only transaction, "INSERT, UPDATE, DELETE, MERGE, and COPY FROM if the table they would write to is not a temporary table; all CREATE, ALTER, and DROP commands; COMMENT, GRANT, REVOKE, TRUNCATE; and EXPLAIN ANALYZE and EXECUTE if the command they would execute is among those listed" are all disallowed.

Read the same docs' caveat too, because it matters for how you reason about this: "This is a high-level notion of read-only that does not prevent all writes to disk." A read-only transaction is a correctness guardrail layered on top of a least-privilege role. It is not a replacement for one. Use both.

Layer 3 — a pre-execution gate

Before the query reaches the database, parse it (with a real SQL parser — sqlglot, pglast, whatever fits your stack — never a regex) and reject anything that isn't a single SELECT statement. Refuse multiple statements. Refuse CTEs containing DML. Refuse references to objects outside the allowlisted schema.

State the purpose honestly: this layer is defense in depth, not the security boundary. Parser-based filters have been bypassed before and will be again. Layer 2 is the boundary. Layer 3 catches the boring 99% earlier, with a better error message.

Layer 4 — cost check before execution

EXPLAIN without ANALYZE produces a plan and an estimated cost without running the query. Gate on it:

plan = db.execute(f"EXPLAIN (FORMAT JSON) {sql}").scalar()
est_cost = plan[0]["Plan"]["Total Cost"]
est_rows = plan[0]["Plan"]["Plan Rows"]

if est_cost > COST_CEILING or est_rows > ROW_CEILING:
    raise QueryTooExpensive(est_cost, est_rows)

Estimates are estimates, and a bad one will occasionally let something through — which is why statement_timeout from Layer 2 is still doing work behind this. Belt and braces. Also inject a hard LIMIT into every generated query: the model's LIMIT 1000 is a suggestion; yours is a wrapper.

Layer 5 — evaluate on execution, not on similarity

You cannot ship this without a regression suite, and the metric matters. Comparing generated SQL to reference SQL by string or embedding similarity is nearly meaningless — two queries can look almost identical and return different numbers, or look completely different and be equivalent.

Measure what BIRD measures: execution accuracy. Build 50–200 question/expected-result pairs from real questions your users actually ask, run the generated SQL against a frozen snapshot, and compare result sets. Every failure mode in this article becomes a test case:

• a question whose correct answer requires a 1:N join (fan-out)
• one where NULLs must be counted as zero
• one crossing a month boundary in a non-UTC time zone
• one where the honest answer is "that's ambiguous, which definition do you mean?"

That last category is the one teams forget, and it's how you find out whether your system knows the difference between not knowing and guessing. Building that harness — and knowing what a regression actually looks like — is the discipline behind LLM Evaluation and Testing; the adversarial half, including how injected content reaches a query generator in the first place, sits in AI Security: Defending LLM Applications.

The UX layer nobody wants to build

One more thing, and it's not technical.

Always show the SQL. Not hidden behind a "details" toggle — visible, next to the number, with the row count and the timestamp of the data snapshot. A user who can see WHERE placed_date_utc >= '2026-08-01' can catch a time-zone error that no automated check would flag. A user handed only 1,284,391.22 cannot.

The instinct to hide the query is strong, because showing it makes the feature feel less magical. It also makes it honest. The realistic destination for text-to-SQL in most organizations is not "everyone writes their own queries in English" — it's a very good first draft that a competent human confirms. Products that position it that way ship successfully. Products that promise the number and hide the query eventually produce a wrong one that reaches a board deck.

That framing also determines who you should be training. The highest-leverage move is usually not a better model — it's analysts who can read a generated query, spot a fan-out, and say "that's wrong, and here's why." That literacy is what No-Code Data Analysis with AI is built to produce, and it pairs with the engineering side far better than either does alone.

Summary

#	Failure mode	Primary fix
1	Schema the model never saw	Curated views + COMMENT ON metadata
2	1:N join fan-out inflating aggregates	Pre-aggregate in the semantic layer
3	NULL semantics (COUNT, AVG, NOT IN)	Explicit COALESCE in views; documented NULL meaning
4	Ambiguous business definitions	One approved definition per metric, in the view
5	Time zones and interval boundaries	Explicit AT TIME ZONE columns; half-open intervals
6	Soft deletes and tenant scope	Predicates baked into views + row-level security
7	Writes and runaway queries	Least-privilege role, BEGIN READ ONLY, statement_timeout, EXPLAIN gate

The through-line: every one of these is fixed in the schema, in the grants, or in the evaluation harness. None of them is fixed in the prompt. Text-to-SQL is a data engineering project with a language model attached — and teams that build it in that order are the ones whose numbers you can trust.

---

Sources: BIRD benchmark · OWASP Top 10 for LLM Applications · PostgreSQL — SET TRANSACTION · PostgreSQL — Client Connection Defaults