Why does one join fetching a GraphQL parent list and two of its child lists return too many rows?
answer
- Two child lists on one parent
- The store cannot know they are unrelated
- Combinations, not a union
- Counts computed over duplicated rows
- One result set per relationship
basics
~20 sBecause the two child tables each match the parent independently, the store emits one row per combination. A trial with 61 sites and 23 arms returns 1,403 rows, not the 84 you expected. Fetch each child list with its own statement.
solid answer
~50 sJoining two one-to-many children to the same parent in one statement produces their **cross product** per parent, not their union. A trial with 61 sites and 23 arms yields 61 × 23 = 1,403 rows; add 9 protocol amendments as a third child list and it is 12,627 rows for one trial. Two things go wrong. The transfer and grouping cost explodes, and any aggregate computed over those rows is silently wrong — counting site rows now returns 1,403. Deduplicating in memory only works if every child carries a stable identity to deduplicate on, and `distinct` in the statement does not help because the duplicated rows genuinely differ in the other child's columns. The fix is not a cleverer join: fetch each child list with its own statement or its own loader, so each result set has the cardinality of exactly one relationship.
code
graphql · 7 linesquery TrialDetail($id: ID!) {
trial(id: $id) {
protocolTitle
sites { id city } # 61 of these
arms { id label } # 23 of these
}
}go deeper
Remember the shape of the trap: joining two child lists to the same parent multiplies their counts instead of adding them. Being able to say "fetch each list with its own query" is enough here.
Interviewers want the arithmetic and the second failure. Work 61 × 23 out loud, then explain that any count or sum computed over those rows is silently wrong, and why a single-row fixture hides all of it.
Show how you would find it in a running service — rows read versus objects returned per operation — and be able to weigh separate statements against store-side aggregation, including what each costs in coupling and concurrency.
Make it a reviewable rule rather than folklore: which edges may be joined, what fixtures must contain, and what ratio you alert on. Own the fact that this class of defect scales with data, not traffic, so it arrives without a deploy.
## The multiplication A relational join emits one output row for every combination of matching input rows. When two independent one-to-many children hang off the same parent, "every combination" means the cross product of the two child sets, computed per parent. For one trial in a registry graph: ``` sites = 61 arms = 23 rows = 61 * 23 = 1,403 ``` The intuition that fails is additive: people expect 61 + 23 = 84 rows, because that is how many child objects the response contains. The store has no way to produce 84 — it has no concept of "these two lists are unrelated to each other". It only knows both tables match the same trial id. Add a third child list and the growth is multiplicative again: 61 sites × 23 arms × 9 amendments is 12,627 rows to deliver 93 child objects. On a page of 25 trials, that is over 300,000 rows for a response with a few thousand objects in it. ## Two distinct failures **Cost.** Every one of those rows carries the parent's columns and both children's columns. The wire cost, the deserialization cost and the in-memory grouping cost all scale with the product, while the useful output scales with the sum. This is the failure people notice, eventually, in production. **Correctness.** This is the one that bites quietly. Any aggregate the server computes from the joined rows is now computed over duplicates: - counting site rows returns 1,403, not 61; - summing an enrolment target per site multiplies each site's target by 23; - "most recent site activation" is still right, but "average sites per trial" is off by the arm count. A one-arm, one-site test fixture returns 1 × 1 = 1 row and every number is correct, which is exactly why this ships. ## Why the usual patches do not work **`distinct` on the whole row.** It removes nothing, because the rows genuinely differ — site 7 with arm A and site 7 with arm B are different tuples. `distinct` only helps if you have already narrowed the projection to one child's columns, at which point you are doing one statement per child anyway. **Deduplicating in memory.** This does work, but only if each child row carries a stable identity to deduplicate on. If a child has no primary key in the projection — a computed row, an aggregate, a value object — the server cannot tell a duplicate from two legitimately identical siblings, and collapsing them loses data. It also means you paid the full transfer cost to throw most of it away. **A `left join` for optionality.** A trial with sites but no arms produces 61 rows with null arm columns; a trial with neither produces one all-null row. The grouping code must recognise those and emit empty lists, not lists containing a null element. ## What to do instead Give each one-to-many edge its own result set: - **One statement per child list.** Fetch the trial page, then one bulk lookup for sites keyed by the collected trial ids, and one for arms. Three round trips, 25 + 1,525 + 575 rows, and every row is used. The two child statements are independent and can run concurrently. - **Aggregate on the store side.** Some engines can return each child list as a nested structure in a single parent row, which sidesteps the product entirely. That keeps one round trip at the cost of coupling the fetch to one engine's aggregation features and moving the assembly work into the store. - **Join only what is one-to-one.** A trial and its current status row, or a site and its address, duplicate nothing. Reserve joins for those edges and use separate statements for the branching ones. ## The boundary worth naming This is a **cardinality** problem, not a query-tuning problem. The row count is determined by the shape of the relationships and is the same no matter which physical operator the engine picks or which indexes exist — how the store executes the join, and whether it picks a good plan, is a database subject in its own right. Fixing it means changing what you asked for, not how the store answers. It is also not something GraphQL specifies or causes. The specification says a field returning a list resolves to a list; nothing about it implies one statement or several. What GraphQL contributes is the temptation: a single document naturally selects several sibling lists at once, so the pattern shows up far more often here than in an endpoint hand-written for one screen.
- Why does a single-fixture test never catch this?Because one site and one arm produce 1 × 1 = 1 row, which is indistinguishable from the correct result. Every count, sum and average is right, the grouping code works, and the response is byte-for-byte what you expect. The defect only appears when both child counts exceed one, and it grows as their product — so it typically surfaces on the largest real record in production, not in the suite. Fixtures with at least two rows in each child list are the cheap guard.
- Can you keep the single round trip without the cross product?Yes, by having the store return each child list as a nested structure inside one parent row rather than as joined rows — most engines offer some form of row or array aggregation for this. You keep one round trip and lose the multiplication, but you couple the fetch to that engine's features, push assembly work into the database, and give up the ability to apply independent filters or per-parent limits to each child list as easily.
- How would you detect this in an existing service before it becomes an incident?Compare rows read against distinct objects returned per operation. A healthy edge sits near 1; a cross product shows a ratio equal to the other child's fan-out, and it climbs with data growth rather than traffic. Statement-level row counts from the store, correlated with the operation that issued them, make it obvious. A cheap static check also helps: flag any statement that joins two one-to-many relationships to the same parent.
Asking for a trial's sites and arms in one join is like asking a librarian for every pairing of an author with a translator on a book — you wanted two lists and got the seating chart of every possible pair.
saying these in an interview costs you the question
- Expects 61 + 23 rows instead of 61 × 23
- Thinks DISTINCT collapses the cross-product rows
- Says it is an indexing or query-plan problem
- Computes counts and sums over the joined rows
- Believes a LEFT JOIN prevents the multiplication
- Assumes GraphQL itself causes the row blow-up