Two child tables, one very wrong total

The bug that survives code review

You have an order. It has line items. It also has payments. Both are perfectly ordinary one-to-many relationships. You write what looks like an obvious query:

SELECT
  o.id,
  sum(i.qty * i.unit_price) AS goods,
  sum(p.amount)             AS paid
FROM orders o
JOIN order_items i ON i.order_id = o.id
JOIN payments    p ON p.order_id = o.id
GROUP BY o.id;

Order 5001 has 3 line items and 2 payments. The join produces 3 × 2 = 6 rows. Each line item appears twice, so goods is double the truth. Each payment appears three times, so paid is triple the truth. Neither number errors, neither looks absurd, and the ratio between them is even preserved badly enough that a sanity check on “did they pay the full amount” may still pass.

This is the fan trap, sometimes called the chasm trap in its sibling form. It is the single most expensive join bug in business reporting, and it is invisible in the query text. Nothing about those three lines looks wrong.

Why the multiplication happens

Go back to the mechanism. A join emits one row per match. When you join the parent to child table A, you get one row per A. When you then join that result to child table B, each of those rows looks for matches in B, so every A row is paired with every B row for the same parent. The output is the Cartesian product of the two children, per parent.

The rule: two independent one-to-many joins from the same parent multiply each other. Three children multiply three ways. It gets worse quickly: 5 items, 3 payments and 4 shipments give 60 rows for one order.

Spotting it

Two signs, both cheap.

First, the row count. If the joined result has far more rows than either child table, something is fanning. Second, an “impossible” total: revenue larger than the company’s revenue, quantities that exceed inventory, a customer who apparently paid eleven times. Distrust any total you did not check against a known figure.

But the reliable detection is structural, not numerical: count the one-to-many joins in the FROM clause. More than one from the same parent, and you have a fan trap unless you did something about it.

The fixes

Pre-aggregate each child separately, then join the summaries. This is the correct default:

WITH item_totals AS (
  SELECT order_id, sum(qty * unit_price) AS goods
  FROM order_items GROUP BY order_id
),
payment_totals AS (
  SELECT order_id, sum(amount) AS paid
  FROM payments GROUP BY order_id
)
SELECT o.id,
       coalesce(it.goods, 0) AS goods,
       coalesce(pt.paid, 0)  AS paid
FROM orders o
LEFT JOIN item_totals    it ON it.order_id = o.id
LEFT JOIN payment_totals pt ON pt.order_id = o.id;

Each CTE has grain “one row per order”. Joining one-to-one preserves it. Nothing multiplies, and the LEFT JOINs keep orders that have no payment yet, which is usually the interesting group.

Or use scalar subqueries, when there are only one or two values and readability matters more than speed:

SELECT o.id,
  (SELECT sum(qty * unit_price) FROM order_items WHERE order_id = o.id) AS goods,
  (SELECT sum(amount)           FROM payments    WHERE order_id = o.id) AS paid
FROM orders o;

Each subquery returns exactly one value per order, so the grain cannot change. On modern Postgres the planner often rewrites this into something close to the CTE version; on other engines it can run once per row, which is fine for a thousand orders and painful for a million.

Or count(DISTINCT ...), but only for counts. count(DISTINCT i.id) survives the fan because it deduplicates. sum(i.qty) does not, and there is no sum(DISTINCT) that means what you want: sum(DISTINCT amount) would collapse two separate payments of ₹500 into one. This is why reaching for DISTINCT to “fix” a total is dangerous: it works for counting and corrupts summing.

The chasm trap, briefly

The related failure is joining two children that have no rows in common, so an inner join returns nothing for orders that have items but no payments yet, and those are precisely the unpaid orders your report was about. Same cause, opposite symptom: the fan inflates, the chasm deletes. LEFT JOINs on pre-aggregated summaries avoid both.

The habit worth keeping

Before writing a query with more than one join, list the tables and write “one” or “many” next to each. If two “many” tables hang off the same parent, stop and pre-aggregate. It takes fifteen seconds and it is the difference between a report you can defend and one that is wrong by a factor of six.