Two SQL shapes that stopped a text-to-SQL agent doing arithmetic in prose — ROLLUP and scalar subqueries

Introduction

Our text-to-SQL agent reported a total as roughly 712,000 when the correct figure was roughly 612,000. The SQL it ran was valid, the answer was formatted correctly, and nothing errored. It had fetched ten rows and added them up in the text of its reply, slipping one digit in the hundred-thousands place.

What made this worth chasing was that the system prompt already forbade it. The rule was there, in a section called do the arithmetic in SQL, with a worked wrong example and a worked right one. A rule with examples attached was being broken anyway, and the obvious response (write the rule again, more firmly) had already been tried by whoever wrote the section.

The fix was not a better prohibition. It was two changes to the shape of the queries the agent writes, after which the mistake had nowhere to happen: GROUP BY ROLLUP for totals, and scalar subqueries in place of a JOIN. This article covers both failures, both fixes, and the two other changes that measurement rejected.

The prohibition was already there

The relevant section of the prompt said, in substance: never compute in the answer text. Ratios, percentages, unit conversions, and subtotals all belong in the SELECT clause, and the number you write is the number the query returned.

It carried two examples. One showed a wrong answer computing a per-unit figure in prose from two separately-fetched numbers. The other showed a wrong answer adding a breakdown by hand: 1,951 + 278 = 2,229.

That second example is almost exactly the failure that happened anyway.

This rules out the comfortable explanations: the rule was not missing, not vague, and not buried in a wall of text without an example. The model had been told, shown the wrong version, and shown the right version, and it did the wrong version regardless.

Failure 1: three rows added in prose

The question asked for a nationwide total. A convention elsewhere in the prompt requires that particular total to be reported in two tiers: consolidated subsidiaries first, then the figure including equity-method affiliates.

The agent ran one query:

SELECT company_group, consolidation, stock_count
FROM stock
WHERE fiscal_year = 2026

Ten rows came back. It added them in the reply, and the sum was wrong by one digit.

The agent had done exactly what the two-tier convention required and exactly what the arithmetic rule forbade, because the query it wrote could not satisfy both.

A breakdown query returns the parts and not the whole. To report the second tier the agent needed a number no cell contained, and its options were to run a second query or to add the rows itself. It added the rows.

Reading it as disobedience points at the wrong repair. The rule was not being ignored; it was unsatisfiable in that position, and no amount of restating it changes the position.

GROUP BY ROLLUP: the total arrives as a cell

The agent proposed GROUP BY ROLLUP and I took it. Presto and Trino both support it, so it runs on Athena unchanged:

SELECT COALESCE(consolidation, 'total') AS tier,
       SUM(stock_count) AS stock_count
FROM stock
WHERE fiscal_year = 2026
GROUP BY ROLLUP(consolidation)
ORDER BY 1

tier             stock_count
consolidated         524,000
equity_method         88,000
total                612,000

Both numbers the two-tier answer needs are now cells. The addition is not discouraged; it has stopped existing as a step. The agent quotes two values and moves on.

The prompt change was a worked template in the section describing two-tier answers, plus one sentence keeping the constraint narrow: you may fetch the subtotal and the total as two separate queries if you prefer, and the only forbidden thing is adding the breakdown rows in the reply. Naming one banned move rather than mandating one blessed query leaves the agent room and still closes the failure.

The four questions in our suite that require two-tier answers ran three times each after the change. All twelve passed, and the failing question used the ROLLUP template in all three rounds with a single tool call each time, down from four.

Failure 2: one row joined to three, and revenue tripled

The second failure came from a per-head ratio: one business segment’s revenue for a year, divided by that group’s sales staff at a point in time. Revenue lives in a monthly financials table; staff live in a monthly headcount snapshot.

The agent aggregated revenue correctly to a single row, then joined it to the staff table:

SELECT SUM(r.revenue) / NULLIF(MAX(p.sales_staff), 0)
FROM (
  SELECT company_group, SUM(revenue) AS revenue
  FROM financials
  WHERE fiscal_year = 2025 AND segment = 'construction'
  GROUP BY 1
) r
JOIN headcount p ON r.company_group = p.company_group
WHERE p.fiscal_year = 2025 AND p.month = 3

The group has three companies, so headcount returned three rows. The one revenue row was duplicated against each of them and SUM counted it three times: a figure near 21,000 became a figure near 63,000, and the ratio came out roughly three times too large.

Two details made this one nastier than the first. The staff figure was right, because two of the three companies had zero sales staff, so SUM and MAX agreed; a half-correct answer invites less scrutiny than a wholly wrong one. And it was intermittent: across fourteen observations of that question the failure appeared once, so any single passing run was evidence of nothing.

The prompt already had a fan-out section, and it already prescribed the standard remedy of aggregating both sides in subqueries before joining. As with the arithmetic rule, the guidance existed and the failure happened anyway.

Scalar subqueries: for a single entity, do not JOIN at all

The observation that fixed it is that this query never needed a JOIN. The question is about one group. The numerator is one number, the denominator is one number, and a JOIN is machinery for matching sets:

SELECT ROUND(
  (SELECT SUM(revenue) FROM financials
    WHERE company_group = 'north' AND fiscal_year = 2025 AND segment = 'construction')
  / NULLIF((SELECT SUM(sales_staff) FROM headcount
    WHERE company_group = 'north' AND fiscal_year = 2025 AND month = 3), 0)
, 2) AS revenue_per_head

Each subquery returns exactly one value. There are no rows to duplicate, so fan-out is not avoided here, it is unrepresentable. That is a different guarantee from the subquery-JOIN pattern, which is correct but still depends on the agent aggregating both sides every time.

The rewritten section leads with this shape for single-entity ratios and demotes the JOIN pattern to what it is actually for: comparing or ranking several groups, where a JOIN is genuinely required and the GROUP BY naturally pushes the agent toward aggregating both sides anyway.

The eleven ratio questions in the suite all passed after the change. Five of the eight single-entity ones dropped the JOIN entirely. The three that kept it were joining against an annual snapshot using MAX, which is immune to duplication because a repeated row does not change a maximum. The dangerous combination, SUM across a multi-row join, stopped appearing in the generated SQL.

The fix that would only have moved the benchmark

Before either prompt change, the agent had implemented something else: column totals computed inside the Lambda behind the SQL tool. Any multi-row result would come back with the sums of its additive numeric columns attached, so a total would always be a value to quote rather than a sum to compute. It shipped with eight unit tests covering excluded column types, single-row results, and non-numeric cells.

It was withdrawn before deployment. Checking how the assistant platform actually executes SQL showed the query tool is a built-in platform capability, configured by a flag and a database name, running Athena directly. The Lambda that had just been improved was reachable only from our benchmark harness.

Shipping it would have raised the benchmark score while leaving production exactly as it was. A measurement that improves because the measurement apparatus improved is worse than no measurement, because it reports progress that did not happen. After that finding I ruled that only the main prompt and the reviewer were in scope, and the two shapes above were built under that constraint.

The change that made things worse

The other rejected change is worth reporting because it shows the method disconfirming as well as confirming.

Several failures had involved converting a calendar month to a fiscal year, so the agent replaced the arithmetic rule with a lookup table listing the mapping explicitly for each year in range. Removing arithmetic had just worked twice, so extending the idea looked obviously right.

It was measured across four rounds of the thirteen date-dependent questions. Three questions that had been passing consistently began to fail, all with the same wrong year, and one of them wrote the correct year in a SQL comment while putting a different one in the WHERE clause. The likely cause is interference: the prompt now stated the January-to-March mapping in two overlapping tables, and the model had two competing patterns to match against instead of one rule to apply.

Reverting restored the previous behaviour, confirmed at eighteen consecutive passes on the three affected questions. The same reasoning that produced two good changes produced one bad one, and only the measurement told them apart.

Summary

The suite finished at 57 of 57 on the run after these changes, with the arithmetic and fan-out failures gone. Four changes were attempted and two kept:

The generalisable part is the difference between the two kept changes and the rule they replaced. A prohibition is a request for compliance on every future run; a query shape that cannot express the mistake asks for nothing. When a written rule with worked examples is being broken, adding a third example is usually the weakest move available. The stronger question is what the model would have to write instead, and whether the failure can be made unrepresentable there.

Two smaller things carried over. Check whether a rule is satisfiable in the position where it is being broken before assuming it was ignored, because the first failure here was a genuine conflict between two correct rules. And measure per-question across repeated runs rather than trusting a suite total: an intermittent failure at roughly one run in fourteen is invisible in a single pass, and it was repeated measurement that caught both the change that helped nothing and the change that hurt.

On where the judgement sat, since this was agent-assisted work: the agent diagnosed the rule conflict, proposed both mechanisms, wrote the prompt changes, and found that the Lambda was off the production path. I set the scope after that finding, chose to ship the fan-out change without demanding proof of its effect, and made the call on what to keep. The agent’s proposals were good and its first instinct about where to put the fix was wrong, which is roughly the split to expect.

References

• Presto GROUP BY documentation, covering ROLLUP and the GROUPING SETS it expands to
• Amazon Athena SQL reference, which inherits the Trino GROUP BY extensions
• A study of in-context-learning-based text-to-SQL errors, which classifies unaligned aggregation structure as a distinct error type
• Large language model agents can use tools to perform clinical calculations, on delegating arithmetic away from the model