On-demand ordered live query sends a poisoned offset after subscribing over a pre-populated collection — permanent row hole + frozen window

[lennybr]

• I've validated the bug against the latest version of DB packages - Reproduced on @tanstack/db@0.8.5 + @tanstack/query-db-collection@1.2.9

Describe the bug
An ordered, windowed live query over a syncMode: "on-demand" collection only pages correctly if the collection was empty when the subscription first ran.

Subscription.requestLimitedSnapshot (dist/esm/collection/subscription.js) serves the window local-first from the collection's BTree index and counts the rows it served into limitedSnapshotRowCount (subscription.js:576), which is then sent to the sync layer as the paging offset (offset: offset ?? currentOffset, subscription.js:618). That counter means "rows this subscription handed to its own pipeline" — not "position in the sync layer's ordering". The two only coincide when the collection starts empty. If the collection already holds rows from a different subset (scattered across the new query's ordering, not a prefix of it), the counter over-counts, and it is monotone non-decreasing (Math.max at subscription.js:581-584 and :723-726), so every subsequent load goes out at the inflated offset. The block of rows between the true frontier and that offset is never requested by any load — a permanent hole.

Two latches then freeze the window instead of surfacing the gap: hasNextPage (live-query-window-controller.js, data.length > pageCount * pageSize) goes false as soon as a delivered page comes up short, and computeOrderedLoadCursor dedupes on serialize({minValues, offset, limit}) with a biggest cursor that never advances past the hole, so loadMoreIfNeeded stops issuing requests entirely.

The common real-world trigger is useLiveInfiniteQuery: any re-key (filter/sort change) constructs the new live query and starts its sync synchronously, while the old query's collection is only dropped to the ~1ms GC — so the new subscription's first snapshot runs over a collection still holding the old subset's rows.

To Reproduce

npm i @tanstack/db @tanstack/query-db-collection @tanstack/query-core
node repro.mjs

// repro.mjs
import {
  BTreeIndex,
  createCollection,
  createLiveQueryCollection,
  eq,
  parseLoadSubsetOptions,
} from "@tanstack/db";
import { queryCollectionOptions } from "@tanstack/query-db-collection";
import { QueryClient } from "@tanstack/query-core";

// 20 rows, ids "01".."20"; odd ids status "a", even ids status "b".
const rows = Array.from({ length: 20 }, (_, i) => ({
  id: String(i + 1).padStart(2, "0"),
  status: (i + 1) % 2 ? "a" : "b",
}));

// On-demand source with an offset-paging queryFn over the in-memory array
// (LoadSubsetOptions.offset is documented as usable "instead of `cursor`").
function makeSource(id) {
  const calls = [];
  const collection = createCollection(
    queryCollectionOptions({
      id,
      queryClient: new QueryClient(),
      queryKey: [id],
      getKey: (r) => r.id,
      syncMode: "on-demand",
      autoIndex: "eager",
      defaultIndexType: BTreeIndex,
      queryFn: (ctx) => {
        const opts = ctx.meta?.loadSubsetOptions;
        const { filters, limit } = parseLoadSubsetOptions(opts);
        const offset = opts?.offset ?? 0;
        calls.push({ offset, limit });
        const matched = rows.filter((r) =>
          filters.every((f) => r[f.field[0]] === f.value),
        );
        return matched.slice(offset, offset + (limit ?? matched.length));
      },
    }),
  );
  return { collection, calls };
}

const windowed = (source, filtered) =>
  createLiveQueryCollection({
    query: (q) => {
      let b = q.from({ r: source });
      if (filtered) b = b.where(({ r }) => eq(r.status, "a"));
      return b.orderBy(({ r }) => r.id, "asc").limit(6).offset(0);
    },
    startSync: false,
  });

// Phase 1: filtered live query A, grown until all 10 "a" rows are resident.
// A stays attached — same effect as the previous query a re-key drops to the
// ~1ms GC, without racing the timer.
const poisoned = makeSource("poisoned");
const liveA = windowed(poisoned.collection, true);
await liveA.preload();
await liveA.utils.setWindow({ offset: 0, limit: 10 });

// Phase 2: unfiltered live query B over the SAME, now pre-populated source.
poisoned.calls.length = 0;
const liveB = windowed(poisoned.collection, false);
await liveB.preload();
await liveB.utils.setWindow({ offset: 0, limit: 11 });

console.log("poisoned loads:", JSON.stringify(poisoned.calls));
console.log("poisoned ids:  ", JSON.stringify(liveB.toArray.map((r) => r.id)));

// Control: identical phase 2 against a FRESH source — no phase 1.
const control = makeSource("control");
const liveC = windowed(control.collection, false);
await liveC.preload();
await liveC.utils.setWindow({ offset: 0, limit: 11 });

console.log("control loads: ", JSON.stringify(control.calls));
console.log("control ids:   ", JSON.stringify(liveC.toArray.map((r) => r.id)));

Actual output:

poisoned loads: [{"offset":0,"limit":6},{"offset":9,"limit":2}]
poisoned ids:   ["01","02","03","04","05","06","07","09","10","11","13"]
control loads:  [{"offset":0,"limit":6},{"offset":6,"limit":5}]
control ids:    ["01","02","03","04","05","06","07","08","09","10","11"]

B's initial local serve took the six lowest resident rows (01,03,05,07,09,11 — spanning true positions 1–11), so the counter lands at 9 and the follow-up load goes out as {offset: 9, limit: 2}. Row "08" is never requested by any load and stays absent while its neighbors are present; growing the window further loads nothing (the two latches above).

Expected behavior
B's second load should be {offset: 6, limit: 5} (as in the control), and all of 01–11 should arrive. The auto-tracked offset should reflect how far the subscription has progressed through the sync layer's ordering — not count rows that happened to be locally resident from another subscription's subset.

Screenshots
n/a

Desktop (please complete the following information):
n/a - plain Node, no browser. Reproduced on Node v24, macOS.

Smartphone (please complete the following information):
n/a

Additional context

• Honoring cursor instead does not avoid this — buildCursor derives from the same poisoned biggest tracking (empirically it stalls earlier), and the query-collection adapter's subset cache key deliberately excludes cursor expressions from serialization, so two loads differing only by cursor collide on one cache entry.
• Holes compound across re-key cycles: each further filter apply/clear over the same collection can inflate the counter again.
• Candidate fix directions: (a) count only sync-delivered rows toward the auto-tracked offset, not locally-served snapshot rows; and/or (b) have useLiveInfiniteQuery dispose the previous live query's collection synchronously on re-key instead of relying on the ~1ms GC, so a new subscription always starts over an empty collection.