analytics: the ObjectQL face never applies a query's order, limit or offset (every bucketed time-dimension query lands there), while its echoed SQL and /analytics/sql render ORDER BY and LIMIT #21316

Activity

1. objectstack-fleet commented on Oct 2, 2026

   

   objectstack-fleetbot

   on Oct 2, 2026

   ContributorAuthor

   More actions

   Triage: first grade — bug · priority:p1 · domain:services · area:reports · pm:queue. The ObjectQL face applies order, limit and offset, as its own echoed SQL says

   Triage seat (objectstack-wide, seat post #6015) · session_01AavokzJ5DndAwitDXvKy4U · 2026-10-02T04:56Z. ⛔ Not a claim, ⛔ not a dispatch.

   Why p1. It returns a silent wrong answer with broad reach. Every bucketed time-dimension query lands on this face, so a "top N" or ordered widget is answered unordered and unlimited. The echoed sql and /analytics/sql claim the opposite. It was measured on SQLite and PostgreSQL.

   Direction.

   • The face applies order (selected members only, after analytics: on the native-SQL strategy an order key that is not a selected member answers 500 (ambiguous, must appear in GROUP BY, or no such column); the ObjectQL face answers 200 for the same queries #21267's door), then offset and limit, to the aggregated rows. That way the answer and the echoed statement agree.
   • ⛔ No second ordering rule. It reads the same resolved order the native face compiles.

   Pins: the card's rows, as ordered and limited answers on both drivers, equal to the native face where both answer (the control).

   Serial. It is the same package as #21267's PR #21314. The dispatch's same-package check holds the claim.

   ---

   Generated by Claude Code
2. objectstack-fleet

   added

   area:reportsBusiness reporting — dashboards, reports, the numbers a manager readsBusiness reporting — dashboards, reports, the numbers a manager reads

   bugSomething isn't workingSomething isn't working

   domain:services

   pm:queue

   priority:p1High: required for production / M2High: required for production / M2

   on Oct 2, 2026
3. objectstack-fleet commented on Oct 2, 2026

   

   objectstack-fleetbot

   on Oct 2, 2026

   ContributorAuthor

   More actions

   Claim: PM loop round 1 · 2026-10-02T06:03Z
   Session: session_01DiCSbmJrkzNhuEAier4VoJ
   Account: os-bill (the seat's linked user as GET /user answers it; the card's assignee)
   Branch: claude/issue-21316-objectql-face-order-limit
   Worktree: objectstack-issue-21316
   Domain: domain:services
   Seat: domain:services#2 (seat post #21118)
   File surface:

   • packages/services/service-analytics/src/: the ObjectQL strategy (strategies/objectql-strategy.ts) applies the query's order (selected members only, after analytics: on the native-SQL strategy an order key that is not a selected member answers 500 (ambiguous, must appear in GROUP BY, or no such column); the ObjectQL face answers 200 for the same queries #21267's door), then offset and limit, to the aggregated rows, on the base path and the cross-object path alike, reading the same resolved order the native face compiles (⛔ no second ordering rule). Plus tests.
   • Pins on SQLite and PostgreSQL (under the touched package or packages/qa/dogfood/test/), docs sentences this change makes false, and a changeset.
     Stop on breach; explain in the report.
     Container & model: M, mode:subagent, model: opus (no path-derived mandate; default tier).
     Clause-②: no
     Thread-read: 5945861102
     Serial constraints cleared, read in this act against origin/main at 97239c3c8: PR fix(service-analytics)!: refuse an analytics order key that names no member the query selects #21314 (analytics: on the native-SQL strategy an order key that is not a selected member answers 500 (ambiguous, must appear in GROUP BY, or no such column); the ObjectQL face answers 200 for the same queries #21267, the same package) merged as 1caa60373. No open PR touches packages/services/service-analytics/. No open pm:dispatched claim declares it.
     Selection: priority:p1 (triage 5945861102), under the maintainer's 「优先 p1 的卡片」 and 「并发保持3」. It takes the slot freed by runtime strings in the domain:services packages carry tracker numbers (168 messages in 17 packages, 263 ledgered ids): this lane's share of the #20513 A/A burn-down #20751 stage 3's report. os package install (install-local) drops an app's script action bodies: REST 404 and MCP run_action "No handler registered" while list_actions advertises the action #21321 (p1) takes the next free slot.

   Ruling quoted to the dev from triage 5945861102 (overturnable by the maintainer): "The face applies order (selected members only, after #21267's door), then offset and limit, to the aggregated rows. That way the answer and the echoed statement agree. ⛔ No second ordering rule. It reads the same resolved order the native face compiles." Pins: "the card's rows, as ordered and limited answers on both drivers, equal to the native face where both answer (the control)."

   ---

   Generated by Claude Code · https://claude.ai/code/session_01DiCSbmJrkzNhuEAier4VoJ
4. objectstack-fleet

   added

   pm:dispatched

   and removed

   pm:queue

   on Oct 2, 2026
5. objectstack-fleet

   assigned

   os-bill

   on Oct 2, 2026
6. objectstack-fleet commented on Oct 2, 2026

   

   objectstack-fleetbot

   on Oct 2, 2026

   ContributorAuthor

   More actions

   os-dev-report
   {
   "issue": 21316,
   "status": "done",
   "branch": "claude/issue-21316-objectql-face-order-limit",
   "pr": "#21363",
   "session": "session_01DiCSbmJrkzNhuEAier4VoJ",
   "premise_still_valid": true,
   "summary": "Premise re-measured on main 97239c3. It ran through the real POST /api/v1/analytics/query and /analytics/sql dispatcher route, using AnalyticsServicePlugin (default composition, and capabilities narrowed to the engine aggregate), a real ObjectQL engine and SqlDriver, on SQLite and on PostgreSQL 16.14 (a private cluster, C.UTF-8). The card's rows reproduce: bucketed closed_on month with order desc and limit 1 answered every month (ascending on SQLite; 04, 03, 05 on PG). A selected dimension ordered desc came back unordered. A selected measure with limit 2 offset 1 answered every group. The echo (debugSql) and /sql rendered ORDER BY / LIMIT / OFFSET for each. FIX: ObjectQLStrategy.orderAndWindow applies query.order, then offset and limit, to the remapped answer, on the base path and on the cross-object path after the re-bucket. It uses the package's one comparator and window, applyOrdering and applyWindow from dataset-executor.ts. Nothing is copied and no implicit ordering is added. Push-down is impossible: EngineAggregateOptions and the StrategyContext.executeAggregate contract declare no order or limit. SHIPPED DEFECT THIS TOUCHES, fixed here: DatasetExecutor pushes a single query's window down and then re-windowed the answer, so offset was applied twice. Measured on main through queryDataset: limit 2 offset 1 over 5 groups answered 1 row on the native face on both drivers. This change would have extended that to the ObjectQL face, which was right only because it dropped the window. The post-pass now windows only a grid it could not push down. After the fix, at the route on both drivers: the bucketed card row answers the newest month alone, and the ObjectQL face answers the native face's rows. FALSIFIED PM ASSUMPTION (Zone 2 item 3): matching native ORDER BY semantics everywhere is impossible without a second rule. Native NULL placement differs by driver (SQLite lowest, PG highest), while applyOrdering keeps NULL and empty string last in both directions, compares numeric text numerically, and uses localeCompare for other text. The faces therefore agree on numbers and on single-case ASCII text only. This is reported as open question 1, not invented. Docs: no page in content/docs (outside releases/) or skills/ says the ObjectQL face ignores order or limit, so no doc sentence changes. Code comments that said so are corrected in the dataset-executor.ts header and post-pass and in the order-key-door.ts header.",
   "tests": "Every number below is at head a317977 (main merged in; service-analytics byte-identical to c6c4ae9) unless stated. (1) OS_TEST_POSTGRES_URL=postgres://postgres@127.0.0.1:54316/os21316 pnpm --filter @objectstack/service-analytics test, after turbo build of its closure: Test Files 167 passed (167), Tests 3869 passed (3869); os-verify-lock VERDICT command-exit 0. (2) pnpm --filter @objectstack/service-analytics typecheck: exit 0; tsc --listFiles includes both touched test files. (3) New pins, src/tests/objectql-face-order-limit.test.ts: 26 passed (13 SQLite + 13 live PG). They cover the bucketed card row on both compositions with no raw SQL run; a bucketed window; equality with the native face for a selected dimension (card row), a selected measure with offset/limit, two order keys, limit 0, and the cross-object path by measure and by relationship-path dimension; the echoed sql executed on the same driver equalling the answer; the CONTROL that with no order/limit/offset the rows equal the engine aggregate's rows in order; and the dataset door's pushed-down page on both faces. The #21267 controls in order-key-selected.test.ts now assert exact order on both faces. (4) Ablations, at committed c6c4ae9, each through node scripts/ablation-replace.mjs (anchor x1 to x0, replacement x0 to x1, blob changed; restore proven: blob == HEAD and git diff HEAD empty), with the pins imported from src/ so no dist/ is involved. A1 ordering removed: 17 failed, 9 passed. A2 window removed: 18 failed, 8 passed. A3 ordering inverted: 20 failed, 6 passed (every native-equality pin and the controls red on both drivers). A4 executor re-windows a pushed-down page: 4 failed, 22 passed (exactly the dataset-door pins). Control after restore: 26 passed. Two first attempts were void and re-run: the first A4 was a no-op that the tool refused, because its replacement was a substring of the anchor; and on an earlier fixture, SQLite's ascending group order kept two order pins green under A1, so the fixture was hardened and all legs re-run. (5) Gate union, node scripts/pm/dispatch-gates.mjs --repo objectstack-ai/objectstack --commands (merge base 96b12b5): 63 commands, all exit 0. --ran reconciles 63 derived, 63 run, 0 NOT-MEASURED. check:dual-build-cjs-loads first answered PREREQUISITE NOT MET (exit 3, no dist) on the earlier head, and answered 0 after a full turbo build, both there and at a317977. (6) eslint --no-inline-config --format json on the 5 touched .ts files: 5 files, 0 errors, 0 warnings, exit 0. eslint.config.mjs enables no type-aware linting (no parserOptions.project), so untouched files' verdicts cannot move. The repo-wide pnpm lint is left to CI. (7) Route-level before/after readings, from a scratch harness that was deleted and never committed, are logged in the PR body. CI not awaited: in_progress.",
   "mcp_calls": "0 — no MCP GitHub tool called; reads used gh api (REST GET)",
   "api_writes": "3 relay writes plus git push. (a) pr_create through scripts/pm/fleet-write/dispatch.mjs: POST /repos/objectstack-ai/objectstack/dispatches, executed as POST /repos/objectstack-ai/objectstack/pulls; PR #21363, draft, body read back with 9294 bytes sent and 9294 stored, identical. (b) scripts/pm/label-write.mjs --issue 21363 --assign os-bill: one relay dispatch, executed as POST /repos//issues/21363/assignees; read-back MATCHES. (c) This os-dev-report comment through scripts/pm/post-stamped.mjs: POST /repos//issues/21316/comments. git push of claude/issue-21316-objectql-face-order-limit (not REST).",
   "gates": "head a317977: 63/63 derived gate commands exit 0 (--ran: 63 derived, 63 run, 0 NOT-MEASURED, derived zero); service-analytics test 3869/3869 with live PG; typecheck exit 0; narrowed eslint 0/0; CI: in_progress, not awaited",
   "line_budget": "n/a — no skills/** or ledger file touched",
   "files_changed": [
   ".changeset/21316-objectql-face-order-limit.md",
   "packages/services/service-analytics/src/strategies/objectql-strategy.ts",
   "packages/services/service-analytics/src/dataset-executor.ts",
   "packages/services/service-analytics/src/order-key-door.ts",
   "packages/services/service-analytics/src/tests/objectql-face-order-limit.test.ts",
   "packages/services/service-analytics/src/tests/order-key-selected.test.ts"
   ],
   "deviations": [
   "dataset-executor.ts is changed beyond the strategy the claim names, but inside its file surface (packages/services/service-analytics/src/). Reason: a shipped defect this change touches and would have extended to the ObjectQL face — the pushed-down offset was applied twice. Measured on main and pinned on both faces and both drivers.",
   "Zone 2 item 3 is falsified as stated. The native ORDER BY's NULL placement and collation differ by driver, and the one existing comparator (applyOrdering) is documented as nulls-last, so the ObjectQL face matches the native face only on numbers and on single-case ASCII text. It was reused rather than forked, per the ruling ⛔ no second ordering rule. See open question 1.",
   "The route was measured in-process: the real createDispatcherPlugin route, AnalyticsServicePlugin, ObjectQL engine and SqlDriver, rather than a pnpm dev boot. PostgreSQL was a private 16.14 cluster on port 54316 (C.UTF-8 collation), stopped after the run.",
   "origin/main was merged into the branch (a317977) before the PR, per AGENTS.md Multi-agent discipline §10. The package suite and the full gate union were re-run on the merged head.",
   "The #21267 control pins in order-key-selected.test.ts were tightened from set-equality to exact order on the ObjectQL face, because their comment would otherwise be stale.",
   "The changeset is a patch with Clause-②: no, as the dispatch asked. check:adr-0087-registration, check:changeset-no-major and check:empty-changeset all exit 0. Note that the ObjectQL face's answer for a negative limit changes from every row to applyWindow's slice; see open question 2."
   ],
   "open_questions": [
   {
   "question": "The ObjectQL face now orders with the package's one comparator (applyOrdering, nulls and empty strings last in both directions, numeric text numeric, localeCompare). The native face's ORDER BY places NULL lowest on SQLite and highest on PostgreSQL, keeps '' as a string, and collates text by codepoint (SQLite BINARY; PG under C.UTF-8). These were measured at the route on this branch with tags null, '', '10', '9', 'B', 'a'. Where both faces answer, they disagree on those cells, and the echoed ORDER BY does not reproduce them. The dataset door already re-sorts native output with the same comparator, so its answer and its echo disagree on the same cells today. Should the two faces be made to agree, and how?",
   "options": [
   "A — Keep one comparator as is: the documented report rule 'Null / empty buckets sort last in both directions'. Accept the divergence and state in the echo docs that NULL placement and collation follow the comparator. Business need: no measured caller orders NULL-bearing groups, and dashboards and reports already read this rule. Long-term: one rule, no fork. AI-error axis: an AI reading the echo can still be misled on NULL cells. Startup focus: zero new surface.",
   "B — Make SQL agree with the comparator on NULLs: render NULLS LAST in the native ORDER BY and in both echoes (PG and SQLite 3.30+ support it). Collation, numeric text and '' still differ. Business need: same as A. Long-term: answer and echo agree on NULLs across faces. AI-error axis: fewer silent disagreements. Startup focus: one small native-face change, no new key.",
   "C — A dialect-aware comparator that mimics each driver. Rejected by the ruling (⛔ no second ordering rule), and it binds answers to the driver."
   ],
   "recommendation": "A in this PR (done), and B as its own card only if the maintainer wants echo and answer to agree on NULL cells. On the four axes: no measured business pull for NULL-group ordering yet, so 创业阶段不扩散 favours A. B is the contract-tightening move if pull appears. C fails long-term soundness and the ruling."
   },
   {
   "question": "AnalyticsQuerySchema declares limit and offset as z.number(), so negative and fractional values pass the door. Measured at the route on this branch: limit -1 gives native SQLite every row, native PG 500, and the ObjectQL face (now) all but the last row, as the dataset door's applyWindow already does. limit 1.5 gives native SQLite 500, PG 2 rows, ObjectQL 1 row. offset -1 gives native 500 on both drivers. No value of these has one answer across drivers. Refuse them?",
   "options": [
   "A — Tighten the spec: limit and offset become z.number().int().nonnegative() on AnalyticsQuerySchema, and the dataset selection shares the shape, so they are refused 400 at parse. This narrows the accept set: Clause-② no (narrowing), BREAKING minor. Business need: no producer sends such values (a 500 today). Long-term: declared equals enforced, contract-first. AI-error axis: an AI-written negative limit is refused loudly instead of answering a driver-dependent page. Startup focus: removes a dialect rather than adding one.",
   "B — A runtime refusal at the analytics door, like order-key-door.ts, with no spec change. Same narrowing, but the schema still advertises a number it refuses.",
   "C — Leave it as is."
   ],
   "recommendation": "A, as its own card: it changes a spec contract and is a narrowing, outside this card's Clause-② no. Until then, this PR gives the ObjectQL face the dataset door's existing window semantics (one rule), not a new one."
   }
   ],
   "out_of_scope_findings": [
   "class: a · reach: the analytics service door that POST /api/v1/analytics/query calls (AnalyticsService.query, native face, SQLite), measured on this branch at head 0b8f05f with the native face unchanged by this PR: an order plus offset with no limit answered SqliteError near "OFFSET": syntax error (statement '… ORDER BY "owner.email" DESC OFFSET 1'), a 500 DATABASE_ERROR at the route. SQLite requires LIMIT before OFFSET; PG answers. The ObjectQL face's echo and /analytics/sql render the same statement SQLite cannot run. Same family as finding 2 (analytics window validity across drivers). Dedupe words: analytics OFFSET without LIMIT sqlite syntax error; NativeSQLStrategy assembleStatement OFFSET LIMIT -1; analytics offset only 500 sqlite",
   "class: a · reach: POST /api/v1/analytics/query measured at the route on main 97239c3 and on this branch: limit -1 answers 500 on native PG; limit 1.5 answers 500 on native SQLite; offset -1 answers 500 on native SQLite and PG. AnalyticsQuerySchema admits all three (z.number()), and the ObjectQL face and dataset door answer applyWindow slices for them. See open question 2 for the proposed contract tightening. Dedupe words: analytics limit negative 500; AnalyticsQuerySchema limit int nonnegative; analytics offset negative DATABASE_ERROR; analytics limit fractional"
   ]
   }

   ---

   Generated by Claude Code
7. objectstack-fleet commented on Oct 2, 2026

   

   objectstack-fleetbot

   on Oct 2, 2026

   ContributorAuthor

   More actions

   ACCEPT (seat review) · PR #21363 @ a3179775d · domain:services seat 2 (#21118) · session_01DiCSbmJrkzNhuEAier4VoJ · 2026-10-02T07:53Z

   Basis: the dev report 5947663959, the claim 5946467307, triage's direction 5945861102, and the diff.

   • Shape: draft, base main. Line 1 is Fixes #21316, line 2 Clause-②: no. Assignee os-bill. The footer carries the session URL.
   • Scope: 6 files, inside the claim (service-analytics/src/). ObjectQLStrategy.orderAndWindow orders by order, then applies offset and limit, on the direct path and the cross-object path after the re-bucket. It uses the package's ONE comparator and window (applyOrdering / applyWindow, dataset-executor.ts), with no copy. Push-down is impossible: the aggregate contract declares no order or window.
   • A shipped defect this touched, fixed in scope: the dataset door pushed a single query's window down and then re-windowed the answer, so offset was applied twice (limit 2, offset 1 over 5 groups answered 1 row on the native face, on both drivers). It now windows only a grid it could not push down. Measured on main and pinned. Accepted.
   • Pins and ablations: 26 pins (13 SQLite, 13 live PostgreSQL). They cover the card's rows; equality with the native face for a selected dimension, a selected measure with a window, two keys, limit 0 and the cross-object path; the echo executed on the same driver equalling the answer; the no-window control; and the dataset door's page. Ablations A1 to A4 each went red exactly where predicted. The full suite passes 3869 of 3869 with live PG. Gates: 63 derived, 63 run, all exit 0.

   Open question 1, answered by the seat: A (a no-escalate class: verification semantics within the ruling). The native face itself places NULL by driver (lowest on SQLite, highest on PostgreSQL), so there is no single native answer to match on NULL cells. The ruling's "⛔ No second ordering rule" excludes a dialect-mimicking comparator (C). The face keeps the package's one documented comparator (nulls and '' last in both directions), and the changeset states the divergence plainly. Making SQL render NULLS LAST (B) would be its own card if pull appears. There is no measured caller ordering NULL-bearing groups, so it is not filed.
   Open question 2 and both out-of-scope findings: filed as #21365 (negative or fractional limit / offset, and an offset with no limit, answer 500 on the native face; AnalyticsQuerySchema admits them). It is one family card, serial after this PR.

   Changeset checked sentence by sentence (PR #21192 rule):

   1. "Before, the ObjectQL strategy passed none of the three keys to engine.aggregate … and applied none of them itself" matches the source read on #21316.
   2. The three measured rows match the dev's route readings on both drivers.
   3. "Now the strategy orders the answer by order … then applies offset and limit … on the direct path and on the cross-object (FK-expand) path" matches orderAndWindow and its pins.
   4. "A bare limit with no order slices the engine's order" holds: no implicit ordering is added.
   5. The NULL / '' / numeric-text / mixed-case divergence sentence is the measured reading behind Q1 = A.
   6. The "Dataset door." paragraph matches the executor change and its pin.
   7. "Unchanged.": the no-window control is pinned, and the order-key door from analytics: on the native-SQL strategy an order key that is not a selected member answers 500 (ambiguous, must appear in GROUP BY, or no such column); the ObjectQL face answers 200 for the same queries #21267 and the dataset door's own ordering are unchanged.

   Landing: Clause-②: no, no contract face. The PR goes to the queue once every check on this head is green.

   ---

   Generated by Claude Code · https://claude.ai/code/session_01DiCSbmJrkzNhuEAier4VoJ
8. objectstack-fleet

   closed this as completedin #21363on Oct 2, 2026
9. objectstack-fleet

   removed

   pm:dispatched

   on Oct 2, 2026
10. akarma-synetal

    added a commit that references this issue on Oct 7, 2026

    fix(service-analytics): the ObjectQL face applies a query's order, of…

    ...

    This issue will close once commit fbe2deb is merged into the 'main' branch.

    fbe2deb