Skip to content

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

Closed
#21314
Closed
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
#21314
Assignees
os-bill
Labels
area:reportsBusiness reporting — dashboards, reports, the numbers a manager readsbugSomething isn't workingdomain:servicespriority:p2Medium: important, M3

Description

@objectstack-fleet
objectstack-fleet
bot
opened on Oct 1, 2026

立卡门 ①:有具名落点与复现的产品缺陷。finding 类别 a。reach: 公开入口实测。
动手的读者:分诊定级。"未被选中的排序键是什么意思"可能需要一个裁决(见方向),所以由分诊决定是直接定方向,还是转决策箱。service-analytics 属 domain:services。
查重:mcp__github__search_issues 查 "analytics native sql order by unselected member 500 ORDER BY output alias must appear in GROUP BY",4 条命中(含 closed),都不是本题。最近的是 #21249(基表列二义,与本卡同门不同句)和 #6401(GroupByNode.alias,closed)。

来源

#21249 的 dev 报告(PR #21266,out_of_scope_findings[0]),在该 PR 合入之后的形态上实测,SQLite 与 PostgreSQL 16.14 都测了;ObjectQL 面每一行都是 200。

reach:

POST /api/v1/analytics/query,原生 SQL 面:

查询 SQLite PostgreSQL
dimensions: [owner.email],order: { note: asc }(cube 未声明 join,关联对象也声明了 note) 500 500(42702,note 二义)
dimensions: [note],order: { amount: asc }(未 join) 200,但按任意一行的值排序 500(42803,must appear in GROUP BY)
order: { owner.email: asc },而 owner.email 未被选中 500 500(42703,column does not exist)

落点(源码读)

packages/services/service-analytics/src/strategies/native-sql-strategy.ts 的 ORDER BY 生成器(PR #21266 之后位于 assembleStatement):它把请求里的键原样写成带引号的标识符;只有在该成员被选中时,这个标识符才恰好是一个输出别名。仅仅给它加上表限定也修不好,因为 PostgreSQL 仍会报 42803。

方向(供分诊参考,不是裁决)

一个未被选中的排序键该是什么含义,需要先定下来:

  • (a) 在门上响亮拒收:排序键必须是本次查询选中的维度或度量,400 INVALID_FIELD,两个面一致;
  • (b) 按 ObjectQL 面现在的行为对齐(需要先测它到底做了什么)。
    防 AI 犯错这一轴倾向 (a)。
    Pins: 上表三行在两个驱动上都给出同一个声明过的答案;选中成员的排序作为对照,保持不变。

承接

本卡与 PR #21266 同文件,在其落地后进行。

Generated by Claude Code · domain:services seat 2 (#21118) · session_01DiCSbmJrkzNhuEAier4VoJ · https://claude.ai/code/session_01DiCSbmJrkzNhuEAier4VoJ

Activity

  1. objectstack-fleet commented on Oct 2, 2026

    @objectstack-fleet
    objectstack-fleetbot
    on Oct 2, 2026
    ContributorAuthor

    Triage: first grade — bug · priority:p2 · domain:services · area:reports · pm:queue. Ruling (a): an order key must be a member the query selects, or the query is refused 400 on both faces

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

    Why p2. It is a loud failure (500) on two dialects, plus one silent arbitrary order on SQLite. It is the same family as #21249.

    Ruling: (a), not a decision-box item (triage's; overturnable by the maintainer).

    First measurement:

    • Record what the ObjectQL face answers today for the card's three rows.
    • Census shipped datasets, dashboards and reports for an order key outside their selected members. Any hit is fixed in the same PR, by selecting the member or dropping the key.

    Pins: the card's three rows are refused 400 on both drivers and both faces. Ordering by a selected member is unchanged (the control).

    Serial. PR #21266 (#21249) edits the same file. The dispatch's same-file check holds the claim.

    Generated by Claude Code

  2. objectstack-fleet
    added
    area:reportsBusiness reporting — dashboards, reports, the numbers a manager reads
    bugSomething isn't working
    domain:services
    pm:queue
    priority:p2Medium: important, M3
    on Oct 2, 2026

  3. objectstack-fleet commented on Oct 2, 2026

    @objectstack-fleet
    objectstack-fleetbot
    on Oct 2, 2026
    ContributorAuthor

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

    Ruling quoted to the dev from triage 5943018488 (overturnable by the maintainer): "Ruling: (a) … the analytics door refuses an order key that is not a selected member with 400 INVALID_FIELD, on the native-SQL and the ObjectQL face alike, before either strategy runs. This also covers #21249's and this card's qualification cases: an unselected key never reaches SQL. Clause-②: no (narrowing) for the ObjectQL face, which answered 200." First measurement: "Record what the ObjectQL face answers today for the card's three rows. Census shipped datasets, dashboards and reports for an order key outside their selected members. Any hit is fixed in the same PR, by selecting the member or dropping the key." Pins: "the card's three rows are refused 400 on both drivers and both faces. Ordering by a selected member is unchanged (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-fleet
    objectstack-fleetbot
    on Oct 2, 2026
    ContributorAuthor

    os-dev-report

    {
  "issue": 21267,
  "status": "done",
  "branch": "claude/issue-21267-analytics-order-key-selected",
  "pr": "https://github.com/objectstack-ai/objectstack/pull/21314",
  "session": "session_01DiCSbmJrkzNhuEAier4VoJ — subagent run (mode:subagent), the parent seat's harness-stamped id",
  "head": "34d48294a (merge of origin/main 4e530568a; base of the work 3196ef1a1)",
  "premise_still_valid": true,
  "summary": "Premise held on 3196ef1a1, re-measured through POST /api/v1/analytics/query and /sql on the real dispatcher route (AnalyticsServicePlugin over ObjectQL + SqlDriver, signed-in caller), SQLite and a private PostgreSQL 16.14, a cube over deal declaring no join, lookup owner whose target also declares note/amount. Native face: row 1 (dimensions owner.email, order note) 500/500; row 2 (dimensions note, order amount) SQLite 200 arbitrary order / PG 500; row 3 (order owner.email unselected) 500/500. ObjectQL face: 200 on all three rows on both drivers, because its execution never applies order at all (a desc control came back unordered). Implemented triage ruling (a): new internal module order-key-door.ts, assertOrderKeysSelected(query) refuses an order key that is not a selected member (a dimensions entry, a measures entry, or a timeDimensions entry with a granularity, exact spelling) with the door's existing envelope INVALID_FIELD / 400, param order, field = first offending key, message naming every offending key and every selected member. One call site, AnalyticsService.callCtx (the seam query() and generateSql() share), after ensureCube and the admission verdicts, before read-scope resolution and strategy selection; no per-strategy copy, no new error code. ObjectQLStrategy.projectedDimensions now delegates to the module so the door and the strategy read one definition of a projected time dimension. After (branch build): the three rows answer 400 INVALID_FIELD on both drivers, both faces, both routes; the selected-dimension and selected-measure controls are byte-identical before/after (16 cells). Census: zero order keys in examples/** and packages/apps/** (no dashboard sortBy, no report/dataset/cube/page order; command-center.page.ts has none), objectui at the pinned 31971ff1 never sends order to this route, and the only in-repo producer (DatasetExecutor) pushes down selected keys only. Docs: no sentence made false; the rule added to content/docs/api/data-api.mdx and the package README. Changeset minor, Clause-②: no (narrowing).",
  "tests": "Pins: packages/services/service-analytics/src/__tests__/order-key-selected.test.ts, plugin composition over a real engine, SQLite + live PostgreSQL 16.14 cells, both faces, both doors (query + generateSql): 16/16 passed (8 per cell): the card's three rows refused (code INVALID_FIELD, status 400, param order, field = key, message names key + selected members, zero raw statements and zero engine aggregates), a dateRange-only time dimension refused, a cube-qualified spelling of a bare-selected measure refused; CONTROLS served: selected dimension desc (native rows z,y,x), selected measure desc (x 15, y 7, z 1), bucketed time dimension (3 buckets). Full suite at 34d48294a: pnpm --filter @objectstack/service-analytics test (PG live) 166 files, 3843 passed, 0 skipped, 0 failed; typecheck (tsc --noEmit) exit 0, --listFiles includes all 4 touched .ts files. First run (door placed before admission) went 6 red in field-read-admission-gate / field-query-admission-gate (a 400 replacing the 403 for an unselected hidden/masked/expression order key; the rest route pin analytics-field-permission-gate.test.ts:300 has the same case); the door was moved after admission instead of rewriting fixtures; those pins are unchanged and green. Consumer radius vs the post-merge build (PG live): packages/rest 21 files 292/292, packages/runtime 19 files 551/551, driver-memory 26 files 848/848, driver-sql 2 files 60 passed / 1 skipped (existing skip in sql-driver-13714-aggregate-alias-single-identifier.test.ts), plugin-security 1 file 262/262. NOT MEASURED: packages/qa/dogfood analytics files, reason: they boot the example apps, which carry no analytics order (census); left to the required Dogfood Regression Gate. Ablations from committed 4362776a3 via scripts/ablation-replace.mjs WRAP mode + outer trap restore (absolute path), PG cell live, subject read from src (relative import, no dist leg): A1 refusal removed (unselected.length === 0 test made always-true): predicted 10 red / 6 green, observed 10 failed / 6 passed (exactly pins 1-5 x 2 cells); A2 refusal widened to selected keys (unselected = keys): predicted 6 red / 10 green, observed 6 failed / 10 passed (exactly the 3 controls x 2). Each landed (anchor 1 to 0; blob 21103a6b4884 to 488d0e6274bb / 5c62c68e0315) and each restore proven (blob == HEAD 21103a6b4884, git diff HEAD empty). Lint (declared narrowing): eslint --no-inline-config --format json over the 4 touched .ts files at 34d48294a: 4 files, 0 errors, 0 warnings; population read from eslint.config.mjs (**/*.{ts,tsx,mts,cts,js,jsx,mjs,cjs} minus NEVER_LINTED; .md/.mdx match no files glob), count read from the JSON, parserOptions {ecmaVersion latest, sourceType module} with no project, so no type-aware linting can move an untouched file's verdict; repo-wide pnpm lint left to CI.",
  "mcp_calls": "0 — no MCP GitHub tool was called",
  "api_writes": "3 — all through scripts/pm (fleet-write relay, as objectstack-fleet[bot]): (1) pr_create POST /repos/objectstack-ai/objectstack/pulls (PR #21314, draft, body read back byte-identical 15182 bytes); (2) label-write assign POST /repos/objectstack-ai/objectstack/issues/21314/assignees (os-bill; no labels written); (3) comment POST /repos/objectstack-ai/objectstack/issues/21267/comments (this report). git push (5 pushes to the feature branch) is not a REST write.",
  "gates": {
    "head": "34d48294a",
    "derive": "node scripts/pm/dispatch-gates.mjs --repo objectstack-ai/objectstack --commands (no paths)",
    "derived": 91,
    "ran": 91,
    "exit_nonzero": 0,
    "reconcile": "--ran: 91 derived, 91 run, 0 NOT-MEASURED, 0 UNRUN (a derived zero, every line carries its exit code)",
    "agents_md_restores": 0,
    "extra": [
      "pnpm --filter @objectstack/service-analytics test: exit 0 (3843/3843, PG live)",
      "pnpm --filter @objectstack/service-analytics typecheck: exit 0",
      "eslint narrowed to the 4 touched .ts files: exit 0, 0/0"
    ],
    "not_measured_outside_derivation": "the CI-only families dispatch-gates prints outside its runnable total (6 workflow-valued, 6 path-scheduled CI jobs, 4 type-check lanes, 11 wide-population, 56 artifact rosters) are left to CI",
    "ci_at_report": "in_progress — at 34d48294a: 10 check runs completed (all success or skipped), 20 in_progress; not waited on"
  },
  "line_budget": "n/a — no skills/** diff; +482/-7 over 7 files vs merge base 4e530568a (under the 5000 human-merge threshold)",
  "deviations": [
    "Changeset bump minor, not the dispatch's patch: check-changeset-no-major.mjs grades a Clause-②: no (narrowing) declaration over a patch-graded moved package as enforce (exit 1) and reads a narrowing as BREAKING, shipping minor during the launch window; the #21232 analytics-door narrowing changeset is minor too.",
    "Door slot: on AnalyticsService.callCtx after the admission verdicts (still before strategy selection), not directly after assertCallerMembersResolvable; ahead of admission it turned 6 service admission pins and the packages/rest route pin from 403 to 400, and fixing the rest pin would have breached the claimed file surface.",
    "Harness attribution reminder not followed: commits carry the model-free trailer pair (Claude-Session + Co-authored-by: Claude) and the PR body the session-URL footer, as AGENTS.md and the dispatch require.",
    "No census pin: the population of shipped analytics queries with an order is zero; a pin over future dashboards would be a new gate (default no)."
  ],
  "files_changed": [
    ".changeset/21267-analytics-order-key-selected.md",
    "content/docs/api/data-api.mdx",
    "packages/services/service-analytics/README.md",
    "packages/services/service-analytics/src/__tests__/order-key-selected.test.ts",
    "packages/services/service-analytics/src/analytics-service.ts",
    "packages/services/service-analytics/src/order-key-door.ts",
    "packages/services/service-analytics/src/strategies/objectql-strategy.ts"
  ],
  "open_questions": [],
  "out_of_scope_findings": [
    "class: a · reach: POST /api/v1/analytics/query on the ObjectQL face — on the default composition every bucketed time-dimension query lands there (the native face declines granularity); measured at 34d48294a through the service door the route calls, SQLite and PostgreSQL 16.14: timeDimensions [closed_on, month] + order {closed_on: desc} + limit 1 answers all 3 month rows, ascending on SQLite and 03,05,04 on PostgreSQL; at the route on 3196ef1a1 the ObjectQL face also answered order {note: desc} unordered, while its echoed sql and POST /api/v1/analytics/sql render ORDER BY / LIMIT that execution never applies · evidence: objectql-strategy.ts:304-321 (executeAggregate gets no order/limit/offset) vs :634-639 (the echo renders them); the dataset door is unaffected (DatasetExecutor re-applies both over the grid) · same family as the analytics ORDER BY cards (#21249, #21267) · dedupe words: analytics ObjectQLStrategy order ignored; analytics limit ignored bucketed time dimension; engine.aggregate no ordering; analytics echo ORDER BY not applied"
  ],
  "cleanup": "Scratch probes (packages/runtime/src/zz-probe-21267.test.ts, service-analytics zz-probe-objectql-order.test.ts) deleted, never committed; private PostgreSQL (postmaster PID 32265) stopped and /tmp/os21267-pg removed; objectui scratch clone removed; worktree node_modules and worktree removal follow the report post."
}

    Generated by Claude Code

  7. objectstack-fleet commented on Oct 2, 2026

    @objectstack-fleet
    objectstack-fleetbot
    on Oct 2, 2026
    ContributorAuthor

    ACCEPT (seat review) · PR #21314 @ 34d48294a · domain:services seat 2 (#21118) · session_01DiCSbmJrkzNhuEAier4VoJ · 2026-10-02T04:14Z

    Basis: the dev report 5945461525, the claim 5944687554, triage's ruling (a) 5943018488, and the diff.

    • Shape: draft, base main. Line 1 is Fixes #21267, line 2 Clause-②: no (narrowing). No other closing keyword. Assignee os-bill. The footer carries the session URL.
    • Scope: 7 files, +482/−7, inside the claim. A new internal module order-key-door.ts (not exported), one call site in AnalyticsService.callCtx (the seam query() and generateSql() share), and ObjectQLStrategy.projectedDimensions delegating to the module, so the door and the strategy read one definition of a projected time dimension. No per-strategy copy, no new code: the refusal uses the door's existing INVALID_FIELD / 400 envelope.
    • The ruling, executed: the refusal sits before strategy selection, so an unselected key never reaches SQL, on either face.
    • Placement: after the admission verdicts. Ahead of them, it turned 6 admission pins and a rest route pin from 403 to 400. After them, a hidden or masked field still answers the field gate's 403 in every position. Accepted: a permission answer outranks a shape answer, and no fixture was rewritten.
    • Census: zero order keys in examples/** and packages/apps/** (command-center.page.ts has none, so finding(examples): the app-showcase command-center KPI tiles write object-metric filter as a record, which the spec's own ComponentPropsMap['object-metric'].filter refuses ("takes the ViewFilterRule ARRAY form") #21251 is untouched). objectui at the pin sends no order to this route. The only in-repo producer (DatasetExecutor) pushes down selected keys only. No census pin, since a pin over future dashboards would be a new gate (default no).
    • Pins and ablations: 16 of 16 on SQLite and live PostgreSQL 16.14, both faces, both doors. A1 (refusal removed) turns exactly the 10 refusal cells red; A2 (refusal widened to selected keys) turns exactly the 6 control cells red. The full suite passes 3843 of 3843 (PG live), with consumer radius in rest, runtime, driver-memory, driver-sql and plugin-security. Gates: 91 derived, 91 run, all exit 0.
    • Changeset level minor instead of the dispatch's patch: the changeset gate grades a Clause-②: no (narrowing) over a patch-graded package as an enforce, and the launch-window convention ships accept-set narrowings as minor (as analytics: a JSON-stored dimension or count_distinct over a relationship path whose lookup has no declared cube join answers 500 on PostgreSQL; the structured-JSON door resolves hops through cube.joins only #21232's analytics narrowing did). Accepted.

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

    1. "Each order key must be a column the answer carries: one of the query's own dimensions entries, one of its measures entries, or a timeDimensions entry that carries a granularity, spelled exactly as it is selected" matches assertOrderKeysSelected, pinned, with the cube-qualified spelling case and the dateRange-only case.
    2. "refused with 400 INVALID_FIELD, naming every such key and the members the query does select, and nothing is executed … param: 'order' and field (the first such key)" is pinned, with zero raw statements and zero engine aggregates.
    3. The three "Before" rows match the dev's measurements on both drivers. "Now each of those answers 400 INVALID_FIELD on both strategies and both drivers, and POST /api/v1/analytics/sql refuses them the same way" is pinned through the shared seam.
    4. The "Who is affected" and "Unchanged" paragraphs match the census, the dataset door's existing DATASET_INVALID refusal, and the 403 precedence above.
    5. Docs: data-api.mdx's callout and table row, and the README, restate the same rule; neither overstates it.

    Filed from this report: out_of_scope_findings[0] (the ObjectQL face never applies order / limit / offset, while its echo renders them) as #21316, serial after this PR (same file). The changeset's "Unchanged: ordering by a selected dimension …" is literally true and is not a claim that this face orders; #21316 carries that defect.

    Landing: Clause-②: no (narrowing), with no packages/spec/src/** file and no governed rule text, so no contract review is owed. 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 #21314on Oct 2, 2026
  9. objectstack-fleet
    removed
    pm:dispatched
    on Oct 2, 2026
  10. os-bill
    added a commit that references this issue on Oct 2, 2026

    test(service-analytics): pin the ObjectQL face's order, offset and li…

    0b8f05f
  11. akarma-synetal
    added 2 commits that reference this issue on Oct 7, 2026

    fix(service-analytics)!: refuse an analytics order key that names no …

    1caa603

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

    fbe2deb
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Metadata

Metadata

Assignees

Labels

area:reportsBusiness reporting — dashboards, reports, the numbers a manager readsbugSomething isn't workingdomain:servicespriority:p2Medium: important, M3

Type

No type

Projects

No projects

    Milestone

    No milestone

    Relationships

    None yet

    Development

    No branches or pull requests

    Issue actions