Before using totals from AI-written SQL, check how many times each source fact appears after the joins. Define what one output row represents, inspect the relationships between tables and compare the result with a small example whose answer is known. Successful execution does not establish that the query counts the intended events once.
The risk is especially easy to miss in a report combining several kinds of activity: tickets with work logs and tags, accounts with contacts and transactions, or projects with tasks and updates. Each relationship may look reasonable on its own. Combining them can multiply rows before the final sum or count.
Our recommendation is to review the row grain alongside the SQL. Grain means what one row represents: a ticket, a work-log entry or a ticket-tag association, for example. The worked example below is fictional. Its SQL and arithmetic were checked locally in SQLite; no AI SQL product or production database was tested.
Execution is one check, not the answer contract
The Spider 2.0 paper, published at ICLR 2025, studies enterprise text-to-SQL workflows involving database metadata, documentation, codebases and multiple query steps. Its evaluation checks outputs against expected results. Its error analysis distinguishes problems such as joins, calculations and filtering from syntax errors.
That is research on the benchmark and systems studied at the time. It does not estimate today’s assistant error rates or establish how often join fanout causes workplace mistakes. We have not replicated the experiments. The practical implication we draw is that “the query ran” and “the returned values answer the request” require different evidence.
For a report owner, the answer contract should include the output grain, included population, reporting period and meaning of each measure. For a developer reviewing generated SQL, it should also identify the source key for each fact and the expected relationship across every join. A familiar column name is not proof that two tables share the intended key.
Understand what a matching join produces
The PostgreSQL 18 table-expression documentation specifies that an inner join produces a row for each matching row on the other side. A left join also preserves unmatched left-side rows with null values on the right. These are database semantics, not AI-specific behavior.
Suppose a ticket has three work logs and two tags. Joining both child tables through the ticket identifier creates every matching work-log/tag combination: six rows. Each work log appears twice, once for each tag. This multiplication is often called fanout.
Grouping the joined rows back to one ticket does not undo the repeated values before aggregation. It returns one output row, but that row’s sum can still include each work log twice. Checking only that the final report has one row per ticket would miss the error.
A fictional ticket with 25 minutes of work
Assume the report should show one row per ticket, with the number of work logs, total recorded minutes and number of tag associations. Ticket 101 has three distinct log identifiers with durations of 10, 10 and 5 minutes. It has two tag associations. Ticket 102 has one tag and no logs. Ticket 103 has one seven-minute log and no tags.
For this example, every stored work-log duration is a known, non-null integer. An absent work log means no recorded work, so the report represents its count and minutes as zero. In another system, missing or null durations might mean unknown information; agree on that distinction before converting values to zero.
This query runs, but its totals for ticket 101 are wrong:
SELECT t.ticket_id, COUNT(w.log_id) AS log_count,
SUM(w.minutes) AS total_minutes
FROM tickets AS t
LEFT JOIN worklogs AS w ON w.ticket_id = t.ticket_id
LEFT JOIN ticket_tags AS g ON g.ticket_id = t.ticket_id
GROUP BY t.ticket_id
ORDER BY t.ticket_id;The local fixture returned six logs and 50 minutes for ticket 101. The underlying records contain three logs and 25 minutes. Nothing in the SQL syntax requires the database to warn that the selected reporting measure has been multiplied.
Bring both child tables to the output grain
For this report, one repair is to summarize each child table by ticket before joining the summaries to the ticket table. Each summary then contributes at most one matching row per ticket.
WITH work_by_ticket AS (
SELECT ticket_id, COUNT(*) AS log_count,
SUM(minutes) AS total_minutes
FROM worklogs
GROUP BY ticket_id
), tags_by_ticket AS (
SELECT ticket_id, COUNT(*) AS tag_count
FROM ticket_tags
GROUP BY ticket_id
)
SELECT t.ticket_id,
COALESCE(w.log_count, 0) AS log_count,
COALESCE(w.total_minutes, 0) AS total_minutes,
COALESCE(g.tag_count, 0) AS tag_count
FROM tickets AS t
LEFT JOIN work_by_ticket AS w ON w.ticket_id = t.ticket_id
LEFT JOIN tags_by_ticket AS g ON g.ticket_id = t.ticket_id
ORDER BY t.ticket_id;The corrected local result was:
| Ticket | Work logs | Minutes | Tag associations |
|---|---|---|---|
| 101 | 3 | 25 | 2 |
| 102 | 0 | 0 | 1 |
| 103 | 1 | 7 | 0 |
This is a repair for the stated measures, not a universal instruction to aggregate every table immediately. A report asking for minutes by tag needs an allocation rule: should a ticket’s minutes count under each tag, be split across tags or belong to one designated tag? The database cannot supply that business decision simply by changing the join.
Similarly, put time and population filters in the appropriate part of the calculation. Summarizing all historical logs and later filtering only ticket creation dates would answer a different question from work recorded during the reporting period.
Do not replace entity identity with distinct values
A tempting repair is SUM(DISTINCT w.minutes). In the fixture, that returned 15 for ticket 101 because it counted the value 10 once and the value 5 once. The two ten-minute logs are separate events and both belong in the total.
For an event count, counting distinct valid event identifiers can be appropriate. That does not make distinct durations a sound basis for summing work. Review what makes each fact unique, rather than applying DISTINCT until the output looks plausible.
Google Cloud’s Looker explanation of symmetric aggregates illustrates repeated measures after joins and describes its product mechanism for counting facts at the correct grain. It also states that the mechanism depends on a unique primary key and the correct modeled join relationship. This is vendor engineering guidance, not independent testing of a particular Looker model or AI assistant.
If your reporting platform already supplies such controls, inspect its actual model and generated calculation. Do not assume arbitrary SQL pasted into another tool receives the same protection. Nor should you remove an unfamiliar generated expression solely to make the query shorter without understanding what it preserves.
Change the fixture to test the measure’s behavior
A useful review tests what should remain unchanged as well as what should increase. We checked these changes locally with Python’s SQLite interface, using SQLite 3.53.1:
- Adding a third tag to ticket 101 changed the naive query to nine logs and 75 minutes. The corrected query retained three logs and 25 minutes while increasing the tag count to three.
- Adding another ten-minute work log after that increased the corrected output to four logs and 35 minutes. The equal duration remained a separate event.
- The original tickets without logs or without tags remained in the corrected output, with zeros only where the example’s reporting definition required them.
Those checks establish behavior for this small fixture and SQL subset. They do not establish performance, permissions, dialect compatibility or correctness on another organization’s data. Test the query on the target database with appropriate access and execution limits before relying on it operationally.
For recurring reports, retain the measure definitions, query revision and a small set of representative cases. Check keys and relationship assumptions when the schema or data model changes. Our guide to defining an AI-generated chart’s comparison covers the later step of making the report’s visual and narrative claims match its values.
Start with one important measure in one generated query. Trace its source records through each join, verify the expected multiplicity and compare the aggregate with an independently computed total for the same scope. Once that relationship is clear, the review can move from whether the SQL executes to whether its result deserves to inform the business decision.