Quick answer: Pagination off-by-one bugs show up at the edges, where a row is duplicated across two pages or skipped entirely. Test them with fixed datasets sized around exact multiples of the page size, walk every page, and collect the ids into one set so a duplicate or missing row cannot hide behind a correct count. Cover the zero-item, exact-multiple, last-page, and out-of-range cases rather than only the happy path.
Where the off-by-one hides
A page-based API takes a page number and a size, and the offset is usually (page - 1) * size. The errors cluster around a few comparisons. Treating the first page as zero instead of one shifts every offset by a full page. Using floor instead of ceiling for the page count drops the final partial page. When the total is an exact multiple of the size, a wrong loop condition can produce an extra empty page. The comparison at the boundary, using greater-than versus greater-than-or-equal, decides whether the row at the edge lands on one page or both. Limit and offset endpoints have the same problem written differently, and cursor endpoints can repeat or skip the last seen id.
Build boundary-sized datasets
Use a stable, ordered list of known ids and choose totals that sit on and around the boundaries, rather than testing one arbitrary count. With a size of 10, use totals of 0, 1, 9, 10, 11, and 20.
| Input | Expected behavior | Common off-by-one |
|---|---|---|
| 25 items, size 10 | 3 pages, last page holds 5 | Floor gives 2 pages |
| 20 items, size 10 | 2 pages, no empty page 3 | Extra empty page returned |
| Page 1 | Offset 0, first 10 rows | Offset 10 skips the first page |
| Page past the last | Empty list or a 400 | Repeats the last page |
| Size 0 or negative | A 400 | Division error or all rows |
| Edge ids 10 and 11 | One id per page | Duplicate or missing row |
Assert the response metadata, total and page count and next and previous links, matches the rows actually returned, since the two can drift independently.
Assert on ids, not just counts
Walk every page and gather the ids into a single set, then compare that set against the full dataset. A count alone cannot tell you that two pages each returned id 10 while id 11 never appeared. Check that the set has no duplicates, covers every id exactly once, and that each full page has the right size and the last page has the expected remainder. Add explicit calls for page zero, the page after the last, and an oversized or zero size so the error contract is pinned down. A desktop API workspace lets you run these pages as an ordered scenario, carry the collected ids through the steps, and assert on each response against a fixed local list. Powerduck works this way, so the boundary walk is repeatable without depending on changing live data.
Honest limits
A local mock runs in the desktop main process and returns a fixed ordered list, which is ideal for the arithmetic and comparison bugs but does not verify the real database query, index, or sort stability. It cannot reproduce rows moving while a client pages through, since inserts and deletes during a real walk can cause duplicates or gaps, and it does not prove ties in the sort key are ordered consistently. Confirm those behaviors against genuine data with a stable sort that includes a unique key. Importing a schema does not deploy a backend, and a passing mock run is not evidence about production query behavior.
Frequently asked questions
Why does a correct total count still fail? Counting returned rows does not detect a duplicate id paired with a missing id. Collect the ids into a set and compare it with the source list to catch both.
Why test exact multiples of the page size? That is where an extra empty page or a missing final page appears, because the loop boundary lands exactly on the last row.
What should a page past the last return? Either an empty list or a documented 400, applied consistently. The bug is silently repeating the last page so the client never stops.
Do cursor APIs have the same problem? Yes, around the last seen id. Repeating the cursor returns the same row, and advancing past it skips a row, so assert on the edge id.
The bottom line
Pagination bugs live at the page edges, so size the test data around exact multiples and the zero and one-item cases, then collect ids across every page and assert on the set rather than a count. Pin down the out-of-range and invalid-size contracts, and verify real database sorting and concurrent changes separately. For more on this approach, see the API testing guide hub.
