analytics: cube query with a time-dimension granularity (month) on a date dimension crashes 500 DATABASE_ERROR

[baozhoutao]

A cube query that adds a timeDimensions granularity on a date dimension returns 500 DATABASE_ERROR, while the same cube's plain measure/dimension queries succeed. Found while browser-testing the dashboards/analytics area against the showcase dev app (console pin 190fbd01, framework 502ff8b5); reproduced ×2.

Reproduction

POST /api/v1/analytics/query
{
 "cube": "showcase_delivery",
 "measures": ["showcase_delivery.count"],
 "timeDimensions": [{ "dimension": "showcase_delivery.due_date", "granularity": "month" }]
}
→ 500 DATABASE_ERROR

Controls that succeed (same cube, same session):

• measures:["showcase_delivery.count"] + dimensions:["showcase_delivery.status"] → 200, buckets reconcile with /data.
• measures:["showcase_delivery.total_estimate_hours"] by status → 200.
• Malformed bodies are correctly rejected at entry with a 400 + migration hint (so the entry validator is fine; the failure is downstream, at SQL generation/execution for the month bucket).

Impact

Any dashboard widget or report that buckets a measure by month/week/day over a date dimension (a common chart shape — "count by month") fails at runtime with a 500. On the checklist this surfaced as dashboards.drill-through-range and dashboards.empty-null-bucket-boundaries not being able to isolate the month-bucket path in the UI (the underlying query 500s), even though status/priority bucketing renders correctly.

Notes

• The date-bucket parity unit pins (SQLite 上 Field.datetime 的 dateGranularity 分桶恒为 NULL —— 趋势图塌成一根柱子 #3773 date-bucket-parity, 空分组桶的键两条路不一致:下推 SQL 给 null,内存兜底给 "(null)"(不限日期分桶) #3839 empty-group-parity) are green — they exercise the bucketing logic at a layer that does not hit the real SQL driver the way the live /analytics/query path does, which is why this only shows against a running deployment.
• Likely a date-truncation SQL emission issue for the granularity clause on the better-sqlite3 driver (showcase dev app); worth checking whether the Postgres driver path differs.

(Dedup note: the GitHub search API was rate-limited at filing time, so a pre-file duplicate scan could not complete — please close as duplicate if an existing card already covers analytics time-dimension bucketing.)

QA-source: #13404 · dashboards.drill-through-range, dashboards.empty-null-bucket-boundaries (UI supplement) · analytics timeDimension

[os-warren]

Triage → domain:services · p1 · bug. Cross-domain possible; owning lane is domain:services.

Anchoring, with the precedent that settles it. The granularity path is owned end to end by packages/services/service-analytics — it resolves the granularity (analytics-service.ts:1262, dataset-executor.ts:282-324), picks native-SQL vs ObjectQL strategy, and reads the driver's supports.queryDateGranularity record. The actual date-bucket SQL is emitted in packages/drivers/driver-sql/src/sql-driver.ts (the strftime forms at :4632, quarter at :4637) ⇒ domain:engine if the fault turns out to be there. Two prior analytics cards of this exact shape — #5688 (native dateGranularity lowering) and #5520 (analytics 500 instead of 400 on a bad dimension) — are both labelled domain:services, so the lane follows precedent as well as the entry point.

Dedup discharged. The card notes the GitHub search API was rate-limited at filing time so its own scan could not complete. I ran it: no open duplicate. Nearest neighbours are all closed — #4033 (bucket label dropped from rows), #3650 (ObjectQLStrategy silently ignoring timeDimensions[].dateRange), #5688, #5520, #3588. The last two are the same family (analytics + granularity + a 500) and are worth reading before starting, but neither covers this.

⛔ Mandatory first step — it decides both the grade and the fix site

Reproduce on Postgres before choosing where to fix. The card measured this against the showcase dev app on better-sqlite3 and says so honestly ("worth checking whether the Postgres driver path differs"). That one reading forks the card:

• Fails on both ⇒ the fault is in the strategy/negotiation layer (service-analytics), the grade stands at p1, and the fix is single-lane.
• SQLite only ⇒ the fault is in driver-sql's SQLite date-bucket emission, the card re-anchors to domain:engine as a cross-domain half, and the production grade drops to p2 (dev-app driver only).

⚠️ There is a live suspect for the SQLite-only branch already in the source: sql-driver.ts:4580 — "SQLite's strftime gained ISO week (%V) in 3.46 (2024-05-23); play it safe" — i.e. this emitter already has known version-dependent behaviour. Read that block before reaching for the strategy layer.

A second thing the reproduction must answer. sql-driver.ts:1448-1477 documents a real negotiation: a driver publishes supports.queryDateGranularity, and the engine reads that record and falls back to in-engine bucketing for a granularity the driver declines. A 500 DATABASE_ERROR means SQL was emitted and the database rejected it — so either the driver claimed month and emitted SQL the DB won't take, or something bypassed the capability check entirely. Which of those it is, is the root cause; ⛔ do not patch the SQL string without answering it, or the same class returns at week and quarter.

Grade p1. "Count by month" is not an exotic query — it is the default shape of most dashboard time charts, and it fails with a hard 500, not a degraded result. The card also records the downstream cost: two checklist items (dashboards.drill-through-range, dashboards.empty-null-bucket-boundaries) cannot be exercised at all because the underlying query 500s, so this one defect is blinding QA on a whole area. Held at p1 rather than p0 because a shipped Postgres deployment may be unaffected — which is exactly what the first step above settles.

Note on why the unit pins did not catch it — the card is right and this belongs in the fix's test plan: #3773 (date-bucket-parity) and #3839 (empty-group-parity) are green because they exercise bucketing at a layer that never reaches the real SQL driver. Whatever fix lands needs a test that goes through the actual driver, or the next regression is invisible the same way.

QA-source: #13404.

---

Generated by Claude Code

[os-steve]

认领 — domain:services 执行座位 #6021,R11

• session: session_016ZC5rNQj3WEet5HAmmAkMs
• branch: claude/issue-13714-timedimension-granularity-sql
• 热文件互斥:packages/services/service-analytics/** 与 packages/drivers/** 对当前全部开着的 PR 分支逐一核过 merge-base 差集,零命中(⚠️ 按文件核,不按包 —— 按包会把 changeset-release/main 碰的 CHANGELOG.md/package.json 误读成占用)。
• 档位:默认档。修一条 SQL 发射路径的崩溃,不改任何接受/拒绝语义、不扩公开面。⛔ 路径肢按实际 diff 现判。

报告质量说明(给 dev)

⭐ 这张卡是带对照的现场报告,不是猜测:同一 cube、同一会话下 dimensions:[status] 的分桶 200 且与 /data 对得上,畸形请求在入口被 400 正确拒绝 ⇒ 入口校验器没问题,故障在下游的 SQL 生成/执行。⛔ 不要重新论证这一层。

⚠️ 而立卡人自己点出的那句是本卡最重要的输入:

日期分桶的 parity 单测(#3773、#3839)是绿的 —— 它们在一个不像 live /analytics/query 那样打到真 SQL 驱动的层面上验证分桶逻辑,这就是为什么这个缺陷只在跑起来的部署上显形。

⇒ 一个只加同层单测的修复,会连这个缺陷都复现不出来。 本仓有 live-dialect 测试装置(driver-sql 的 declareDialectCell,跑真 PG / MySQL),这是本卡该用的形状。

立卡人自陈的一处未完成

Dedup note: the GitHub search API was rate-limited at filing time, so a pre-file duplicate scan could not complete。

⇒ 查重是本刀的第一步,⛔ 不要跳过。若已有卡覆盖 analytics 时间维度分桶,按重复关闭并回报。

---

Generated by Claude Code

[os-steve]

⚠️ 派发的 dev 被会话额度中断 —— 在制品已保全并推送

domain:services PM 席(session session_016ZC5rNQj3WEet5HAmmAkMs)。本卡的 dev agent 于 2026-09-01 01:2x UTC 被 HTTP 429「session limit」 终止(额度 04:30 UTC 重置),⛔ 不是实现失败。

在制品已由 PM 保全(worktree 里的未推提交,容器临时、不推即失):

branch: claude/issue-13714-timedimension-granularity-sql
commit: eea34c7d2 fix(driver-sql): emit an aggregate alias as one identifier,
 not a qualified reference
files : 5

⭐ 它停在哪一步,是本条最有用的信息

该 agent 的最后一句自述是:

Now the ablation. Predicting first: reverting the emission fix should redden every non-bare-alias case; week on SQLite must stay green (it is refused at the capability check before any statement is built) — a declared control, not evidence.

⇒ 修复已落盘,ablation 一步都没跑。 接手的人:

1. ⛔ 不要把分支当成已验证 —— 零 测试读数、零 ablation、零门族读数在案。
2. ⭐ 但方向已经先声明了,而且声明得对(先预测再跑,且已把 SQLite 的 week 正确标为声明控制项而非证据 —— 它在能力检查处就被拒,根本走不到语句构造)。⇒ 接着按这个预测跑,⛔ 不要事后改预测去凑结果。

认领仍然有效,同一 session 持有,额度重置后可续做。⛔ 在此之前请勿另起认领。

---

Generated by Claude Code

[claude]

os-dev-report

#13714 — implemented. Draft PR: #14112

@baozhoutao — you run the deployment this was measured on, so the short version first: your report is correct, your controls were the decisive evidence, and your hypothesis was wrong in a useful way.

It is not a date-truncation bug. The strftime / date_trunc bucket expression is correct on every dialect. What the database refuses is the alias. Every analytics measure is addressed as CUBE.MEASURE, and that dotted name is handed to the SQL driver as the aggregation alias — the key you read the number back under. The driver bound it through a knex placeholder that parses an identifier rather than quoting one: it splits on the dot into table.column. So your query compiled to count(*) as \showcase_delivery`.`count`` and was rejected before it ran.

Your controls are what proved it. The granularity is only the ROUTER: NativeSQLStrategy declines exactly when a granularity is present, and that face already wrote the alias correctly. So an un-bucketed query never reached the broken door and a bucketed one always did — which is precisely why dimensions:[status] was 200 and granularity:'month' was 500, on the same cube in the same session. Crossing the two axes on the pre-fix tree:

alias	granularity	result
n	day / month / quarter / year	200
showcase_delivery.count	none at all	500
showcase_delivery.count	month	500

The middle row is the one that settles it — no bucket anywhere, same 500.

What to expect in your deployment. All of day/month/quarter/year are fixed together, not just month. week on SQLite was never broken this way: the driver declines it up front and the engine buckets it in memory, which is why it kept working. The two blocked checklist items (dashboards.drill-through-range, dashboards.empty-null-bucket-boundaries) should be exercisable again once this lands.

What I could not verify, and who can. Everything below was measured on better-sqlite3 — your driver. Postgres and MySQL are pinned by the same suite but were NOT executed here (no live servers in this container); CI's live-dialect matrix job runs them. The defect lives in knex's shared formatter rather than in any dialect, so both are broken by construction and repaired by construction — but that is an argument, not a reading, and I am not presenting the SQLite green as a measurement for them.

If you can re-run the original request against your deployment once this lands, that is the one confirmation nobody else can give.

---

For the PM

Dedup (step 0, the filer could not finish it). Repo-scoped REST list endpoint, 436 open issues scanned (PRs excluded), grepped locally for timeDimension / granularity / date bucket / date_trunc / strftime / analytics-month-week. Zero duplicates — the only hit was #13714 itself. /search/issues was never used.

Root cause. Not the granularity clause. SqlDriver.aggregate bound the caller's aggregation alias through knex's ??, which parses an identifier rather than quoting one and splits on .. Every analytics measure is CUBE.MEASURE, so every cube query reaching that door carried a dotted alias. The granularity only ROUTES the query there — NativeSQLStrategy.canHandle declines exactly on a granularity, and that face already emitted the alias correctly.

Answering the anchoring comment's two questions directly. The lane is domain:engine, not domain:services: the fault is entirely in packages/drivers/driver-sql. And it is neither of the two branches that comment forecast — not "fails on both" (a strategy/negotiation fault) and not "SQLite only" (a SQLite date-bucket emission fault). It is dialect-independent and lives in the alias position, not the bucket. The capability negotiation is working correctly throughout: the driver claimed month, emitted a correct strftime, and the statement died on the alias.

Ablation, direction predicted before running. Mutation = the file restored to its 62a137bae blob; confirmed on disk by blob hash and by both marker counts moving, and in dist/ by ablation-dist-preflight.mjs --absent for the cross-package leg. Restore proven by state (whole-tree git status --porcelain clean, blob back to HEAD, markers back, preflight positive).

suite	mutated	restored
driver-sql alias identity	10 red, 3 green	13 green, 2 unprovisioned
service-analytics granularity route	12 red, 1 green	13 green

Green in both directions, so ⛔ declared controls and not ablation evidence: week on SQLite (both suites), and driver-sql's two bare-alias controls. The prediction held unrevised.

The ablation also caught an overclaim of mine: the second suite's un-bucketed cases reddened too, because that harness sets nativeSql: false and forces them down the same door. I renamed them for what they measure rather than leaving them labelled as a reproduction of the reporter's controls.

A pin swap worth flagging. I first wrote the end-to-end pin as a dogfood bootStack HTTP suite. Its build closure could not be brought up in this container — parallel agents' full-workspace builds repeatedly cleaned the dist/ directories it needs, across four attempts. Rather than ship a test I had never executed, I replaced it with a service-analytics pin that runs here against better-sqlite3, the reported driver, and cites the already-green native-sql-granularity-decline.test.ts for the routing half instead of duplicating it.

Gates. Re-derived from the actual diff (both sections read whole; re-derived after the diff changed and again after git fetch origin main — stable at 36). 35 green / 36, on 7d20ebda1. check:dual-build-cjs-loads and check:type-check-debt first exited 3 naming a missing full-workspace dist/; I built that closure (70/70) and both re-ran green. check-test-completeness stays NOT MEASURED — it grades a saved turbo run test log, a CI artifact.

{
 "issue": 13714,
 "status": "done",
 "branch": "claude/issue-13714-timedimension-granularity-sql",
 "pr": "https://github.com/objectstack-ai/objectstack/pull/14112",
 "premise_still_valid": true,
 "summary": "The reported 500 reproduces and is fixed, but the filer's hypothesis is falsified: it is not a date-truncation problem. SqlDriver.aggregate bound the caller's aggregation alias through knex's ?? placeholder, which parses an identifier rather than quoting it and splits on '.'. Every analytics measure is named CUBE.MEASURE and ObjectQLStrategy uses that dotted name verbatim as the driver-level alias, so the statement compiled to `count(*) as \"showcase_delivery\".\"count\"` and was refused before running. The granularity is only the ROUTER: NativeSQLStrategy.canHandle declines exactly on a granularity and that face already emitted the alias correctly, which is exactly why the reporter's controls were 200 and the bucketed query was 500. Fixed by aliasIdentifierSql (client.wrapIdentifier, the same function knex calls per segment) at all five alias positions on the aggregate/window builders; column references still bind through ?? and may still be qualified. Lane is domain:engine, not domain:services.",
 "tests": "Ablation, direction predicted BEFORE running ('revert should redden every non-bare-alias case; week on SQLite stays green'). Mutation = file restored to its 62a137bae blob, confirmed ON DISK by blob hash 32687ef2->9e8a58e6 and by marker counts (aliasIdentifierSql 7->0, loose 'as ??' 4->10), never by an editor exit code; for the cross-package leg driver-sql was REBUILT each leg and the mutation confirmed in dist/ via scripts/ablation-dist-preflight.mjs --absent. Restore proven BY STATE: whole-tree `git status --porcelain` clean, blob back to HEAD blob, markers back, preflight positive. Results: driver-sql alias-identity suite 10 red / 3 green mutated, 13 green + 2 unprovisioned restored; service-analytics route suite 12 red / 1 green mutated, 13 green restored. DECLARED CONTROLS (green in BOTH directions, therefore NOT ablation evidence): week on SQLite in both suites (refused at the capability check before any statement is built), and driver-sql's two bare-alias controls. Regression: driver-sql full suite 149 files / 2273 tests pass (9 files + 136 tests skipped), service-analytics full suite 86 files / 1850 tests pass, spec api/analytics.test.ts 25 pass as the entry-validator control. Typecheck: driver-sql tsc --noEmit exit 0 over a 569-file --listFiles program containing both changed files; service-analytics exit 0 over 537 files containing the new pin. Gates re-derived from the actual diff (both sections whole; re-derived twice, stable at 36): 35 green on 7d20ebda1. check:dual-build-cjs-loads and check:type-check-debt first exited 3 = PREREQUISITE NOT MET naming a missing full-workspace dist/; that closure was built (turbo 70/70) and both re-ran green. check-test-completeness remains NOT MEASURED (grades a saved turbo run test log, a CI artifact). DIALECT BOUNDARY: SQLite/better-sqlite3 MEASURED red-before/green-after; Postgres and MySQL are PINNED via declareDialectCell but NOT EXECUTED here (no OS_TEST_POSTGRES_URL / OS_TEST_MYSQL_URL) and report as named unprovisioned cells - CI's live-dialect matrix job must confirm them, and the SQLite green is NOT offered as a reading for either.",
 "mcp_calls": "3 — issue_read get, issue_read get_comments, issue_write create (#14113). Everything else went over repo-scoped REST or git.",
 "open_questions": [],
 "out_of_scope_findings": [
 "filed as #14113: drivers(turso) RemoteTransport holds the aggregation alias to SAFE_IDENTIFIER (no dots), so every analytics cube query on a remote Turso datasource throws a bare Error with no code and no status - same class one face over, but it needs its own call on the injection guard (escape the quote character rather than reject the dot), so it was filed rather than patched inline. Duplicate-scanned first: 436 open issues, zero hits.",
 "not filed, recorded as a boundary observation: knex's Postgres wrapIdentifierImpl mangles an identifier containing a bracketed index (an alias 'a[0]b' emits \"a\"[0]). Behaviour is IDENTICAL before and after this fix and has no measured writer, so filing it would be speculative rather than a defect report."
 ]
}

---

Generated by Claude Code