F
Member
【Editorial Discussion】
A generated SQL query can be syntactically valid, run successfully, and produce plausible totals while counting the same business event several times. Joining orders to line items changes the row grain. Adding another one-to-many table can multiply it again. A language-model application that validates only execution success will not detect that an order-level amount has been summed at a line-item or cross-product grain.
The first requirement is semantic: what entity does the requested measure belong to, and what entity should each result row represent? “Revenue by customer” is insufficient if the source includes order totals, item prices, partial refunds, and multiple currencies. A technically consistent query can still answer the wrong business question.
**Use fixtures that expose multiplication**
Create a small synthetic database with manually reviewable relationships. Include an order with multiple line items and multiple associated notes, another order without notes, and an order sharing the same monetary total as a different order. Define the requested order-level measure explicitly. The equal-valued orders matter because SUM(DISTINCT amount) can appear to repair duplication while incorrectly merging distinct events with equal amounts.
Ask the application to generate its query, then inspect the intermediate rows before aggregation. Count matches per order and compare the total against an independent order-grain calculation. Keep this reference simple enough to audit; asking the same model to write a second complex query is weak independence.
**Challenge the result with semantic-preserving changes**
Add a note to an existing order. An order-total report should not change merely because an unrelated descriptive child row appeared. Split an existing line item into two equivalent lines while preserving quantity and value. For an explicitly order-total-based report, that representation change should also leave the answer unchanged. These interventions test the intended measure, not a universal rule that joins must never change a total.
When a result moves, distinguish mechanisms. An inner join may be dropping orders with no child rows; a one-to-many join may be duplicating them; a filter applied to a child table may intentionally restrict which orders qualify. Test those cases separately before prescribing pre-aggregation or an existence predicate. The correct repair follows the requested semantics, not a blanket ban on joins.
Read-only execution, row limits, and query timeouts remain useful safeguards, but they do not establish numerical meaning. Declared foreign keys alone also do not guarantee one matching child per parent. Synthetic tests may miss slowly changing dimensions, null behavior, or valid adjustments that require a different accounting grain.
What contract do you give a text-to-SQL system for measure grain and join cardinality? Which database mutation has caught a plausible but duplicated total that ordinary query-execution tests accepted?
A generated SQL query can be syntactically valid, run successfully, and produce plausible totals while counting the same business event several times. Joining orders to line items changes the row grain. Adding another one-to-many table can multiply it again. A language-model application that validates only execution success will not detect that an order-level amount has been summed at a line-item or cross-product grain.
The first requirement is semantic: what entity does the requested measure belong to, and what entity should each result row represent? “Revenue by customer” is insufficient if the source includes order totals, item prices, partial refunds, and multiple currencies. A technically consistent query can still answer the wrong business question.
**Use fixtures that expose multiplication**
Create a small synthetic database with manually reviewable relationships. Include an order with multiple line items and multiple associated notes, another order without notes, and an order sharing the same monetary total as a different order. Define the requested order-level measure explicitly. The equal-valued orders matter because SUM(DISTINCT amount) can appear to repair duplication while incorrectly merging distinct events with equal amounts.
Ask the application to generate its query, then inspect the intermediate rows before aggregation. Count matches per order and compare the total against an independent order-grain calculation. Keep this reference simple enough to audit; asking the same model to write a second complex query is weak independence.
**Challenge the result with semantic-preserving changes**
Add a note to an existing order. An order-total report should not change merely because an unrelated descriptive child row appeared. Split an existing line item into two equivalent lines while preserving quantity and value. For an explicitly order-total-based report, that representation change should also leave the answer unchanged. These interventions test the intended measure, not a universal rule that joins must never change a total.
When a result moves, distinguish mechanisms. An inner join may be dropping orders with no child rows; a one-to-many join may be duplicating them; a filter applied to a child table may intentionally restrict which orders qualify. Test those cases separately before prescribing pre-aggregation or an existence predicate. The correct repair follows the requested semantics, not a blanket ban on joins.
Read-only execution, row limits, and query timeouts remain useful safeguards, but they do not establish numerical meaning. Declared foreign keys alone also do not guarantee one matching child per parent. Synthetic tests may miss slowly changing dimensions, null behavior, or valid adjustments that require a different accounting grain.
What contract do you give a text-to-SQL system for measure grain and join cardinality? Which database mutation has caught a plausible but duplicated total that ordinary query-execution tests accepted?

