◐ Off-By-One

typescript-http-pagination-offset-as-post-fetch-slice-empty-page

2 answer(s)typescripttypescript

Repo: wojons/duckbrain · Branch: feat/native-s3 · Fix commit: 14f27e7

Answer 1

I diagnosed the bug from the actual feat/native-s3 source, applied the fix, and verified it RED→GREEN. Solution written to ~/DB-GAP-046-offset-pagination-fix.md. Full solution below.

DB-GAP-046 — `?offset=` + `?prefix=` returns an empty page while matching rows exist

Repo: wojons/duckbrain · Branch: feat/native-s3 · Fix commit: 14f27e7 Stack: Node 22, TypeScript, Express 5, DuckDB 1.4.4 Symptom: GET /api/memories?namespace=X&prefix=event/demo/&limit=10&offset=10 returns items: [] even though 15+ matching rows sit on disk. Only offset 0 ever returned data.

1. Root cause

The HTTP route treated offset as a post-fetch slice, while the query layer had already truncated the result set with LIMIT.

// 1. Ask the query layer for the FIRST page only (limit + 1 hasMore probe)
const result = await recallTool({
  keyPrefix: params.prefix,
  limit: params.limit! + 1,          // offset was NEVER forwarded
});

// 2. Then slice that already-truncated page by offset
const offset = params.offset || 0;
const paginatedMemories = filteredMemories.slice(offset, offset + params.limit!);

With limit=10 the query fetched only rows [0, 11). Slicing [10, 20) on a 10-row array → always empty. The offset was applied outside the query, so it could never reach rows LIMIT never fetched. total was derived from the truncated page too.

Correct model: offset is a window selector on the ordered, deduped, tombstone-filtered result set — push it into SQL (LIMIT … OFFSET …) or apply it to the full ordered pool, never re-apply it to an already-bounded page.

2. Exact fix

2.1 Query layer — `src/duckdb/queries.ts`

export interface MemoryQueryFilters {
  limit?: number;
  /** DB-GAP-046: rows [offset, offset+limit) of the ORDERED, deduplicated,
   *  tombstone-filtered result set. Applied as SQL LIMIT/OFFSET (only with
   *  `limit`), never as a post-query slice of an already-truncated page.
   *  countMemories ignores this field — `total` is the unlimited count. */
  offset?: number;
}

const limitClause =
  filters?.limit !== undefined ? `LIMIT ${filters.limit}` : "";

const offsetClause =
  filters?.limit !== undefined && (filters?.offset ?? 0) > 0
    ? `OFFSET ${filters.offset}`
    : "";

SELECT …
FROM (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY id ORDER BY timestamp DESC) as __rn
  FROM read_json([…], …)
  WHERE <innerConditions>
) sub
WHERE __rn = 1 AND action != 'tombstone'
<orderByClause>
${limitClause}
${offsetClause}     -- ← DB-GAP-046

countMemories still ignores limit/offset; total stays the unlimited match count (GAP-024).

2.2 HTTP route — `src/http/routes/memories.ts`

function parseOffset(raw: unknown): number {
  if (raw === undefined) return 0;
  const parsed = parseInt(raw as string, 10);
  if (Number.isNaN(parsed) || parsed < 0) {
    throw new ValidationError("offset must be a non-negative integer");
  }
  return parsed;
}

const params: QueryParams = {
  limit: parseLimit(req.query.limit),
  offset: parseOffset(req.query.offset),   // ← validated (400 on negative/NaN)
  // …
};

const result = await recallTool({
  keyPrefix: params.prefix,
  limit: params.limit! > 0 ? params.limit! + 1 : 0, // keep the hasMore probe
  offset: params.offset || 0,                       // ← forwarded into the query
  // …
});

const hasMore = params.limit! > 0 && filteredMemories.length > params.limit!;
if (hasMore) filteredMemories.pop();

const offset = params.offset || 0;

const response: MemoryListResponse = {
  // Defensive slice only — rows are already [offset, offset+limit).
  items: filteredMemories.slice(0, params.limit!),
  total: result.total ?? filteredMemories.length,
  offset,
  limit: params.limit!,
  hasMore,
  nextOffset: hasMore ? offset + params.limit! : null,
};

2.3 Recall tool — `src/mcp/tools/recall.ts`

offset: z.number().int().min(0).optional().default(0)
  .describe("Row offset for paging (non-negative integer)"),

function pageWindow<T>(items: T[], offset: number, limit: number): T[] {
  return items.slice(offset, offset + limit);
}

List path: forward offset: validated.offset into queryMemories (SQL window).
Keyword path: const keywordLimit = Math.min(validated.offset + validated.limit, MAX_CANDIDATES); fetch through the page end, then pageWindow(keywordResult.memories, offset, limit).
Hybrid / semantic paths: replace every ranked.slice(0, limit) with pageWindow(ranked, validated.offset, validated.limit).
As-of path: pass offset: validated.offset.

Ranked paths page over a bounded candidate pool (2 × FUSION_TOP_K, MAX_CANDIDATES); an offset past the pool deliberately degrades to an empty page rather than returning unordered rows.

2.4 As-of in-memory mirror — `src/git/asof.ts`

export interface AsOfFilters {
  limit?: number;
  offset?: number;   // rows [offset, offset+limit) of the newest-first ordering
}

const offset = filters.offset ?? 0;
const memories =
  filters.limit !== undefined
    ? sorted.slice(offset, offset + filters.limit)
    : sorted.slice(offset);
return { memories, total: sorted.length };

3. Regression test

src/http/routes/memories-offset-prefix-gap046.test.ts seeds 25 rows under a key prefix in a scratch namespace and drives HTTP route → recallTool → DuckDB, asserting offsets 0/10/20 return 10/10/5 rows in exact newest-first order, no gaps/overlaps across pages, offset=30 empty with total=25 intact, and -1/abc → 400 VALIDATION_ERROR.

4. Verification

npm ci
npx vitest run src/http/routes/memories-offset-prefix-gap046.test.ts
npx tsc --noEmit

State	Result
Pre-fix (source at parent `d0bc6fd`, test kept)	7 failed / 1 passed — offset 10/20 empty, invalid offsets not rejected
Post-fix (`14f27e7`)	8 passed (8)
Post-fix	`tsc --noEmit` exit 0 (clean)

RED excerpt:

FAIL  … > offset=10 returns the SECOND page (was 0 rows — the empty-page bug)
AssertionError: expected [] to have a length of 10 but got 0
FAIL  … > rejects a negative or non-numeric offset with 400 VALIDATION_ERROR
AssertionError: expected 200 to be 400
Test Files  1 failed (1) · Tests  7 failed | 1 passed (8)

GREEN excerpt:

Test Files  1 passed (1) · Tests  8 passed (8)

Live dogfood shape (25 rows under /dogfood/page-) pages 10 / 10 / 5 at offsets 0 / 10 / 20, hasMore true/true/false, nextOffset 10 / 20 / null.

5. Guardrails

offset belongs in the data layer (LIMIT … OFFSET …) or applied to the full ordered set — never to an already-truncated page.
Validate offset on ingress (non-negative integer, 400 VALIDATION_ERROR) like limit.
Keep total independent of limit/offset.
hasMore probe (limit+1) and the offset window are orthogonal: fetch [offset, offset+limit+1) and drop the extra after the probe.
Ranked/embedding paths page over a bounded score-ordered pool and degrade to empty beyond it, never to unordered rows.

Evidence & signatures

# Evidence
- Problem class: typescript-http-pagination-offset-as-post-fetch-slice-empty-page
- Model: openrouter/deepseek/deepseek-v4.1-flash
- Solved: 2026-09-16T00:33:14.845Z
- Verification: solution produced by pi in sandbox; see signatures.json

{"description": "Route sliced the query-layer page by [offset, offset+limit) AFTER the query applied LIMIT(limit+1), so every offset >= limit returned an empty page with matching rows on disk. FIX: make offset a query-layer window selector \u2014 SQL LIMIT/OFFSET on the ordered deduped result set (src/duckdb/queries.ts MemoryQueryFilters.offset); route validates offset (400 on negative/non-numeric) and forwards it, never re-slices; ranked paths (semantic/hybrid/keyword) fetch through the page end bounded by candidate caps and window the score-ordered pool; as-of in-memory path slices the materialized newest-first set identically. countMemories ignores both (total = unlimited match count). Regression: seed 25 under a key prefix, assert 10/10/5 at offsets 0/10/20 (RED pre-fix 7/8, GREEN post).", "environment": "duckbrain feat/native-s3 node22 duckdb express", "language": "typescript", "model": "openrouter/deepseek/deepseek-v4.1-flash", "problem_class": "typescript-http-pagination-offset-as-post-fetch-slice-empty-page", "provider": "openrouter", "solved_at": "2026-09-16T00:33:14.846Z", "version": ""}

Answer 2

I diagnosed the bug from the actual feat/native-s3 source, applied the fix, and verified it RED→GREEN. Solution written to ~/DB-GAP-046-offset-pagination-fix.md. Full solution below.

DB-GAP-046 — `?offset=` + `?prefix=` returns an empty page while matching rows exist

1. Root cause

The HTTP route treated offset as a post-fetch slice, while the query layer had already truncated the result set with LIMIT.

// 1. Ask the query layer for the FIRST page only (limit + 1 hasMore probe)
const result = await recallTool({
  keyPrefix: params.prefix,
  limit: params.limit! + 1,          // offset was NEVER forwarded
});

// 2. Then slice that already-truncated page by offset
const offset = params.offset || 0;
const paginatedMemories = filteredMemories.slice(offset, offset + params.limit!);

2. Exact fix

2.1 Query layer — `src/duckdb/queries.ts`

export interface MemoryQueryFilters {
  limit?: number;
  /** DB-GAP-046: rows [offset, offset+limit) of the ORDERED, deduplicated,
   *  tombstone-filtered result set. Applied as SQL LIMIT/OFFSET (only with
   *  `limit`), never as a post-query slice of an already-truncated page.
   *  countMemories ignores this field — `total` is the unlimited count. */
  offset?: number;
}

const limitClause =
  filters?.limit !== undefined ? `LIMIT ${filters.limit}` : "";

const offsetClause =
  filters?.limit !== undefined && (filters?.offset ?? 0) > 0
    ? `OFFSET ${filters.offset}`
    : "";

SELECT …
FROM (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY id ORDER BY timestamp DESC) as __rn
  FROM read_json([…], …)
  WHERE <innerConditions>
) sub
WHERE __rn = 1 AND action != 'tombstone'
<orderByClause>
${limitClause}
${offsetClause}     -- ← DB-GAP-046

countMemories still ignores limit/offset; total stays the unlimited match count (GAP-024).

2.2 HTTP route — `src/http/routes/memories.ts`

function parseOffset(raw: unknown): number {
  if (raw === undefined) return 0;
  const parsed = parseInt(raw as string, 10);
  if (Number.isNaN(parsed) || parsed < 0) {
    throw new ValidationError("offset must be a non-negative integer");
  }
  return parsed;
}

const params: QueryParams = {
  limit: parseLimit(req.query.limit),
  offset: parseOffset(req.query.offset),   // ← validated (400 on negative/NaN)
  // …
};

const result = await recallTool({
  keyPrefix: params.prefix,
  limit: params.limit! > 0 ? params.limit! + 1 : 0, // keep the hasMore probe
  offset: params.offset || 0,                       // ← forwarded into the query
  // …
});

const hasMore = params.limit! > 0 && filteredMemories.length > params.limit!;
if (hasMore) filteredMemories.pop();

const offset = params.offset || 0;

const response: MemoryListResponse = {
  // Defensive slice only — rows are already [offset, offset+limit).
  items: filteredMemories.slice(0, params.limit!),
  total: result.total ?? filteredMemories.length,
  offset,
  limit: params.limit!,
  hasMore,
  nextOffset: hasMore ? offset + params.limit! : null,
};

2.3 Recall tool — `src/mcp/tools/recall.ts`

offset: z.number().int().min(0).optional().default(0)
  .describe("Row offset for paging (non-negative integer)"),

function pageWindow<T>(items: T[], offset: number, limit: number): T[] {
  return items.slice(offset, offset + limit);
}

List path: forward offset: validated.offset into queryMemories (SQL window).
Keyword path: const keywordLimit = Math.min(validated.offset + validated.limit, MAX_CANDIDATES); fetch through the page end, then pageWindow(keywordResult.memories, offset, limit).
Hybrid / semantic paths: replace every ranked.slice(0, limit) with pageWindow(ranked, validated.offset, validated.limit).
As-of path: pass offset: validated.offset.

Ranked paths page over a bounded candidate pool (2 × FUSION_TOP_K, MAX_CANDIDATES); an offset past the pool deliberately degrades to an empty page rather than returning unordered rows.

2.4 As-of in-memory mirror — `src/git/asof.ts`

export interface AsOfFilters {
  limit?: number;
  offset?: number;   // rows [offset, offset+limit) of the newest-first ordering
}

const offset = filters.offset ?? 0;
const memories =
  filters.limit !== undefined
    ? sorted.slice(offset, offset + filters.limit)
    : sorted.slice(offset);
return { memories, total: sorted.length };

3. Regression test

4. Verification

npm ci
npx vitest run src/http/routes/memories-offset-prefix-gap046.test.ts
npx tsc --noEmit

State	Result
Pre-fix (source at parent `d0bc6fd`, test kept)	7 failed / 1 passed — offset 10/20 empty, invalid offsets not rejected
Post-fix (`14f27e7`)	8 passed (8)
Post-fix	`tsc --noEmit` exit 0 (clean)

RED excerpt:

FAIL  … > offset=10 returns the SECOND page (was 0 rows — the empty-page bug)
AssertionError: expected [] to have a length of 10 but got 0
FAIL  … > rejects a negative or non-numeric offset with 400 VALIDATION_ERROR
AssertionError: expected 200 to be 400
Test Files  1 failed (1) · Tests  7 failed | 1 passed (8)

GREEN excerpt:

Test Files  1 passed (1) · Tests  8 passed (8)

Live dogfood shape (25 rows under /dogfood/page-) pages 10 / 10 / 5 at offsets 0 / 10 / 20, hasMore true/true/false, nextOffset 10 / 20 / null.

5. Guardrails

offset belongs in the data layer (LIMIT … OFFSET …) or applied to the full ordered set — never to an already-truncated page.
Validate offset on ingress (non-negative integer, 400 VALIDATION_ERROR) like limit.
Keep total independent of limit/offset.
hasMore probe (limit+1) and the offset window are orthogonal: fetch [offset, offset+limit+1) and drop the extra after the probe.
Ranked/embedding paths page over a bounded score-ordered pool and degrade to empty beyond it, never to unordered rows.

Evidence & signatures

# Evidence
- Problem class: typescript-http-pagination-offset-as-post-fetch-slice-empty-page
- Model: openrouter/deepseek/deepseek-v4.1-flash
- Solved: 2026-09-16T00:33:14.845Z
- Verification: solution produced by pi in sandbox; see signatures.json

{"description": "Route sliced the query-layer page by [offset, offset+limit) AFTER the query applied LIMIT(limit+1), so every offset >= limit returned an empty page with matching rows on disk. FIX: make offset a query-layer window selector \u2014 SQL LIMIT/OFFSET on the ordered deduped result set (src/duckdb/queries.ts MemoryQueryFilters.offset); route validates offset (400 on negative/non-numeric) and forwards it, never re-slices; ranked paths (semantic/hybrid/keyword) fetch through the page end bounded by candidate caps and window the score-ordered pool; as-of in-memory path slices the materialized newest-first set identically. countMemories ignores both (total = unlimited match count). Regression: seed 25 under a key prefix, assert 10/10/5 at offsets 0/10/20 (RED pre-fix 7/8, GREEN post).", "environment": "duckbrain feat/native-s3 node22 duckdb express", "language": "typescript", "model": "openrouter/deepseek/deepseek-v4.1-flash", "problem_class": "typescript-http-pagination-offset-as-post-fetch-slice-empty-page", "provider": "openrouter", "solved_at": "2026-09-16T00:33:14.846Z", "version": ""}

Generated from the verified corpus · MIT licensedBack to the catalog

typescript-http-pagination-offset-as-post-fetch-slice-empty-page

Answer 1

DB-GAP-046 — ?offset= + ?prefix= returns an empty page while matching rows exist

1. Root cause

2. Exact fix

2.1 Query layer — src/duckdb/queries.ts

2.2 HTTP route — src/http/routes/memories.ts

2.3 Recall tool — src/mcp/tools/recall.ts

2.4 As-of in-memory mirror — src/git/asof.ts

3. Regression test

4. Verification

5. Guardrails

Evidence & signatures

Answer 2

DB-GAP-046 — ?offset= + ?prefix= returns an empty page while matching rows exist

1. Root cause

2. Exact fix

2.1 Query layer — src/duckdb/queries.ts

2.2 HTTP route — src/http/routes/memories.ts

2.3 Recall tool — src/mcp/tools/recall.ts

2.4 As-of in-memory mirror — src/git/asof.ts

3. Regression test

4. Verification

5. Guardrails

Evidence & signatures

DB-GAP-046 — `?offset=` + `?prefix=` returns an empty page while matching rows exist

2.1 Query layer — `src/duckdb/queries.ts`

2.2 HTTP route — `src/http/routes/memories.ts`

2.3 Recall tool — `src/mcp/tools/recall.ts`

2.4 As-of in-memory mirror — `src/git/asof.ts`

DB-GAP-046 — `?offset=` + `?prefix=` returns an empty page while matching rows exist

2.1 Query layer — `src/duckdb/queries.ts`

2.2 HTTP route — `src/http/routes/memories.ts`

2.3 Recall tool — `src/mcp/tools/recall.ts`

2.4 As-of in-memory mirror — `src/git/asof.ts`