Text-to-SQL Agents: Why Valid SQL Can Still Produce the Wrong Answer

Text-to-SQL translates a question into a database query. A query that parses and runs has passed a technical check; it has not necessarily answered the intended business question.

To evaluate accuracy, check the chosen population, metric, time range, grouping, and execution scope. Then check whether the explanation describes the returned result faithfully.

The query runs, but the rate is wrong

Consider two fictional acquisition channels in the same period:

Channel	Converted visitors	Eligible visitors	Conversion rate
Referral	1	2	50%
Search	9	90	10%

An agent asked for the overall conversion rate might average the two percentages and return 30%. The correct aggregate is 10 conversions divided by 92 eligible visitors, about 10.87%.

Given a table with one row per channel and the columns shown below, this query calculates the combined rate:

SELECT
  100.0 * SUM(converted_visitors)
    / NULLIF(SUM(eligible_visitors), 0) AS conversion_rate_pct
FROM channel_conversions;

This assumes visitors belong to one channel in the report and the conversion definitions are aligned. If the same visitor appears in several channels, the denominator may need deduplication at visitor grain before aggregation. Correct arithmetic cannot repair the wrong population.

Separate five kinds of correctness

1. Syntax: can the warehouse parse the query?
2. Structure: are the tables, keys, joins, and data types valid?
3. Semantics: is the metric calculated over the intended population and time convention?
4. Authorization: may this user execute this operation and see these results?
5. Communication: does the explanation preserve units, scope, uncertainty, and evidence?

Treat them as separate checks. A model can pass the first two and fail the last three. A database error rate alone is an incomplete quality metric.

Give the agent a smaller query surface

Expose curated views or an approved metric interface when the task is routine reporting. Provide business definitions, supported dimensions, and example questions. Ask for clarification when a term maps to several legitimate metrics.

A semantic layer can support that process, but it must be connected to the execution path. Supplying metric descriptions while letting the agent freely bypass them through raw SQL leaves the definitions advisory.

Some systems intentionally support open-ended SQL investigation. In that case, define allowed datasets, resource budgets, and validation steps. A text check for a leading SELECT is not a sufficient security boundary. Use appropriate query parsing, restricted database credentials, and platform-enforced limits.

Design tests around expected behavior

Build small fixtures with known answers. Include the rate example, duplicate join keys, a one-to-many relationship, null amounts, an empty result, and timestamps around a reporting boundary.

Add questions with equivalent wording and questions that should produce different answers. “Orders created in August” and “orders paid in August” are not interchangeable unless the data and business definition make them so.

For a period with no eligible visitors, specify whether the answer should be unavailable rather than zero. The query above returns null for a zero denominator. The explanation should preserve that distinction.

Test access using at least two users with different scopes. Verify the permitted result set and whether restricted rows can affect exposed aggregates. Keep query results and traces within the same confidentiality boundaries as the underlying data.

Compare results, not identical SQL strings

Different queries can produce the same correct answer. Conversely, the same SQL can become wrong after a definition or source schema changes. Evaluate known outputs on fixtures, inspect query properties that matter, and reconcile representative real requests against an approved calculation.

Record the question, definition version, query, data snapshot, and answer. Re-run the suite when changing models, prompts, tools, joins, or metric definitions.

For related errors, use our AI data-answer diagnosis guide. Discuss an AI analytics implementation with Labs4Change if you need to turn a successful SQL demo into a dependable reporting tool.