Issue 2190: bounded orderBy index diagnostics#

Date: 2026-10-16

Baseline: 2c83fdc53bd762fa8b8d5e5a8b82e092710618be (origin/main)

Review scope: the repair recorded by this document. PR 2193 was explicitly excluded from the investigation. This record reviews the issue report against the baseline and the executable repair in the same commit as this file.

Law#

When a bounded orderBy cannot use an installed index for its ordered source prefix, its development warning must describe the remaining cause:

• no index matches the ordered field;
• field indexes exist, but none matches the resolved comparison options; or
• an index matches both, but cannot support ordered lazy loading.

If any candidate matches the field and comparison options and supports ordered lazy loading, registration order must not hide it. The compiler must use that candidate and issue a bounded ordered acquisition rather than a full-source fallback.

Custom string order is a separate case: even a compatible local index cannot make the provider paginate by an arbitrary callback. On-demand sources require a filtered full-source acquisition. A missing ordinary index, by contrast, can still start with a bounded ordered prefix and local scan or sort.

The authority is the existing developer warning's stated remediation and the bounded ordered-acquisition contract. A diagnostic that recommends work the Collection already has is false. Index registration order is physical planning and cannot change eligibility.

Experiment and inference#

packages/db/tests/index-suggestion-oracle.test.ts now contains an independent finite classifier over field identity, null placement, ordered-read capability, and candidate order. The production driver compiles and preloads a public live-query Collection and records the development warning.

On the baseline, four of the original eleven diagnostic cells failed:

• a null-placement mismatch emitted the generic missing-index warning;
• matching but order-ineligible indexes emitted the same warning in two cells;
• an ineligible candidate registered before an eligible candidate still caused fallback and a warning.

These were assertion mismatches at the intended diagnostic checkpoint, not setup failures or timeouts. The first three cells establish the issue report's missing classification. The candidate-order cell exposes a sibling selection bug: findIndexForField returned the first field/options match before the compiler checked ordered-read capability.

packages/db/tests/query/custom-index-order-selection-oracle.test.ts supplies the production-path witness. An on-demand source registers an order-ineligible custom index before a normal eligible index. After preload, the query must show the independently sorted two-row prefix, issue a bounded ordered request, and issue no unbounded full-source request. A boundary probe may accompany that request. Spies on the two readers calibrate which compiler path produced the public requests; reader identity is not itself a public product observation.

A hostile first-match mutant that ignores the eligibility predicate failed that oracle because the eligible index's takeFromStart was never called. The same mutant also failed two diagnostic cells by silently treating an ineligible index as usable. These were assertion failures at the intended reader and warning observations.

An independent prep review then found that the first formatter called JSON.stringify on user-owned comparison options. Both a missing-index and a mismatch history threw on cyclic localeOptions, although Intl accepts that object. A custom comparator was rendered as the unusable string "[function]". All three added oracle cells were RED on that formatter. The repair describes only safe scalar fields and tells locale and custom callers to reuse the same locale configuration or comparator function reference; it does not traverse those values.

The initial repaired tree passed 22 index-suggestion cases and five custom-index selection cases. The production-message census also accepted the new development-only literals.

Finding ledger#

| S-01 | A first matching but order-ineligible index can hide a later eligible index. | The baseline warned and fell back when the custom index was registered first. | fixed-now | Both candidate orders are classified; the production-path witness requires bounded demand without an unbounded full-source request and calibrates the eligible reader. | | R-01 | Formatting a development warning can throw on valid cyclic localeOptions. | Query construction threw in both missing-index and mismatch cells. | fixed-now | The diagnostic oracle crosses both causes without traversing the options object. | | R-02 | Rendering a custom comparator as "[function]" produces invalid remediation and loses required reference identity. | The warning embedded the string in an executable-looking createIndex example. | fixed-now | The custom-collation cell requires the exact reference-identity instruction and rejects the old placeholder. |

PR #2196 follow-up#

A later review found that the repaired message still described ordinary unindexed prefixes as full-source loads and offered a local index as a cure for custom provider ordering. The revised diagnostic distinguishes custom collation, virtual fields, and ordinary index failures. Public on-demand acquisitions show one full-source request for a custom comparator even with a matching local index, and a bounded ordered request without an index. The matching-index, virtual-field, and request-shape checks were RED on the prior message and GREEN on the revised one. Direction is omitted from the mismatch advice because a compatible index can be read in reverse.

The neighboring selection checks were also RED before repair: a direct optimizedOnly snapshot ignored the second eligible index, and eager auto-indexing did not create one when an ineligible manual index existed. The same public oracle now checks both at initial read. It does not claim provider paging from an incomplete source or later index changes. Using full current eligibility as the auto-index creation predicate was a plausible narrow fix, but it created extra identical indexes for mixed value domains. The mixed-index oracle rejected it at the index-count checkpoint. The final admission check distinguishes a custom index's missing static range capability from a structurally sound index's changing value domain.

Oracle review#

• ORC-001: The law and limits are stated above and beside both executable owners. The comparison-options diagnostic grammar currently distinguishes null placement; BaseIndex.matchesCompareOptions has separate direct coverage for locale, locale options, string-sort mode, direction, and custom comparator identity.
• ORC-002: The diagnostic model uses only declared candidate facts. It does not call the production field, comparison, or eligibility classifiers. The ordered-result model sorts a copied row array by numeric rank.
• ORC-003: Both owners state their laws, finite histories, production paths, observation cuts, and limits beside the executable checks.
• ORC-004: Not applicable because the diagnostic grammar is bounded enumeration, not a generated-history claim. The matrix still reconstructs the report, isolates each classifier axis, crosses both orders for mixed eligibility, and excludes an unrelated-field index from field presence.
• ORC-005: Public live-query construction and preload reach the warning and acquisition paths. The recorders retain every warning and require the exact bounded request shape. Reader spies are path calibration only.
• ORC-006: The baseline is the rejected generic-diagnostic design. The first-match mutant is the rejected one-trace selection repair.
• ORC-007: Not applicable; neither new check is an important generated property.
• ORC-008: Not applicable; both models are stateless finite judgments.
• ORC-009: The tests use Collection, source Collection, acquisition, and checkpoint as defined by the project glossary. Candidate is a test-only index description, not a production lifecycle concept.
• ORC-010: Both owners preserve a primary assertion failure separately from cleanup failures and restore spies.
• ORC-011: Not applicable. No plausible shared semantic classifier was identified between the finite model and production. The production-path witness supplies integration evidence, not an independent second semantics.
• ORC-012: This record supplies the exact RED, hostile-mutant, GREEN, and scope evidence for the oracle repair.
• ORC-013: Missing-field and mismatch cells distinguish field presence; matching-ineligible and matching-eligible cells distinguish capability; the mixed-order cells reject first-candidate policy. Cyclic options reject both unconditional and mismatch-only JSON serialization, while the custom cell rejects remediation that loses comparator identity.
• ORC-014: The controlled on-demand adapter proves the compiler-to-adapter request and reader choice only. It does not claim real-provider paging.

Remaining limits#

The diagnostic decision table does not enumerate every comparison-option dimension, every hostile object behavior, production-build suppression, repeated warning volume, or every query shape. The formatter does not inspect user-owned locale-options or comparator values, so cyclic objects and function identity distinguish the unsafe designs without claiming every object shape. The existing comparison-option tests own the lower-level matching semantics, and the production-message oracle owns development-text erasure. Provider paging from an incomplete source and later source changes remain with the ordered-acquisition owners.