fix(db): boundary expansion for multi-column orderBy pagination #1556
main fromrefs/pull/1556/head·opened 🎯 Changes#
loadNextItems in CollectionSubscription uses a BTree index that orders by (first_col, _id). For multi-column orderBy, items sharing the same first-column value as the boundary but not selected by BTree's take() may rank higher by the full comparator. These missing candidates cause D2 to pick incorrect items for the top-K window.
The fix adds a boundary expansion step after index.take() that fetches all items matching the boundary's first-column value and sends them as additional candidates to D2.
✅ Checklist#
- I have tested this code locally with
pnpm test:pr. - I have followed the steps in the Contributing guide.
🚀 Release Impact#
- This change affects published code, and I have generated a changeset.
- This change is docs/CI/dev-only (no release).
Summary by CodeRabbit#
- Bug Fixes
- Pagination with multi-column sorting now expands the boundary correctly so items at sort boundaries are included when loading more; respects existing filters and avoids duplicate entries.
- Tests
- Added comprehensive tests for multi-column ordering covering various limits, filters, equal-first-column cases, zero/offset edge cases, full-result scenarios, and live updates after inserts.
- 1
Actionable comments posted: 1
🤖 Prompt for all review comments with AI agents
Verify each finding against current code. Fix only still-valid issues, skip the rest with a brief reason, keep changes minimal, and validate. Inline comments: In `@packages/db/tests/boundary-expansion-multi-orderby.test.ts`: - Around line 12-154: Add new test cases in the same test suite to cover pagination/async corner cases: create additional it() cases using createCollection + mockSyncCollectionOptions and createLiveQueryCollection that (1) exercise limit edge values (limit 0, limit 1, limit equal to total rows) and assert results.length and ids, (2) use an offset beyond available rows (e.g., call .offset(...) on the query) and assert an empty result set, and (3) simulate async/race ordering by inserting or updating items after liveQuery.preload() and resolving promises using flushPromises() to ensure orderBy + limit still returns the correct top-K; use the same helper symbols (createCollection, mockSyncCollectionOptions, createLiveQueryCollection, preload, flushPromises, orderBy, limit, offset, select) so the tests run alongside existing cases and lock the pagination boundary-expansion logic against regressions.🪄 Autofix (Beta)
Fix all unresolved CodeRabbit comments on this PR:
- Push a commit to this branch (recommended)
- Create a new PR with the fixes
ℹ️ Review info
⚙️ Run configuration
Configuration used: defaults
Review profile: CHILL
Plan: Pro
Run ID:
75e1b86e-f39d-4813-82ce-42491c0fbe28📥 Commits
Reviewing files that changed from the base of the PR and between be656be5c5fa65cc1c2bf76bc0b60be3d9d2a577 and 147895e8aa620a3d3c809924c294faf5b1ad2340.
📒 Files selected for processing (3)
.changeset/fix-boundary-expansion-multi-orderby.mdpackages/db/src/collection/subscription.tspackages/db/tests/boundary-expansion-multi-orderby.test.ts
@@ -0,0 +134,21 @@134+ .limit(3)135+ .select(({ items }) => ({136+ id: items.id,137+ category: items.category,138+ name: items.name,139+ })),140+ )141+142+ await liveQuery.preload()143+ await flushPromises()144+145+ const results = Array.from(liveQuery.values())146+147+ // Active items sorted by (category asc, name asc):148+ // A-Beta (a3), A-Zeta (a1), B-Delta (b2), B-Gamma (b1), C-Epsilon (c1)149+ // (a2 Alpha is inactive, filtered out)150+ // Top 3: A-Beta, A-Zeta, B-Delta151+ expect(results).toHaveLength(3)152+ expect(results.map((r) => r.id)).toEqual([`a3`, `a1`, `b2`])153+ })154+})🛠️ Refactor suggestion | 🟠 Major | ⚡ Quick win
Add corner-case tests for pagination limits/offsets and async ordering.
Current cases reproduce the core bug, but they don’t cover edge conditions called out for tests (e.g.,
limitedge values,offsetbeyond available rows, and async race/resolve ordering). Please add those to lock this fix against regressions under pagination stress.As per coding guidelines,
**/*.test.{ts,tsx,js}: “Test corner cases including: ... resolved promises, async race conditions, and limit/offset edge cases”.🤖 Prompt for AI Agents
Verify each finding against current code. Fix only still-valid issues, skip the rest with a brief reason, keep changes minimal, and validate. In `@packages/db/tests/boundary-expansion-multi-orderby.test.ts` around lines 12 - 154, Add new test cases in the same test suite to cover pagination/async corner cases: create additional it() cases using createCollection + mockSyncCollectionOptions and createLiveQueryCollection that (1) exercise limit edge values (limit 0, limit 1, limit equal to total rows) and assert results.length and ids, (2) use an offset beyond available rows (e.g., call .offset(...) on the query) and assert an empty result set, and (3) simulate async/race ordering by inserting or updating items after liveQuery.preload() and resolving promises using flushPromises() to ensure orderBy + limit still returns the correct top-K; use the same helper symbols (createCollection, mockSyncCollectionOptions, createLiveQueryCollection, preload, flushPromises, orderBy, limit, offset, select) so the tests run alongside existing cases and lock the pagination boundary-expansion logic against regressions.✅ Addressed in commit a6ac5b3
♻️ Duplicate comments (1)
packages/db/tests/boundary-expansion-multi-orderby.test.ts (1)
154-292: ⚠️ Potential issue | 🟡 Minor | ⚡ Quick winAdd the remaining pagination edge-case tests (
limit(0)and out-of-rangeoffset).Great additions for
limit(1),limit == total, and dynamic insert. One gap remains: this suite still doesn’t coverlimit(0)andoffsetbeyond result length, which are important regressions for paginated top-K behavior.Proposed test additions
+ it(`should return no rows when limit is 0`, async () => { + const initialData: Array<Item> = [ + { id: `a1`, category: `A`, name: `Zeta` }, + { id: `a2`, category: `A`, name: `Alpha` }, + { id: `b1`, category: `B`, name: `Gamma` }, + ] + + const sourceCollection = createCollection( + mockSyncCollectionOptions({ + id: `boundary-limit-0`, + getKey: (item: Item) => item.id, + initialData, + autoIndex: `eager`, + }), + ) + + await sourceCollection.preload() + + const liveQuery = createLiveQueryCollection((q) => + q + .from({ items: sourceCollection }) + .orderBy(({ items }) => items.category, `asc`) + .orderBy(({ items }) => items.name, `asc`) + .limit(0) + .select(({ items }) => ({ + id: items.id, + category: items.category, + name: items.name, + })), + ) + + await liveQuery.preload() + await flushPromises() + expect(Array.from(liveQuery.values())).toEqual([]) + }) + + it(`should return no rows when offset exceeds available rows`, async () => { + const initialData: Array<Item> = [ + { id: `a1`, category: `A`, name: `Zeta` }, + { id: `a2`, category: `A`, name: `Alpha` }, + { id: `b1`, category: `B`, name: `Gamma` }, + ] + + const sourceCollection = createCollection( + mockSyncCollectionOptions({ + id: `boundary-offset-out-of-range`, + getKey: (item: Item) => item.id, + initialData, + autoIndex: `eager`, + }), + ) + + await sourceCollection.preload() + + const liveQuery = createLiveQueryCollection((q) => + q + .from({ items: sourceCollection }) + .orderBy(({ items }) => items.category, `asc`) + .orderBy(({ items }) => items.name, `asc`) + .offset(10) + .limit(2) + .select(({ items }) => ({ + id: items.id, + category: items.category, + name: items.name, + })), + ) + + await liveQuery.preload() + await flushPromises() + expect(Array.from(liveQuery.values())).toEqual([]) + })As per coding guidelines,
**/*.test.{ts,tsx,js}: “Test corner cases including: ... and limit/offset edge cases”.🤖 Prompt for AI Agents
Verify each finding against current code. Fix only still-valid issues, skip the rest with a brief reason, keep changes minimal, and validate. In `@packages/db/tests/boundary-expansion-multi-orderby.test.ts` around lines 154 - 292, Add two tests to cover pagination edge cases: one "limit(0)" test and one "offset beyond results" test. For each, mirror the existing pattern using createCollection(mockSyncCollectionOptions(...)) and createLiveQueryCollection where you .from({ items: sourceCollection }).orderBy(({ items }) => items.category, 'asc').orderBy(({ items }) => items.name, 'asc') and .select(({ items }) => ({ id: items.id, category: items.category, name: items.name })); in the limit(0) test call .limit(0) and assert Array.from(liveQuery.values()) has length 0; in the offset-out-of-range test call .limit(n) with a reasonable n and .offset(valueGreaterThanTotal) then assert values() is empty, making sure to await sourceCollection.preload(), liveQuery.preload() and flushPromises() as in the other tests.🤖 Prompt for all review comments with AI agents
Verify each finding against current code. Fix only still-valid issues, skip the rest with a brief reason, keep changes minimal, and validate. Duplicate comments: In `@packages/db/tests/boundary-expansion-multi-orderby.test.ts`: - Around line 154-292: Add two tests to cover pagination edge cases: one "limit(0)" test and one "offset beyond results" test. For each, mirror the existing pattern using createCollection(mockSyncCollectionOptions(...)) and createLiveQueryCollection where you .from({ items: sourceCollection }).orderBy(({ items }) => items.category, 'asc').orderBy(({ items }) => items.name, 'asc') and .select(({ items }) => ({ id: items.id, category: items.category, name: items.name })); in the limit(0) test call .limit(0) and assert Array.from(liveQuery.values()) has length 0; in the offset-out-of-range test call .limit(n) with a reasonable n and .offset(valueGreaterThanTotal) then assert values() is empty, making sure to await sourceCollection.preload(), liveQuery.preload() and flushPromises() as in the other tests.
ℹ️ Review info
⚙️ Run configuration
Configuration used: defaults
Review profile: CHILL
Plan: Pro
Run ID:
bf545383-af0a-400c-953a-b10ab693cb50📥 Commits
Reviewing files that changed from the base of the PR and between 147895e8aa620a3d3c809924c294faf5b1ad2340 and 227feab4efc62d23306cac8ed95e2729fa847fc2.
📒 Files selected for processing (1)
packages/db/tests/boundary-expansion-multi-orderby.test.ts
🧹 Nitpick comments (1)
packages/db/tests/boundary-expansion-multi-orderby.test.ts (1)
301-311: ⚡ Quick winExtract duplicated source setup into a small helper.
These new edge-case tests repeat the same collection bootstrap/preload block. A shared helper would keep each test focused on the pagination assertion and reduce drift.
♻️ Suggested refactor
+const createSeededSourceCollection = async ( + id: string, + initialData: Array<Item>, +) => { + const sourceCollection = createCollection( + mockSyncCollectionOptions({ + id, + getKey: (item: Item) => item.id, + initialData, + autoIndex: `eager`, + }), + ) + await sourceCollection.preload() + return sourceCollection +}Then replace each repeated block with:
-const sourceCollection = createCollection( - mockSyncCollectionOptions({ - id: `...`, - getKey: (item: Item) => item.id, - initialData, - autoIndex: `eager`, - }), -) -await sourceCollection.preload() +const sourceCollection = await createSeededSourceCollection(`...`, initialData)As per coding guidelines,
**/*.{ts,tsx,js}: “Extract common logic into utility functions when identical or near-identical code blocks appear in multiple places” and “Prefer small, focused utility functions over large inline implementations”.Also applies to: 340-350
🤖 Prompt for AI Agents
Verify each finding against current code. Fix only still-valid issues, skip the rest with a brief reason, keep changes minimal, and validate. In `@packages/db/tests/boundary-expansion-multi-orderby.test.ts` around lines 301 - 311, Multiple tests duplicate the collection bootstrap/preload block; extract that repeated setup into a small helper (e.g., createAndPreloadSourceCollection) that accepts parameters used in the diff (mockSyncCollectionOptions inputs like id, getKey, initialData, autoIndex) and returns the preloaded collection; replace the repeated blocks that call createCollection(... mockSyncCollectionOptions(...)) followed by await sourceCollection.preload() with calls to this helper. Ensure the helper calls createCollection and awaits sourceCollection.preload() internally and keep the same return type so existing assertions using the collection continue to work.🤖 Prompt for all review comments with AI agents
Verify each finding against current code. Fix only still-valid issues, skip the rest with a brief reason, keep changes minimal, and validate. Nitpick comments: In `@packages/db/tests/boundary-expansion-multi-orderby.test.ts`: - Around line 301-311: Multiple tests duplicate the collection bootstrap/preload block; extract that repeated setup into a small helper (e.g., createAndPreloadSourceCollection) that accepts parameters used in the diff (mockSyncCollectionOptions inputs like id, getKey, initialData, autoIndex) and returns the preloaded collection; replace the repeated blocks that call createCollection(... mockSyncCollectionOptions(...)) followed by await sourceCollection.preload() with calls to this helper. Ensure the helper calls createCollection and awaits sourceCollection.preload() internally and keep the same return type so existing assertions using the collection continue to work.
ℹ️ Review info
⚙️ Run configuration
Configuration used: defaults
Review profile: CHILL
Plan: Pro
Run ID:
2b058005-b217-4c60-b664-c2d005793c95📥 Commits
Reviewing files that changed from the base of the PR and between 227feab4efc62d23306cac8ed95e2729fa847fc2 and a6ac5b366f21dd965260f33b22b8750a6ed43c3f.
📒 Files selected for processing (1)
packages/db/tests/boundary-expansion-multi-orderby.test.ts
Closing as superseded by RFC #1657's planned TotalOrder/WindowState work. The multi-column boundary bug is confirmed and will remain an acceptance case; the current point patch does not cover null, direction, and full-tuple ordering semantics.
KyleAMathews(GitHub profile, opens in a new tab) closed this
Sign in to join this conversation.
📝 Walkthrough
Walkthrough#
This PR expands pagination boundaries for multi-column
orderByin TanStack/db by including all rows whose firstorderBycolumn equals the observed boundary value during paginated loads, and adds tests covering several pagination and filter scenarios.Changes#
Boundary Expansion for Multi-Column OrderBy
.changeset/fix-boundary-expansion-multi-orderby.md,packages/db/src/collection/subscription.tsrequestLimitedSnapshotto, when multi-columnorderBypagination hits a boundary, query the collection for all rows with the firstorderBycolumn equal tobiggestObservedValue, exclude keys already in outgoingchangesorsentKeys, applywhereExpressionif present, and append additionalinsertchanges so top‑K selection includes equal-boundary rows.packages/db/tests/boundary-expansion-multi-orderby.test.tslimit(4)and projection; all-rows-same-first-orderBy withlimit(3)validating second-column ordering;wherefilter combined with multi-column ordering andlimit(3);limit(1)case;limitequal to total rows returning full order; live-query update after dynamic insert withlimit(2);limit(0)and offset-beyond-rows edge cases.🎯 3 (Moderate) | ⏱️ ~20 minutes
🚥 Pre-merge checks | ✅ 5
✅ Passed checks (5 passed)
✨ Finishing Touches
🧪 Generate unit tests (beta)
Thanks for using CodeRabbit! It's free for OSS, and your support helps us grow. If you like it, consider giving us a shout-out.
❤️ Share