How is this different from a wrong metric definition?

A definition trap (soft deletes, wrong status string, timezone) gives a wrong number because the filter is wrong. Fan-out gives a wrong number because the row count itself is inflated before any filter or aggregate runs — the SQL can be filtering exactly the right rows and still multiply them.

Why doesn't the AI catch this itself?

The query is syntactically and even semantically reasonable — join orders to order_items to get item details, then sum revenue. Nothing about the SQL looks wrong. The bug only shows up as a number that's implausibly high, which requires knowing what the right answer looks like, not something the SQL itself reveals.

What's the actual fix in the query?

Usually one of: aggregate the child table first in a subquery/CTE before joining, use COUNT(DISTINCT orders.id) instead of COUNT(*), or restructure so the SUM happens on the table you're actually measuring, not on the joined result.

Does this only happen with revenue and counts?

It happens with any aggregate over a joined table — averages, counts, sums. Averages are the sneakiest, because a fanned-out average can look almost right (the ratio shifts less obviously than a sum or count does) while still being wrong.

How do I catch this without knowing SQL well?

Ask a sanity-check question alongside the real one: 'how many orders are in this result vs how many rows did the query return?' If the row count is way higher than the order count, a join fanned it out. Also just compare the number to something you already know roughly — the built-in way to verify AI database answers.

AI Database JOIN Inflates Numbers | Fan-Out Bug

The query ran. No error. No timeout. It came back with a total revenue number that's roughly triple what you know your actual revenue is — and there's nothing in the output that looks wrong. This is one of the more disorienting AI-database failures, because everything about the process looked correct.

The usual cause is a JOIN fan-out: joining a table where each parent row matches multiple child rows, multiplying the row count before any SUM or COUNT gets a chance to run on it.

How the multiplication actually happens

Say you ask "what's our total revenue this month" and the AI writes something that joins orders to order_items to get line-item prices, then sums them. That sounds reasonable — the price data lives on order_items, not orders.

The problem: if an order has 3 items, the join produces 3 rows for that one order, each carrying the same order-level data alongside a different item. Sum the item prices, and you get the right revenue for that order. But if the AI instead sums something order-level, or counts orders via COUNT(*) on the joined result, that one order now gets counted or summed 3 times — once per matching item row — and the total balloons in proportion to how many items orders typically have.

This is invisible in the query itself. SELECT SUM(...) FROM orders JOIN order_items ... reads like perfectly normal SQL. The bug only exists in the shape of the join relative to what you're aggregating, and that's exactly the kind of thing that doesn't trigger any error.

Why it's worse than a simple wrong-filter bug

A wrong filter (wrong status string, forgotten deleted_at check — see metric traps) usually gives you a number that's off by a plausible amount. Fan-out tends to give you a number that's off by a multiple — 2x, 3x, sometimes much more depending on how many child rows each parent typically has — which is often the tell that something structural is wrong, not just a filter.

Averages are the exception, and the sneakier case: if every parent row fans out by roughly the same multiple, an average can come out close to correct even though the underlying sum and count are both inflated, because the inflation partially cancels out. That's the version most likely to slip past a casual sanity check.

How to catch it

Ask a second, cheap question alongside the real one: "how many distinct orders are actually in this result?" compared to however many rows the query touched. If you're expecting revenue across 500 orders and the query's underlying row count is 1,400, a join fanned something out.

More generally, this is exactly the kind of thing verifying against a number you already know catches immediately — if last month's revenue is roughly $40k and the AI hands you $118k with total confidence, that gap is worth five minutes before you trust it for anything.

The prompt that avoids it

Be explicit about which table the aggregate should run on, and ask for the count alongside the sum:

When joining tables for this, watch for fan-out: if a table has
multiple matching child rows per parent (e.g. order_items per order),
aggregate the child table first (subquery or CTE) before joining,
or use COUNT(DISTINCT orders.id) instead of COUNT(*).

Show me both the total and the distinct row count of the table
I'm actually measuring, so I can sanity-check the join didn't
multiply anything.

This doesn't require you to know how to write the fix yourself — it tells the model what failure mode to guard against, which is usually enough for it to restructure the query correctly (aggregate-then-join instead of join-then-aggregate) on its own.

The short version

A join that fans out doesn't error, doesn't time out, and doesn't look wrong in the SQL — it just quietly multiplies rows before your total gets computed. The defense isn't reading the SQL more carefully; it's asking for the underlying row count alongside the number, and comparing the result to something you already roughly know before you trust it.

Read-only MCP gateway: mcpserver.design.

Related reading: