A cap applied before the filter starves the rows you wanted
A result cap fills with the majority before it reaches your rows, and the page still looks full. Put the filter inside the query and test a busy range.
The query returned a full page of 100 results, and 7 of the 11 journal entries I wanted were not in it.
A timeline view in a personal notes tool of mine asked for one month with a cap of 100 and no type filter. That month had 366 dated pages in the index, most of them another kind of page, and 11 actual journal entries. The untyped call returned 4 of the 11.
Nothing in the response looked broken. A hundred rows is a hundred rows. The fix was to pass the type into the query, so the cap applies after the filter.
The shape
A query has a result cap (k, LIMIT, top N) whose ordering has nothing to do with the caller’s intent. Usually it is recency or insertion order. While the population sharing that ordering is small, the cap never bites.
Then a large, unrelated population starts sharing the same key space, and the wanted rows become a minority inside it. The cap fills with the majority before reaching them. The query still returns a full page of results, so nothing about the response looks broken.
The tell
Counts disagree with the source of truth only in busy ranges. A quiet month returns every row and a busy month returns a handful.
The missing rows all trace back to one type, never a random scatter. Sort the returned rows by whatever the cap ordered on, and the wanted type is clustered just past the cutoff.
The fix
Apply the selective filter inside the query, before the cap, whenever the caller has a specific type in mind. Filtering after the query returns does not help, because the rows are already gone.
Then add a regression test that uses a busy range where the wanted rows are a deliberate minority. A quiet range never triggers the bug, so a test on it proves nothing.
Try it
The example is new, written for this post, using node:sqlite (built into Node, in memory) with made-up rows. Days are plain integers. The quiet range, days 1 to 30, has 40 pages and 11 journal entries. The busy range, days 31 to 60, has 366 pages and the same 11 journal entries. I sized it like the real case, but my entries sit in different places, so my run returns 3 of 11 where the real call returned 4.
Node prints a one-line experimental notice for node:sqlite. The script silences it, so the output below is everything that printed.
// Uses node:sqlite (built in, in-memory). Days are plain integers; the data is made up.import assert from "node:assert/strict";
// node prints a one-line "experimental" notice for node:sqlite; keep the output to what the script prints.process.removeAllListeners("warning");const { DatabaseSync } = await import("node:sqlite");
const db = new DatabaseSync(":memory:");db.exec("CREATE TABLE pages (id INTEGER PRIMARY KEY, day INTEGER, type TEXT)");const insert = db.prepare("INSERT INTO pages (day, type) VALUES (?, ?)");
// Days 1..30 are the quiet range: 40 pages, 11 of them journal entries.// Days 31..60 are the busy range: 366 pages, the same 11 journal entries among them.function fill(firstDay, lastDay, total, journalDays) { const days = lastDay - firstDay + 1; let placed = 0; for (let d = firstDay; d <= lastDay; d++) { const others = Math.floor((total - journalDays.length) / days) + (d - firstDay < (total - journalDays.length) % days ? 1 : 0); for (let i = 0; i < others; i++, placed++) insert.run(d, "note"); if (journalDays.includes(d)) { insert.run(d, "journal"); placed++; } } return placed;}fill(1, 30, 40, [2, 5, 8, 11, 14, 17, 20, 23, 26, 28, 30]);fill(31, 60, 366, [32, 35, 38, 41, 44, 47, 50, 53, 56, 58, 60]);
const CAP = 100;
// Wrong: ask for everything in the range, cap it, then keep the journal entries.function capThenFilter(from, to) { const rows = db.prepare("SELECT day, type FROM pages WHERE day BETWEEN ? AND ? ORDER BY day, id LIMIT ?") .all(from, to, CAP); return { page: rows, wanted: rows.filter((r) => r.type === "journal") };}
// Right: the type is part of the query, so the cap applies after it.function filterThenCap(from, to) { const rows = db.prepare("SELECT day, type FROM pages WHERE day BETWEEN ? AND ? AND type = ? ORDER BY day, id LIMIT ?") .all(from, to, "journal", CAP); return { page: rows, wanted: rows };}
const ranges = [ { name: "quiet", from: 1, to: 30 }, { name: "busy", from: 31, to: 60 },];
for (const { name, from, to } of ranges) { const truth = db.prepare("SELECT COUNT(*) AS n FROM pages WHERE day BETWEEN ? AND ? AND type = 'journal'").get(from, to).n; const all = db.prepare("SELECT COUNT(*) AS n FROM pages WHERE day BETWEEN ? AND ?").get(from, to).n; const a = capThenFilter(from, to); const b = filterThenCap(from, to); console.log(`${name} range: ${all} pages, ${truth} journal entries`); console.log(` cap, then filter: ${a.page.length} rows came back, ${a.wanted.length} of ${truth} journal entries (last day reached: ${a.page.at(-1).day})`); console.log(` filter, then cap: ${b.page.length} rows came back, ${b.wanted.length} of ${truth} journal entries`);}
// The regression test. A quiet range cannot fail; the busy one can.console.log("test: every journal entry in the range comes back");const queries = [ { label: "cap, then filter", query: capThenFilter }, { label: "filter, then cap", query: filterThenCap },];for (const { label, query } of queries) { for (const { name, from, to } of ranges) { try { assert.equal(query(from, to).wanted.length, 11); console.log(` ${label}, ${name} range: pass`); } catch { console.log(` ${label}, ${name} range: FAIL`); } }}quiet range: 40 pages, 11 journal entries cap, then filter: 40 rows came back, 11 of 11 journal entries (last day reached: 30) filter, then cap: 11 rows came back, 11 of 11 journal entriesbusy range: 366 pages, 11 journal entries cap, then filter: 100 rows came back, 3 of 11 journal entries (last day reached: 39) filter, then cap: 11 rows came back, 11 of 11 journal entriestest: every journal entry in the range comes back cap, then filter, quiet range: pass cap, then filter, busy range: FAIL filter, then cap, quiet range: pass filter, then cap, busy range: passBoth queries hand back something in the busy range: 100 rows from the broken one and 11 from the fixed one. Only one of them is complete. The tell is in last day reached: 39. The cap ordered by day, ran out of room on day 39, and the journal entries on later days were never in the result.
The test block at the bottom is the regression test the fix needs. The quiet range passes with the broken query, which is the trap: a test that only used the quiet range would have certified it.