Measure definitions are not fully compiled: `aggregate: 'count'` ignores `field`, and `/api/v1/analytics/query` drops every per-measure `filter` — the SQL is `COUNT(*)` for all of them

[os-zhuang]

Measure definitions are not fully compiled: aggregate: 'count' ignores field, and /api/v1/analytics/query drops every per-measure filter — the generated SQL is COUNT(*) for all of them

Measured on 17.1.0 (open edition, objectstack dev), HotCRM's case_metrics / opportunity_metrics cubes. The response includes the compiled SQL, so both defects are visible without guessing.

1. aggregate: 'count' with a field counts rows, not non-null values — a rate reads 100% when it is 12.5%

// src/datasets/case.dataset.ts
{ name: 'closed_count',      label: 'Closed Cases',   aggregate: 'count', filter: { is_closed: true } },
{ name: 'kb_resolved_count', label: 'Resolved by KB', aggregate: 'count',
  field: 'resolved_by_article', filter: { is_closed: true } },
{ name: 'kb_deflection_rate', derived: { op: 'ratio', of: ['kb_resolved_count','closed_count'] }, format: '0%' },

Ground truth in that org: 8 closed cases, of which 1 carries a resolved_by_article. So the deflection rate is 12.5%.

The service dashboard renders:

知识库转移率   100%          ← kb_deflection_rate
知识库解决数   8             ← kb_resolved_count   (should be 1)
已关闭工单     8             ← closed_count        (correct)

解决工单最多的文章
  Resolving Article    Resolved by KB
  —                    7        ← 7 of the 8 have no article at all
  API Rate Limits      1

The pivot immediately below the tile contradicts it: seven of the eight "KB-resolved" cases group under a null article. field: 'resolved_by_article' is dropped, so the measure is COUNT(*) over the filtered rows.

The dataset author anticipated exactly this metric being fragile — there is a hook (case_resolution_article_normalize) whose only job is to normalise '' to NULL so the count is honest, and a comment explaining that every widget shows the numerator and denominator beside the rate "so a reader can check the arithmetic". A reader who checks it finds 8 = 8 and a pivot saying otherwise.

Expected: COUNT("resolved_by_article"), or an author-time rejection saying count does not take a field (and pointing at count_distinct).

2. /api/v1/analytics/query drops per-measure filter and field

POST /api/v1/analytics/query
{"cube":"opportunity_metrics","measures":["opp_count","won_count","lost_count","won_amount"]}

→ 200
{"rows":[{"opp_count":24,"won_count":24,"lost_count":24,"won_amount":5632500}],
 "sql":"SELECT COUNT(*) AS \"opp_count\", COUNT(*) AS \"won_count\", COUNT(*) AS \"lost_count\",
         SUM(\"crm_opportunity\".\"amount\") AS \"won_amount\" FROM \"crm_opportunity\""}

The measures declare filters:

{ name: 'won_count',  label: 'Won Deals',   aggregate: 'count', filter: { stage: 'closed_won' } },
{ name: 'lost_count', label: 'Lost Deals',  aggregate: 'count', filter: { stage: 'closed_lost' } },
{ name: 'won_amount', label: 'Won Revenue', aggregate: 'sum', field: 'amount',
  filter: { stage: 'closed_won' } },

Ground truth: 24 opportunities, 8 won, 5 lost, won revenue 1,290,000. The endpoint answers 24 / 24 / 24 / 5,632,500 — every filter silently gone, no FILTER (WHERE …) and no CASE WHEN in the SQL. case_metrics behaves the same way (closed_count → 39, i.e. all cases).

The dashboard path does apply these filters — the sales dashboard shows 赢单数 8, 丢单数 5, 已成交收入 1,290,000 correctly. So the same cube and the same measure names answer two different numbers depending on which door you come in, and the API door is the one that silently answers wrong. Anything reading metrics over the API — an agent tool, an export, a downstream report — gets unfiltered aggregates while the UI shows filtered ones.

Why this pair is worth one card

Both are the same gap: a measure carries aggregate + field + filter, and the compiler that produced the SQL above used only aggregate. Fixing the compiler in one place fixes the dashboard's count(field) and the API's dropped filters together, and the returned sql field makes both testable — a regression test can assert the compiled SQL contains COUNT("resolved_by_article") and a FILTER/CASE WHEN per filtered measure.

If per-measure filter is deliberately unsupported on the strict /analytics/query wrapper, it should be a 400 naming the measure, not a 200 with different arithmetic.

[qq9340100]

Triage: pm:queue · domain:services · type Bug · priority stays p1 (triage seat, session session_01C5fGRTUHXFevsEdMEFDadM). Rationale: declared≠enforced on the measure compiler — aggregate+field+filter are authored, the compiled SQL uses aggregate only, and the two doors (dashboard vs /api/v1/analytics/query) answer different numbers for the same cube. One compiler fix covers both measured defects, and the response's sql field makes the regression pinnable (COUNT("col") + FILTER/CASE WHEN per filtered measure). Falls in the analytics/dataset service surface (domain:services). If the implementing seat finds per-measure filter genuinely unsupported on the strict wrapper, the card's own fallback holds: a 400 naming the measure, never a 200 with different arithmetic — that variant would be a contract change and forks to a decision at that point.

---

Generated by Claude Code

[os-warren]

Claim: PM seat #6021, session session_01PnJHU45vPJj5UQrxe946Bx.

• Branch: claude/issue-10298-measure-compiler-field-and-filter
• Worktree: /home/user/objectstack-issue-10298（⛔ 禁 git stash）
• File surface: 度量编译器（analytics/dataset 面，以实测落点为准）+ 其测试 + changeset
• Container & model: claude-opus-5
• Clause-②: no（主路径）—— 让编译出的 SQL 与已声明的 aggregate+field+filter 一致，是修正错误的算术，不改端点的接受/拒绝集
• Serial constraints: 与在飞的 finding: the direct /sso/register endpoint's ADR-0024 before-hook admits org owners/admins — wider than the platform-admin posture #9653 landed on the /admin/sso/* bridges #10009（plugin-auth）、同批的 app-showcase's email template declares locale: 'en', which no en-US send can resolve — and it bypasses defineEmailTemplateDefinition #10267（examples/app-showcase）、[finding] A clean first install shows the newcomer two red ✕ unmet-peer warnings (better-sqlite3, better-call) #10326（依赖清单）面不相交

⚠️ 分诊已命名的分叉，我把它写成硬约束：若实测发现 per-measure filter 在严格包装器上确实不受支持，卡面自己的回退是 400 点名该 measure，绝不 200 配不同的算术——而那个变体是契约变更 ⇒ ⛔ 停下回报，不要实现，由 PM 重新定级。⛔ 不得先实现再声明。

⚠️ 本卡是 p1，且两扇门（dashboard 与 /api/v1/analytics/query）对同一个 cube 给出不同数字。响应里的 sql 字段让回归可钉——要求把 COUNT("col") 与每个带过滤的 measure 的 FILTER/CASE WHEN 钉成断言，而不是只断言结果数值。

---

Generated by Claude Code