Live query with orderBy + limit over an on-demand collection is never ready at construction, so useLiveSuspenseQuery retries for ever

Start with the faithful.mjs reproduction and trace createLiveQueryCollection, OrderedSourceLoader, and useLiveSuspenseQuery, focusing on the chained subset requests and synchronous cache-hit responses. The fix is done when a limited, ordered query whose required subsets are loaded reports ready at construction and retries no longer loop; rerun the standalone output to verify both limited and unlimited cases.

• I've validated the bug against the latest version of DB packages

Describe the bug

A live query that combines orderBy with limit over a syncMode: "on-demand" collection is never ready at construction, even when the source collection is ready and every subset the query needs is already loaded and answered synchronously by the adapter.

That alone is a performance wart. Under useLiveSuspenseQuery it is fatal: a component that suspends before it mounts loses its refs, so every retry runs useLiveQuery with collectionRef.current === null and builds a brand-new live query collection. If that fresh collection is loading, the hook throws a new preload() promise, which resolves, which re-renders, which builds another collection — for ever.

Removing .limit() — changing nothing else — makes the retry ready at construction and everything renders.

In a real app (React 19, @tanstack/electric-db-collection) this pegs the main thread: I measured ~190 renders of a single hook on one navigation, a permanently visible Suspense fallback, and no network traffic after the first few requests — the server had already answered, the rows were in memory, and collection.status was ready with collection.size === 4.

Related but not the same: #1418 (closed/fixed) was useLiveSuspenseQuery + on-demand stuck after a dependency change. This reproduces on 0.9.0 and 0.9.2 with no dependency change at all — the trigger is orderBy + limit.

The cause, as far as I traced it

With orderBy + limit, OrderedSourceLoader needs several sequential subset requests, each issued only from the previous one's complete():

1. { orderBy, limit: offset + limit } — the ordered prefix
2. {} — full source (the canExpressCursorOrder / boundary fallback)
3. { orderBy, limit: 46, offset: 4, cursor } — the cursor page

Because they are chained through promise callbacks, the query cannot reach ready synchronously even when all three are cache hits that the adapter answers with a synchronous true. Without limit there is exactly one subset request, it hits synchronously, and the query is ready at construction.

So the "already loaded ⇒ synchronously ready" fast path that makes useLiveSuspenseQuery terminate exists only for the single-request shape.

To Reproduce

Standalone, @tanstack/db only — no React, no bundler, no Electric, no persistence. Save as faithful.mjs in a dir with {"type":"module"} and @tanstack/db installed, then node faithful.mjs.

import { createCollection, createLiveQueryCollection, BTreeIndex } from "@tanstack/db";

const TABLE = [
  { id: "a", batched_date: new Date("2026-09-03T10:00:00Z") },
  { id: "b", batched_date: new Date("2026-09-03T09:00:00Z") },
  { id: "c", batched_date: new Date("2026-09-02T10:00:00Z") },
  { id: "d", batched_date: new Date("2026-09-01T10:00:00Z") },
];

const build = (withLimit) => {
  const served = new Set();
  const issued = [];
  const collection = createCollection({
    id: `t-${withLimit}`,
    getKey: (r) => r.id,
    syncMode: "on-demand",
    autoIndex: "eager",
    defaultIndexType: BTreeIndex,
    sync: {
      sync: ({ begin, write, commit, markReady }) => {
        markReady();
        return {
          loadSubset: (options) => {
            const key = JSON.stringify({
              limit: options.limit ?? null,
              offset: options.offset ?? null,
              ordered: Boolean(options.orderBy),
              cursor: Boolean(options.cursor),
            });
            issued.push(key);
            // Already loaded -> answer synchronously, as a real adapter does.
            if (served.has(key)) return true;
            served.add(key);
            return new Promise((res) => setTimeout(() => {
              begin();
              for (const row of TABLE) write({ type: "insert", value: row });
              res(commit());
            }, 5));
          },
          unloadSubset: () => {},
        };
      },
    },
  });
  const query = (q) => {
    const o = q.from({ t: collection })
      .orderBy(({ t }) => t.batched_date, { direction: "desc", nulls: "last" });
    return withLimit ? o.limit(50) : o;
  };
  return { collection, query, issued, served };
};

const wait = (ms) => new Promise((r) => setTimeout(r, ms));

for (const withLimit of [false, true]) {
  const { collection, query, issued, served } = build(withLimit);

  const first = createLiveQueryCollection({ startSync: true, query });
  first.subscribeChanges(() => {});
  await first.preload();
  await wait(300);
  const afterFirst = issued.length;

  console.log(`\n=== ${withLimit ? "WITH .limit(50)" : "WITHOUT limit"} — table has ${TABLE.length} rows ===`);
  console.log(`  source: status=${collection.status} size=${collection.size}`);
  console.log(`  first live query: status=${first.status} rows=${first.size}`);
  console.log(`  distinct subsets loaded: ${served.size}`);

  // Every useLiveSuspenseQuery retry builds a fresh live query, because the
  // suspended component's refs were discarded. The source is fully loaded.
  const statuses = [];
  for (let i = 0; i < 5; i++) {
    const retry = createLiveQueryCollection({ startSync: true, query });
    retry.subscribeChanges(() => {});
    statuses.push(retry.status);
    await wait(60);
  }
  console.log(`  retry live query status at construction: ${statuses.join(", ")}`);
  console.log(`  subsets requested by the 5 retries: ${issued.length - afterFirst}`);
}
process.exit(0);

Output (identical on 0.9.0 and 0.9.2)

=== WITHOUT limit — table has 4 rows ===
  source: status=ready size=4
  first live query: status=ready rows=4
  distinct subsets loaded: 1
  retry live query status at construction: ready, ready, ready, ready, ready
  subsets requested by the 5 retries: 5

=== WITH .limit(50) — table has 4 rows ===
  source: status=ready size=4
  first live query: status=ready rows=4
  distinct subsets loaded: 3
  retry live query status at construction: loading, loading, loading, loading, loading
  subsets requested by the 5 retries: 15

Every one of those 15 retry requests is a cache hit answered with a synchronous true. The query still reports loading.

Note the table holds 4 rows against a limit of 50, which is what puts the loader on the "need more data" path (dataNeeded() stays at limit - size). A source with more rows than the limit may behave differently; I did not test that.

Expected behavior

A live query over an on-demand collection whose every required subset is already loaded should be ready at construction, regardless of limit — so that useLiveSuspenseQuery's retry can terminate.

Failing that, useLiveSuspenseQuery shouldn't depend on synchronous readiness for termination, since it cannot hold refs across a pre-mount suspend.

Versions

@tanstack/db 0.9.0 and 0.9.2 (same result), @tanstack/react-db 0.3.8, @tanstack/electric-db-collection 0.4.8, React 19.2, Node 24.

The standalone repro above uses @tanstack/db alone; the React/Electric versions are for the app where it was first hit.