Why can a row limit not mean ten parents once the statement joins a child collection, and how do you page instead?

seniormust knowfreq 55%

answer

the limit counts pairs, not parents
boundary parent silently truncated
in-memory paging is correct but unbounded
keys first, then fetch by key list
re-apply the order, unique tie-breaker

basics

~20 s

A row limit counts grid rows, and a row is one parent-child pair, so ten rows may be two parents with a collection cut off mid-way. Page the parent keys first, then fetch those parents with their collection.

solid answer

~50 s

Once a collection is joined, the unit the database can limit is a row, and a row is a parent-child pair. Applying a limit therefore returns an arbitrary number of parents, the last of which has a partially loaded collection that looks complete — silently wrong data, which is worse than a slow page. Layers that detect the combination usually refuse to push the limit down and instead fetch the whole matching result, assemble it, de-duplicate and slice in memory: the page is correct, but memory grows with the unpaged result. The dependable pattern is two statements: first select just the parent keys, with the ordering and the limit, from a statement that joins no collection so one row is one parent; then fetch those parents with their collection restricted to that key list, re-applying the ordering because a join does not preserve the key list's order.

Once a collection is joined, the unit the database can limit is a row, and a row is a parent-child pair. Applying a limit therefore returns an arbitrary number of parents, the last of which has a **partially loaded collection** that looks complete — silently wrong data, which is worse than a slow page. Layers that detect the combination usually refuse to push the limit down and instead fetch the whole matching result, assemble it, de-duplicate and slice in memory: the page is correct, but memory grows with the unpaged result. The dependable pattern is two statements: first select just the parent keys, with the ordering and the limit, from a statement that joins no collection so one row is one parent; then fetch those parents with their collection restricted to that key list, re-applying the ordering because a join does not preserve the key list's order.

code

sql · 13 lines

sql

-- 1) choose the page: no collection joined, so one row is one parent
SELECT p.id
FROM parent p
WHERE p.status = 'OPEN'
ORDER BY p.created_at DESC, p.id DESC
LIMIT 10 OFFSET 20;

-- 2) load those parents with the whole collection; order re-applied
SELECT p.id, p.name, c.id AS child_id, c.label
FROM parent p
LEFT JOIN child c ON c.parent_id = p.id
WHERE p.id IN (?, ?, ?)
ORDER BY p.created_at DESC, p.id DESC;

go deeper

for a junior

Remember the pairing that causes it: a row limit plus a joined collection. The limit counts rows, and a row is one parent-and-child pair, so ten rows is not ten parents.

for a middle

Explain both outcomes — a truncated collection when the limit is pushed down, and a full materialisation when the layer pages in memory — and describe the keys-then-fetch split that avoids both.

for a senior

Demonstrate that you catch this from the emitted statements and the memory profile, insist on a unique tie-breaker for stable pages, and keep the second statement to one collection so the page stays bounded.

for a principal

Set the standard: paged endpoints declare a maximum page size, never fetch collections unbounded, and pay for a total count only where the product needs one. Decide where that policy is enforced rather than reviewed.

Why the limit lands on the wrong unit

A row limit is applied by the database to the rows of the result grid. When the statement joins a child collection, one row is a parent-child pair, not a parent. So a limit of ten rows means "ten pairs", which might be:

ten parents that each happen to have one child;
two parents, one with four children and one with six;
one parent whose collection has hundreds of members, of which you got the first ten.

That last case is the dangerous one, because the layer will happily assemble a parent whose collection holds ten of its four hundred children and hand it over with no indication that it is a fragment. Nothing in the object says "truncated". Sums, counts and business rules computed over that collection are simply wrong.

The two failure shapes

The limit is pushed to the database. Fast, bounded memory, and silently incorrect — arbitrary parent count and a truncated collection on the boundary parent.
The layer refuses to push it down and pages in memory. It runs the statement without the limit, materialises every matching row, assembles and de-duplicates the parents, then slices the requested window. The page is correct, but the whole matching result was fetched to produce it, so memory and latency scale with the total result rather than the page. Many layers emit a warning when they do this; that warning is one of the most under-read signals in a slow-endpoint investigation.

Neither is acceptable on an unbounded list. The first is a data bug, the second is a capacity bug.

The two-statement pattern

The dependable answer separates choosing the page from loading the graph:

Select the parent keys only. A statement with no collection join has one row per parent, so the ordering, limit and offset mean exactly what they say. Apply every filter that lives on the parent here.
Fetch the page's parents with their collection, restricting the parent side to the key list from step 1. The fan-out is now bounded by the page: ten parents times their children, and no more.
Re-apply the ordering in the second statement. The key list carries no order into the join, and the database is free to return the fanned-out rows in any sequence.

Approach	Page correct?	Collection complete?	Memory	Round trips
Limit pushed down with the collection joined	No — arbitrary parent count	No — truncated at the boundary	Bounded	1
Layer pages the assembled result in memory	Yes	Yes	Grows with the whole result	1
Keys first, then fetch by key list	Yes	Yes	Bounded by the page	2

Details that bite in practice

The order must be total. If the ordering columns are not unique, the database may break ties differently between the two statements or between two pages, so rows repeat on one page and vanish from the next. End the ordering with a unique column so the sequence is deterministic.
The key list has a practical ceiling. Page sizes of tens are fine; a key list of thousands strains statement length and parameter limits, and is usually a sign the page size is wrong.
Decide whether the collection should be filtered. If a filter in step 1 referred to the child table, restricting the collection in step 2 with the same predicate gives you partial collections again. Usually step 2 should load the collection whole, and the child predicate should have been expressed as an existence test in step 1.
Do not fetch a second collection in step 2. The page is bounded, but the cross product between two collections is not.
A count for the page header is its own statement, and it must not carry the collection joins.

When a single statement is still right

If the list is not paged, or the collection is small and bounded by the domain — a handful of line items, a couple of addresses — one statement with the collection joined is simpler and faster than two, and the fan-out is linear and tiny. The trap only opens when a row limit meets a joined collection. Recognising that specific pairing, and reaching for keys-then-fetch when you see it, is what the question is testing.

## Why the limit lands on the wrong unit A row limit is applied by the database to the **rows of the result grid**. When the statement joins a child collection, one row is a parent-child pair, not a parent. So a limit of ten rows means "ten pairs", which might be: - ten parents that each happen to have one child; - two parents, one with four children and one with six; - one parent whose collection has hundreds of members, of which you got the first ten. That last case is the dangerous one, because the layer will happily assemble a parent whose collection holds ten of its four hundred children and hand it over with no indication that it is a fragment. Nothing in the object says "truncated". Sums, counts and business rules computed over that collection are simply wrong. ## The two failure shapes 1. **The limit is pushed to the database.** Fast, bounded memory, and **silently incorrect** — arbitrary parent count and a truncated collection on the boundary parent. 2. **The layer refuses to push it down and pages in memory.** It runs the statement without the limit, materialises every matching row, assembles and de-duplicates the parents, then slices the requested window. The page is **correct**, but the whole matching result was fetched to produce it, so memory and latency scale with the total result rather than the page. Many layers emit a warning when they do this; that warning is one of the most under-read signals in a slow-endpoint investigation. Neither is acceptable on an unbounded list. The first is a data bug, the second is a capacity bug. ## The two-statement pattern The dependable answer separates *choosing the page* from *loading the graph*: 1. **Select the parent keys only.** A statement with no collection join has one row per parent, so the ordering, limit and offset mean exactly what they say. Apply every filter that lives on the parent here. 2. **Fetch the page's parents with their collection**, restricting the parent side to the key list from step 1. The fan-out is now bounded by the page: ten parents times their children, and no more. 3. **Re-apply the ordering in the second statement.** The key list carries no order into the join, and the database is free to return the fanned-out rows in any sequence. | Approach | Page correct? | Collection complete? | Memory | Round trips | |---|---|---|---|---| | Limit pushed down with the collection joined | No — arbitrary parent count | No — truncated at the boundary | Bounded | 1 | | Layer pages the assembled result in memory | Yes | Yes | Grows with the whole result | 1 | | Keys first, then fetch by key list | Yes | Yes | Bounded by the page | 2 | ## Details that bite in practice - **The order must be total.** If the ordering columns are not unique, the database may break ties differently between the two statements or between two pages, so rows repeat on one page and vanish from the next. End the ordering with a unique column so the sequence is deterministic. - **The key list has a practical ceiling.** Page sizes of tens are fine; a key list of thousands strains statement length and parameter limits, and is usually a sign the page size is wrong. - **Decide whether the collection should be filtered.** If a filter in step 1 referred to the child table, restricting the collection in step 2 with the same predicate gives you partial collections again. Usually step 2 should load the collection **whole**, and the child predicate should have been expressed as an existence test in step 1. - **Do not fetch a second collection in step 2.** The page is bounded, but the cross product between two collections is not. - **A count for the page header is its own statement**, and it must not carry the collection joins. ## When a single statement is still right If the list is not paged, or the collection is small and bounded by the domain — a handful of line items, a couple of addresses — one statement with the collection joined is simpler and faster than two, and the fan-out is linear and tiny. The trap only opens when a **row limit** meets a **joined collection**. Recognising that specific pairing, and reaching for keys-then-fetch when you see it, is what the question is testing.

Why is in-memory paging over the assembled result treated as a bug rather than a fix?
Because it makes correctness depend on the size of the whole matching set. Page one of a filter matching two million rows fetches all of them to return ten parents. Latency and memory then track the result set instead of the page, and the endpoint fails exactly when the data grows — which is the failure that matters.
What breaks if the ordering columns are not unique?
Tie-breaking is left to the database, and it need not be stable between statements or between pages. The same parent can then appear on two consecutive pages while another is never shown. Ending the ordering with a unique column makes the sequence total and the pages disjoint.
Why must the second statement re-apply the ordering rather than trusting the key list?
A key list in a predicate is a set membership test; it imposes no order on the result. The join is free to return rows in any sequence, so the assembled parents come back in whatever order the plan produced. Only an explicit ordering in the second statement restores the page's sequence.
Can you page by the child rows instead, if that is what the screen shows?
Yes, and it is the right move when the screen is really a list of children. Make the child the root of the query, page it directly so one row is one item, and pull whatever parent fields are needed alongside. The trap only exists when the paged unit and the fanned-out row unit disagree.

saying these in an interview costs you the question

Assumes a row limit returns that many parents when a collection is joined
Does not notice the boundary parent's collection is truncated
Treats in-memory paging as an acceptable production answer
Orders pages by a non-unique column and expects stable pages
Trusts the key list to impose an order on the second statement
Adds a second collection to the fetch that loads the page

Why can a row limit not mean ten parents once the statement joins a child collection, and how do you page instead?

§ answer

basics

solid answer

code

§ go deeper

▾level notes4 levels

▾deep dive~4 min

Why the limit lands on the wrong unit

The two failure shapes

The two-statement pattern

Details that bite in practice

When a single statement is still right

▾common follow-ups4 questions

▾red flags6 flags

Why the limit lands on the wrong unit

The two failure shapes

The two-statement pattern

Details that bite in practice

When a single statement is still right

answer

go deeper