analytics native SQL: an all-NULL sum group is folded to 0 after ORDER BY / LIMIT ran on the NULL, so SQLite and PostgreSQL return different sorted pages

Filing gate: ① product defect, a driver-dependent answer at a named location.
Business reason: a dashboard "top N by sum" widget (ORDER BY a summed measure plus LIMIT) shows different rows on SQLite and PostgreSQL for the same data whenever one group's aggregand is NULL in every row. On one driver it also lists a 0 group in a position the sort doesn't allow. People who develop on SQLite and deploy on PG see a different page in production. If nothing is done, every sorted and paged analytics widget over a sparse column carries this.
Reader: domain:spec / analytics seat, the next PR touching packages/services/service-analytics/src/strategies/native-sql-strategy.ts, the #21042 empty-group fold (folds, after executeRawSql).
Dedupe (open AND closed): "analytics sum null group ORDER BY LIMIT differs between SQLite and PostgreSQL nulls first nulls last" → 10 shown of 16, none on this. The nearest is #21042 (closed), which added the fold this defect follows from. "native sql empty group fold after order by limit" was checked as well; it found no open card on it.
reach: observed through AnalyticsService.query, the service the POST /analytics/query handler calls (packages/runtime/src/domains/analytics.ts), on the native-SQL strategy, on both SQLite and PostgreSQL, by the PR #25903 (#25778) dev while writing dataset-duration-measure.test.ts. That test now excludes the all-NULL group for this reason. In-process at the service, not over HTTP.
type: Bug

What happens

A cube query that orders by a sum measure and has a limit, over data where one group's summed field is NULL in every row:

The statement computes SUM(...) as NULL for that group, then ORDER BY places it. SQLite sorts NULL first in ascending order, and PostgreSQL sorts NULL last (the reverse holds for descending).
LIMIT / OFFSET cut the page on that ordering.
After the rows come back, the analytics: the native-SQL strategy skips the engine aggregate policies — SUM/AVG exact decimal on PostgreSQL (not #20387's double), and an all-NULL group sums to null where the ObjectQL face folds it to 0 (#15546) #21042 fold (native-sql-strategy.ts, the folds loop after executeRawSql) rewrites the NULL to 0, the identity emptyGroupValueFor declares for sum.

So the two drivers return different pages for the same query. On at least one driver the folded 0 sits where a 0 cannot sort, for example after positive values in an ascending order.

Why

The fold applies the platform's empty-group identity in JavaScript, after the database has already ordered and paged on the unfolded NULL. The value is folded, but the ordering that SQL computed from it is not.

Expected

The order and the page agree with the folded value, the same on every dialect. One way is to apply the identity inside the statement (COALESCE(SUM(x), 0) for an aggregate with an identity), so ORDER BY sees 0. Another is to give the ordering an explicit null placement that matches the fold. Measures without an identity (avg, min, max) keep NULL, and their NULL placement should then be stated, not left to the driver.

Found by the dev of PR #25903 (dataset duration measure). It is outside that card's scope.

Triage: first grade — `bug` · `priority:p2` · `domain:server` · `area:reports` · `pm:queue` · triage seat · `session_01AavokzJ5DndAwitDXvKy4U` · 2026-10-26T03:54Z

⛔ Not a claim, ⛔ not a dispatch. finding removed.

p2: a driver-dependent sorted page. The analytics: the native-SQL strategy skips the engine aggregate policies — SUM/AVG exact decimal on PostgreSQL (not #20387's double), and an all-NULL group sums to null where the ObjectQL face folds it to 0 (#15546) #21042 empty-group fold runs after ORDER BY / LIMIT. The fold has to happen in the query (COALESCE in the select and the order), or the sort has to see the folded value. The PR feat(spec,analytics,lint,platform-objects,app-shell): a dataset measure can aggregate the time between two datetime fields of each record #25903 test that excludes the all-NULL group should take that group back once this lands.
Same file as analytics: a filtered dataset measure asked on the cube door as CUBE.MEASURE, the name GET /analytics/meta lists, answers the unfiltered aggregate #25906; one PR is natural.

Generated by Claude Code

Claim: PM loop round 1 · card thread · 2026-10-26T04:13Z
Session: session_016NB4rZT58RvxS6vBGLcXqz (the card thread, mode:thread; the seat writes this claim for it: seat session session_01TFnDdepdwyDJeM2kJ4GtMy)
Account: os-elon-musk (the seat's linked user as GET /user answers it; the card's assignee)
Branch: claude/project-thread-6hvadl (platform-assigned to the card thread; a thread can push only its assigned branch, a measured platform exception to claude/issue-<n>-<slug>)
Worktree: objectstack-issue-25907
Domain: domain:server
Seat: domain:server#2 (seat post #20966; 由 os-elon-musk 账户的项目负责)
File surface (read on origin/main f00e8d5c; stop on a breach and explain it in the report): packages/services/service-analytics/src/strategies/native-sql-strategy.ts (the #21042 empty-group fold, the folds loop after executeRawSql, and the statement it folds), its tests (a cross-dialect pin: the same sorted, paged cube query gives the same page on SQLite and PostgreSQL with an all-NULL sum group), one patch changeset for @objectstack/service-analytics. Measures without an identity (avg, min, max) keep NULL, and the PR states their placement. ⛔ No packages/spec/**, ⛔ no new error code: if either is needed, stop and report.
Container & model: M, mode:thread, model: default tier (dispatch-gates --tier on this surface: "no path-derived mandate"), effort: high
Clause-②: no
Metadata-protocol: none
Basis for the Clause-② line: every query that is accepted today is still accepted. The answer becomes the same on every dialect and consistent with the identity emptyGroupValueFor already declares; no accept set changes.
Responsibility: service-analytics (native-sql-strategy.ts) produces the driver-dependent page | no platform path covers it: the fold runs after the database ordered and paged on the NULL | reached by every sorted, paged analytics widget over a sparse column, on both drivers (observed by PR #25903's dev, per the card)
Thread-read: 6407847702
Premise: holds (the card names the fold after executeRawSql; the thread reproduces red on both dialects first)
Serial constraints cleared: area:reports has one card in flight from this seat (this one); #25906 and #25814 stay queued. No open PR names native-sql-strategy.ts other than any PR #25903 may still carry; the thread reads that PR's file list before pr_create.
Writes: PR only through the relay pr_create; comments, labels and bodies only through the relay write tools (post-stamped.mjs, label-write.mjs, fleet-write/dispatch.mjs). ⛔ No GitHub MCP write tool. Report both counts (writes, mcp_calls).
Resume-from: none

Generated by Claude Code

Claim amendment (file surface) · domain:server#2 · seat session session_01TFnDdepdwyDJeM2kJ4GtMy · 2026-10-26T04:25Z · amends claim 6408066587, same session, same branch claude/project-thread-6hvadl.

File surface adds packages/services/service-analytics/src/strategies/objectql-strategy.ts. Its generateSql echo builds ORDER BY through the same orderClauseSql the native strategy now uses (it already imports windowClauseSql from there for the same reason). Its orderAndWindow doc comment is corrected. The pin objectql-echo-offset-only-window.test.ts, which holds the echoed statement to the native one from ORDER BY on, keeps its full comparison. ⛔ It is not narrowed.

Condition: orderClauseSql stays a module export like windowClauseSql, ⛔ not re-exported from packages/services/service-analytics/src/index.ts, so the package's public surface does not grow and Clause-② stays no. MySQL placement is unchanged and NOT MEASURED; the PR says so. Every other line of the claim stands.

Generated by Claude Code

os-dev-report

{
  "issue": 25907,
  "status": "done",
  "branch": "claude/project-thread-6hvadl",
  "pr": "https://github.com/objectstack-ai/objectstack/pull/25953",
  "session": "session_016NB4rZT58RvxS6vBGLcXqz",
  "premise_still_valid": true,
  "summary": "Reproduced red on base f00e8d5c on both dialects (SQLite page c 0, d -3; PostgreSQL page d -3, a 5; ObjectQL face d -3, c 0). The native statement now carries the empty-group identity (COALESCE(SUM(...), 0), plain and conditional; counts unwrapped since SQL COUNT already answers 0) and the JS fold after executeRawSql is removed. Every ORDER BY key states NULLS LAST on sqlite/postgres through a new module export orderClauseSql (not re-exported from src/index.ts), matching applyOrdering; mysql and unknown keep the driver placement, mysql NOT MEASURED. Per amendment 6408215361 the ObjectQL echo builds its ORDER BY through the same orderClauseSql and its orderAndWindow doc comment is corrected; the cross-face echo pin keeps its full comparison.",
  "tests": "head acc8e8b0: pnpm --filter @objectstack/service-analytics exec vitest run --maxWorkers=2 with OS_TEST_POSTGRES_URL (local PostgreSQL 16, zone set off UTC) -> Test Files 186 passed (186), Tests 4806 passed | 2 skipped. New file native-sql-empty-sum-order-window.test.ts 29/29 (sqlite + live pg); red on base 24/29 failed. typecheck exit 0. Ablation 1 (COALESCE return -> bare aggregate) 24 failed / 5 passed; ablation 2 (NULLS LAST -> null for sqlite/postgres) 12 failed / 17 passed, sum pages stay green; both restored to HEAD blob, git diff HEAD empty. With a UTC PostgreSQL the read-scope-temporal-coercion premise cell is red (environment, not this diff).",
  "gates": "dispatch-gates --commands at acc8e8b0: 68 derived; --ran reconciliation 66 run all exit 0, 2 NOT MEASURED (exit 3 PREREQUISITE NOT MET: check-plugin-teardown-shape --self-test needs an unshallow clone; check:dual-build-cjs-loads needs every dist), 0 unrun. pnpm lint exit 0. CI families: PR #25953 check-runs, in_progress at report time.",
  "dogfood_battery": "n/a — no ! in the PR title and Clause-② no",
  "mcp_calls": "6 — issue_read x5 (25907 get/comments, 25906, 25814; claim read via public REST), pull_request_read get_files on #25903; no write tool",
  "api_writes": "2 — relay pr_create (#25953, run 40273043791); relay pr_request_reviewers + comment (this report) in one stroke",
  "open_questions": [],
  "out_of_scope_findings": [
    "carrier: PR #25903 — it edits the fold loop this PR removes and adds resolveDurationMeasureSql without the identity; the PRs conflict textually in execute(); whichever lands second routes duration.aggregate through withEmptyGroupIdentity (noted in PR #25953 body), not filed",
    "carrier: none — '' in an ordered column sorts last in applyOrdering but as text in SQL; already named in the objectql-strategy doc comment; noted, not filed",
    "carrier: none — the ObjectQL echo prints a bare SUM while that face answers 0 for an empty group; noted in Acceptance notes, not filed"
  ]
}

Generated by Claude Code

ACCEPT → PR #25953 comment 6409134943 · domain:server#2 · 2026-10-26T05:44Z

Generated by Claude Code

Navigation Menu

Repository navigation

analytics native SQL: an all-NULL `sum` group is folded to 0 after ORDER BY / LIMIT ran on the NULL, so SQLite and PostgreSQL return different sorted pages #25907

What happens

Why

Expected

Triage: first grade — `bug` · `priority:p2` · `domain:server` · `area:reports` · `pm:queue` · triage seat · `session_01AavokzJ5DndAwitDXvKy4U` · 2026-10-26T03:54Z

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

analytics native SQL: an all-NULL sum group is folded to 0 after ORDER BY / LIMIT ran on the NULL, so SQLite and PostgreSQL return different sorted pages #25907

Description

What happens

Why

Expected

Activity

objectstack-fleet commented on Oct 26, 2026

Triage: first grade — bug · priority:p2 · domain:server · area:reports · pm:queue · triage seat · session_01AavokzJ5DndAwitDXvKy4U · 2026-10-26T03:54Z

objectstack-fleet commented on Oct 26, 2026

objectstack-fleet commented on Oct 26, 2026

objectstack-fleet commented on Oct 26, 2026

objectstack-fleet commented on Oct 26, 2026

Metadata

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

Issue actions

analytics native SQL: an all-NULL `sum` group is folded to 0 after ORDER BY / LIMIT ran on the NULL, so SQLite and PostgreSQL return different sorted pages #25907

Triage: first grade — `bug` · `priority:p2` · `domain:server` · `area:reports` · `pm:queue` · triage seat · `session_01AavokzJ5DndAwitDXvKy4U` · 2026-10-26T03:54Z