A non-dotted `orderBy` naming a `formula` field answers 200 in arbitrary order — the sort is silently dropped (measured on driver-sql + driver-memory)

[os-zhuang]

Found while measuring #6924's premise (the SORT-axis hint text). Recorded, not claimed. Routing suggestion: domain:engine-core — this is an engine/driver refusal question, not hint text, and #6924's dispatch explicitly scoped it out rather than absorbing it.

The defect

assertSortFieldsExist (packages/metadata-protocol/src/protocol.ts) refuses a dotted orderBy (?sort=account.company_name, #4256) and an unknown field (#4226). It does not refuse a known, non-dotted field that happens to be formula-typed — a formula field is in schema.fields, so it is in gate.known and passes.

It then reaches a driver that has no column for it, and the sort is silently dropped. Query succeeds, all rows present, order arbitrary — the exact failure mode #4226/#4256 exist to stop, on a shape no gate covers.

Measurement

Real SqlDriver (better-sqlite3, on-disk) and real InMemoryDriver, both wired to a real ObjectQL engine, with ObjectStackProtocolImplementation on top for the ingress half. Object carries a formula field sort_key (expression record.title). Five rows inserted C A E B D so "sorted" and "insertion order" are distinguishable.

driver-sql (better-sqlite3):

repro_contact physical columns: ["id","created_at","updated_at","title","seq","account_id","child_total"]
formula col `sort_key` present: false

CONTROL   orderBy title asc     -> ["A","B","C","D","E"]        a real column really sorts
CONTROL   orderBy title desc    -> ["E","D","C","B","A"]
BASELINE  no sort               -> ["C","A","E","B","D"]        insertion order

FORMULA   orderBy sort_key asc  -> ["C","A","E","B","D"]   5 rows, 200
                sort_key values -> ["C","A","E","B","D"]
FORMULA   orderBy sort_key desc -> ["C","A","E","B","D"]   direction-blind

RAW SQL   order by sort_key     -> sqlite: "no such column: sort_key"

PROTOCOL  ?sort=sort_key        -> 200, 5 records, ["C","A","E","B","D"]
PROTOCOL  ?orderBy=["sort_key"] -> 200, 5 records, ["C","A","E","B","D"]

driver-memory:

CONTROL   orderBy title asc     -> ["A","B","C","D","E"]
BASELINE  no sort               -> ["C","A","E","B","D"]
FORMULA   orderBy sort_key asc  -> ["C","A","E","B","D"]
FORMULA   orderBy sort_key desc -> ["C","A","E","B","D"]

asc and desc returning the same order is what makes this a dropped sort rather than a coincidence.

Mechanism

• SqlDriver.createColumn — case 'formula': return; // Virtual — no column. schema-drift.ts's fieldHasColumn() agrees: formula is the only type with no physical column.
• engine.find rewrites ast.fields to drop virtual formula names and inject their dependencies (planFormulaProjection), but leaves ast.sort / ast.orderBy untouched; formulas are evaluated post-query by applyFormulaPlan, after driver.find has already returned.
• sqlite rejects the ORDER BY; the fix(sharing): 共享规则新建页 — 自定义 widget 未国际化，且「接收方」永远无可选项 #3821 unknown-column backstop retries without the sort.

The response even carries the sort_key values in plain view — C, A, E, B, D — in a column the caller asked to be sorted ascending, so the answer visibly contradicts the request and still reports success.

Worth noting for scoping: summary/rollup is not affected. It gets a real, maintained column (case 'summary': col = table.float(name)) and sorts correctly — measured orderBy child_total desc -> E D C B A over values 5 4 3 2 1. Only formula is virtual.

Why it is not fixed in #6924

#6924's scope is the refusal hint text (domain:metadata) — it stopped the hint prescribing a formula field, since following that advice landed the author in this defect one step after being refused for the dotted form. The remaining question is a different decision on a different seat:

1. Refuse at ingress — assertSortFieldsExist rejects an orderBy naming a field whose type materializes no column (400 INVALID_SORT), consistent with how it already treats dotted paths. Cheap, and it keeps the whole REST 列表：sort / select / expand 指向不存在的字段时被静默丢弃（filter 轴已收口，这三条轴还没有） #4226 family on one door.
2. Refuse at the driver — the driver reports that it cannot order by the column instead of letting the fix(sharing): 共享规则新建页 — 自定义 widget 未国际化，且「接收方」永远无可选项 #3821 backstop swallow it. Catches internal callers reaching engine.find() directly, which the ingress gate deliberately does not cover.
3. Materialize — actually give formula fields a stored column. Much larger, and probably contradicts the "formula is virtual" contract the codebase states in several places.

Option 1 has a wrinkle worth flagging before anyone implements it: the gate reads gate.fields[...], so it can see the type — but the refusal would need to name the same remedy the corrected #6924 hint gives ("a stored field"), or the two doors will disagree again.

Refs: #6924 (the hint text, in flight), #6673 (same correction, search axis), #4226, #4256, #3821 (the backstop that swallows it).

[os-project-manager]

Triage (pass #2, 2026-08-09, registered on #6015): routed domain:metadata, graded pm:queue (the fix lands where the existing SORT gate lives — assertSortFieldsExist in metadata-protocol, consistent with #6924's routing; the filer's engine-core suggestion noted). Same family and same direction as the ruled #6674: a known-but-unmaterializable sort field answers 200 in arbitrary order today — extend the gate to refuse formula-typed orderBy fields loudly, corpus-count first per the standing widening discipline.

---

Generated by Claude Code

[os-zhuang]

Claim: PM loop round 8 (domain:metadata seat, sticker #6367)

Session: session_01W6bLax4KMrSfnE1ydFU8Dw
Branch: claude/issue-6994-formula-orderby-gate
Worktree: objectstack-issue-6994
Domain: domain:metadata
Base: origin/main @ current

This card was filed by this seat's own #6924 dispatch as an out-of-scope finding, with a suggested routing of domain:engine-core. Triage routed it to domain:metadata instead, so it is dispatched here — but the routing question is carried into the dispatch as a live one rather than settled: if the measurement shows the right fix locus is the driver or the query compiler rather than the ingress gate, that is a STOP-and-report for re-routing, not something to force into this lane.

Note the relationship to what just landed: #6924 (PR #6996, 6443b792c) fixed the hint text for the dotted-path case — it now prescribes a stored field instead of an unmaterializable formula/rollup denormalization. This card is the other half: the non-dotted form, where a formula field is in gate.known so assertSortFieldsExist passes it through, it reaches the driver, and the query answers 200 in arbitrary order. Measured on both driver-sql and driver-memory during #6924's repro — asc and desc byte-identical, rows in insertion order.

Also relevant and already measured by #6924, so it should not be re-derived: summary/rollup is not in the same boat. A summary field does get a real, maintained column and ORDER BY on it genuinely works. The card's family is formula specifically.

Region: disjoint from the two cards in flight in protocol.ts this round — #7012 is in restoreArtifactRegistryView tier 3, and PR #6973 is being rebased in saveMetaItem's gate region by another session.

---

Generated by Claude Code

[os-zhuang]

os-dev report

Issue: #6994 · Branch: claude/issue-6994-formula-orderby-gate · PR: #7096 (draft) · Base: 2f3e79351 · Status: done · Premise: still valid.

1. Re-confirmed measurement (on my base, not re-cited from the card)

Real SqlDriver (better-sqlite3, on-disk) + real ObjectQL + real ObjectStackProtocolImplementation, five rows inserted C A E B D, sort_key a formula over record.title:

repro_contact physical columns: ["id","created_at","updated_at","title","seq","account_id","child_total"]
formula col `sort_key` present: false

CONTROL   orderBy title asc            ["A","B","C","D","E"]     a real column really sorts
BASELINE  no sort                      ["C","A","E","B","D"]     insertion order

FORMULA   orderBy sort_key asc         ["C","A","E","B","D"]     5 rows, 200
   (its sort_key values)               ["C","A","E","B","D"]
FORMULA   orderBy sort_key desc        ["C","A","E","B","D"]
   asc === desc (byte-identical)?      true

RAW SQL   order by sort_key            sqlite: no such column: sort_key
PROTOCOL  {"sort":"sort_key"}          200, 5 records, ["C","A","E","B","D"]
PROTOCOL  {"orderBy":["sort_key"]}     200, 5 records, ["C","A","E","B","D"]

The card's premise holds exactly as written.

2. PM mechanism hypotheses — all three checked

1. gate.known includes formula fields because it is built without consulting materializability — CONFIRMED. resolveQueryFields (protocol.ts) builds known from Object.keys(schema.fields) plus id/created_at/updated_at. But the follow-up premise is wrong in a useful direction: "is this field backed by a real column" is answerable there — the same helper already returns fields (the raw field map), so gate.fields[name].type is in hand, which is exactly how the dotted branch already reads REFERENCE_VALUE_TYPES. No new plumbing was needed.
2. The fix(sharing): 共享规则新建页 — 自定义 widget 未国际化，且「接收方」永远无可选项 #3821 backstop converts the driver error into a silent success, and is deliberate — CONFIRMED, and it is untouched. sql-driver.ts:3190-3235: on an unknown-column error it retries projection-first, then without the ORDER BY, and its comment states it protects registry-less hosts (the cloud multi-tenant runtime). It keeps doing that; the refusal happens one layer up, before the query is handed over — which is what assertSortFieldsExist's own doc comment already said the ingress gate is for.
3. "Other virtual field kinds beyond formula" — measured, and the family is exactly {formula}. SqlDriver.createColumn has one early return (case 'formula': return; // Virtual — no column); driver-turso's transport skips the same one type; fieldHasColumn agrees. summary → table.float, autonumber → table.string, vector/composite/repeater → JSON columns. Note the trap this rules out: the spec's COMPUTED_VALUE_TYPES is {formula, summary, autonumber} but that is the write contract (never client-written) — gating a sort with it would refuse two types that sort correctly.

3. Remedy chosen, and the evidence for the locus

Option 1 (refuse at ingress), implemented here. The routing evidence is structural, not a preference:

The same gate already answers "known field, wrong TYPE for this axis" on two of its four axes — assertSearchFieldsExist splits unknown from unsearchable (message literally renders type '${meta.type}'), and assertExpandFieldsExist splits unknown from notRelations. SORT had only unknown and dotted. So this is not an engine-side fix pushed through the metadata lane; it is the one member of an existing ingress family that never grew its third verdict, which is precisely the gap formula fell through.

Why not the driver (option 2): the #3821 backstop is correct for genuinely unknown columns and reversing it breaks what it protects; a driver holds only a SQL error and cannot distinguish "formula, unmaterializable by contract" from "this host has no registry"; and driver-memory never errors at all (absent key → all comparisons equal → stable order), so a driver-locus fix needs re-implementing in sql / memory / turso / mongodb / sqlite-wasm with no shared seam.

Why not materialize (option 3): contradicts the stated "formula is virtual" contract. Worth recording the near-miss: the engine could sort post-hoc after applyFormulaPlan, which looks like a fix and is a trap — the driver has already applied limit/offset, so it reorders an arbitrary page. Right on small result sets, wrong the moment pagination exists.

On the routing disagreement: the filer's domain:engine-core instinct was not wrong, it was about a different half — see §4. Triage's domain:metadata routing is right for the half that was actionable, so this is not a STOP.

4. Does an ingress-only fix leave callers uncovered? Yes — measured, and stated plainly.

Same script re-run with the fix in place, both halves visible in one run:

PROTOCOL  {"sort":"sort_key"}          REFUSED 400 INVALID_SORT: … a formula field on 'repro_contact' …
PROTOCOL  {"orderBy":["sort_key"]}     REFUSED 400 INVALID_SORT
PROTOCOL  {"sort":"-sort_key"}         REFUSED 400 INVALID_SORT

FORMULA   orderBy sort_key asc         ["C","A","E","B","D"]     engine.find direct — still silent
   asc === desc (byte-identical)?      true

Covered: everything reaching findData — REST list, POST /data/:object/query, the export route (its $orderby funnels through findData), the RPC dispatcher. Not covered: internal callers reaching engine.find() directly. This PR does not close #6994's defect for those callers, and should not be read as doing so. The test suite carries a pin explicitly labelled RECORD OF A KNOWN HOLE that asserts the unfixed behaviour and says it should go red the day that half lands.

Filed as #7095 (domain:engine-core suggested, unassigned) with three options costed, because closing it means deciding whether engine.find refuses or keeps its documented internal-caller tolerance — a contract decision, not a gate fix, and planFormulaProjection currently acts on the tolerant posture (it drops virtual names from the projection rather than refusing).

5. Reverse verification — direction predicted before running

Predictions were written to a file before the first run. Every category preserved:

	prediction	result
R1 remove the gate, keep tests	8 wire-spelling cases + 2 message cases RED	✅ 10 failed / 95 passed — count matched
R1 · 2 precedence pins	GREEN both directions	✅ green both ways — guards, not evidence (those verdicts predate this change)
R1 · summary control	GREEN both directions	✅ green both ways — can only go red on over-refusal
R1 · known-hole pin	GREEN both directions	✅ green both ways — it pins the half not fixed
R2 revert only the stub driver's row copy	only the known-hole pin RED	✅ 1 failed / 104 passed

One missed prediction, with its cause. The known-hole pin went RED on first write: desc returned E D C B A. Cause was not product code — the conformance stub driver handed back live references into its own store, and applyFormulaPlan writes each computed value onto the record it is given, so one read persisted the virtual value into "the database" and the next read really sorted by a column no driver has. The real-SqlDriver measurement (asc === desc) is what settled which side was wrong: the double was contradicting the driver it stands in for. Fixed the double (copy rows out), did not weaken the assertion. R2 exists to measure whether that copy was load-bearing elsewhere — it was not.

No prediction was left unmeasured.

Process note against myself: I took the fix out with git checkout <base> -- <path> before committing, which overwrote uncommitted work in protocol.ts and cost a re-apply. The patch-file / temp-commit recipe exists for exactly this; R2 was run after committing.

6. Corpus count (widening discipline — counted before changing)

6 formula fields in the example corpus (showcase budget_remaining, f_formula; CRM is_closed, expected_revenue, days_to_close, full_name) — none is a sort target anywhere. The sorts actually declared in the example apps are budget (currency), estimate_hours (number), due_date (date) — all stored types. Zero false positives: the refusal only rejects calls that were already broken.

7. Verification + CI

Local: objectql 162 files / 2800 tests passed; metadata-protocol 65 files / 827 tests passed; objectql typecheck clean; eslint --no-inline-config on both changed files clean; check:nul-bytes / check:error-code-casing / check:route-envelope / check:empty-changeset / check:doc-authoring / check:adr-anchors OK.

check:query-options-erasure went red locally first — two as any in the new pin pushed the test surface 256 → 258. The options were not deliberately off-contract, so the remedy was typing them rather than the as unknown as EngineQueryOptions escape the gate offers for bad input. Back to 256, none new.

CI per-job conclusions on af7e34d7d:

job	conclusion
ESLint (carries the family gates)	completed / success
TypeScript Type Check	completed / success
Test Core (1/3, 2/3, 3/3) + rollup	completed / success
Build Core · Check Changeset · Dogfood Regression Gate (1-3) · Dogfood Verify CLI · Temporal Conformance (live PG + MySQL) · Console Pin Freshness · Check PR Size · ADR maintainer approval · docs links	completed / success

No red jobs. No skip-changeset label: this PR ships .changeset/sort-formula-field-refusal.md (minor).

8. Out-of-scope findings (filed unassigned, not fixed here)

• engine.find() still drops a formula ORDER BY silently — decide whether the engine refuses or keeps its internal-caller tolerance #7095 — engine.find() still drops a formula ORDER BY silently; decide refuse-vs-tolerance. Carries the §4 measurement, three costed options, and the post-hoc-sort trap. Also folds in the consolidation question: "which types materialise no column" is now spelled in six places, none in packages/spec (I added the sixth deliberately — local and documented — rather than opening a cross-package spec edit for one string, per this dispatch's spec STOP).
• Grid/list columns offer a sort header on formula fields, which the server refuses as of framework#6994 objectui#3950 — ObjectGrid disables the sort header only for reference types (isExpandableFieldType: lookup / master_detail / tree / user), so a formula column stays click-sortable. After this PR that click turns from "nothing happens" into a 400. Same root cause: the UI offers a sort the platform cannot perform.

9. Unverified / left open

• Not measured: whether RelatedList and the other objectui data-table renderers derive sortable through the same predicate. Stated as unverified in objectui#3950 rather than asserted.
• The ad-hoc repro script's SUMMARY orderBy child_total desc line is not usable as evidence — I seeded that column in descending insertion order, so "sorted" and "insertion order" coincide there. The non-vacuous summary control is the one in the test file (values chosen so asc E C D B A, desc A B D C E, insertion C A E B D and title A B C D E are pairwise different).
• Not re-derived (already settled by assertSortFieldsExist's dotted-path SORT hint also prescribes an unmaterializable "formula or rollup" denormalization — same defect class as #6673, different axis #6924, and independently re-confirmed only at the DDL level here): that summary sorts correctly end-to-end on a real driver.

Not marked ready, auto-merge not armed — left to the PM.

---

Generated by Claude Code