E0031 join-fan-out

An aggregate over a table whose rows another one-to-many join multiplies.

What it reports

sum, avg or count over the columns of a table joined one-to-many to a parent, when another table is joined one-to-many to the same parent. Each row of the first table is repeated once per matching row of the second, so the aggregate counts it several times. The query runs and returns numbers that are too large.

A join is one-to-many when the joined columns of the child side are not a primary or unique key of its table (coupons.order_id for orders.id).

Example

With a payments table added to the schema on the Rules page:

CREATE TABLE payments (
  id SERIAL PRIMARY KEY,
  order_id INTEGER NOT NULL REFERENCES orders(id),
  amount NUMERIC(10, 2) NOT NULL
);

SELECT o.id, count(c.code) AS coupons, sum(p.amount) AS paid
FROM orders o
JOIN coupons c ON c.order_id = o.id
JOIN payments p ON p.order_id = o.id
GROUP BY o.id;

warning[E0031]: count() over 'c' counts each of its rows once per matching row of 'p': both are joined one-to-many to 'o'
  --> q.sql:1:14
    |
  1 | SELECT o.id, count(c.code) AS coupons, sum(p.amount) AS paid
    |              ^^^^^^^^^^^^
    = help: Aggregate 'c' in a subquery or LATERAL join grouped by its join column, then join the result to 'o'

sum(p.amount) is reported the same way. Fixed, each child aggregated on its own first:

SELECT o.id, c.coupons, p.paid
FROM orders o
JOIN (SELECT order_id, count(code) AS coupons FROM coupons GROUP BY order_id) c ON c.order_id = o.id
JOIN (SELECT order_id, sum(amount) AS paid FROM payments GROUP BY order_id) p ON p.order_id = o.id;

Notes

To report only what the schema proves, nothing is reported when:

• a join condition is anything but equalities of qualified columns between two tables (a filter such as AND p.is_latest may leave one row), or a FROM item is a subquery, CTE or function,
• the aggregate is max, min, count(*), a DISTINCT aggregate or a window function, or its argument uses columns of several tables or unqualified columns,
• the unique keys of a table may be incomplete (an unparsed CREATE UNIQUE INDEX or ALTER TABLE).

A single one-to-many join (sum(i.price) per order over its items) is never reported.

Configuring

# sqlsift.toml
[rules]
join-fan-out = "error"   # or "off"

Or for a single line: -- sqlsift:disable E0031.