Repository navigation
A filtered dataset measure returns ABSENT (not 0) for a group its filter excludes, so every derived ratio over it blanks — on exactly the worst-performing row #4708
Description
Measured on @objectstack/service-analytics@17.0.0-rc.1 while building win-rate widgets in the HotCRM reference app (hotcrm#593 / hotcrm#656, where the full reproduction lives).
Mechanism
A dataset measure carrying its own filter is executed as a separate sub-query and merged back onto the selected dimensions (executeSelection → mergeByDimensions). mergeByDimensions only writes a measure's column onto rows the sub-query returned. A GROUP BY over a filtered row set produces no group at all for a dimension value the filter excludes entirely — so the measure comes back absent, not 0.
computeDerived then propagates that:
const vals = d.of.map((name) => num(row[name]));
if (vals.some((v) => v === null)) return null; // absent input poisons the whole ratioReproduction, through the real executor on shipped metadata
{ name: 'won_count', aggregate: 'count', filter: { stage: 'closed_won' } },
{ name: 'lost_count', aggregate: 'count', filter: { stage: 'closed_lost' } },
{ name: 'decided_count', aggregate: 'count', filter: { stage: { $in: ['closed_won','closed_lost'] } } },
{ name: 'win_rate', derived: { op: 'ratio', of: ['won_count','decided_count'] } },Grouped by lead_source:
| lead_source | won_count | lost_count | decided_count | win_rate |
|---|---|---|---|---|
| content | 2 | 1 | 3 | 66.7% |
| referral | 1 | 1 | 2 | 50.0% |
| partner | 1 | — | 1 | 100% |
| cold_call | — | 1 | 1 | — |
cold_call won nothing and lost one. The correct answer is 0%. What renders is blank — visually identical to "no data for this row", which is the opposite of what the row means.
Note the asymmetry: partner (all wins) renders fine, because there the numerator is the measure that exists. The rows that blank are exactly the worst-performing ones. For a dashboard, that is the least acceptable direction for the bias to run.
Why we did not patch it in the consumer
The obvious fix is ?? 0 in the widget or a coalesce in the measure. We deliberately did not, and think the reasoning generalises: a consumer-side patch has to be repeated by every author of every ratio widget, forever, and forgetting it is silent — the number just reads as missing. It is also not always correct: absent and zero are genuinely different for sum/avg, and only the executor knows which aggregate produced the gap.
Instead the current behaviour is pinned by an app-side assertion written to fail when the platform fixes this, so the fix surfaces as a red test rather than as dashboards silently changing.
Suggested direction
- Fill on merge, by aggregate kind.
mergeByDimensionsknows which measure it is merging. Acountthat produced no group for a dimension value present in another measure's result is unambiguously0— "how many rows matched" has an exact answer when the answer is none.sumover no rows is arguably 0;avg/min/maxare genuinely null. - Or: let
deriveddeclare its null semantics (missingAs: 'zero').
We recommend 1, and the reason is worth stating explicitly: it requires no author to remember anything. Option 2 looks more precise and more "explicit", but it redistributes the decision to every future author, and the failure mode of forgetting is silent. For AI-authored metadata — where the author is a model pattern-matching from neighbouring declarations — a correctness switch that must be remembered is equivalent to a correctness switch that will eventually be missed. Option 1 has no such surface.
Workaround shipped today
decided_count is its own filtered count rather than derived: { op: 'sum', of: ['won_count','lost_count'] } — the derived-sum spelling would blank the denominator for any rep who has never lost a deal (a 100% win rate: the row you least want to hide). Every shipped table also prints the raw counts beside the rate, so a blank reads as "0 of 1" rather than "no data".
Related: hotcrm#656 (full detail), hotcrm#593, #4698.
Activity
os-zhuang commentedon Aug 3, 2026
认领:PM 循环第 4 轮(补批)
会话:
session_015Br2xsJsczFsTR9bvbh2Ny分支:
claude/issue-4708-filtered-measure-zero-fillWorktree:
objectstack-issue-4708分诊:落地 objectstack(
packages/services/service-analytics);取方向 1(在 merge 时按聚合种类填充),不取方向 2。这条不需要额外裁定 —— issue 作者已经给出推荐,且理由正是本仓的第二条轴,我完整同意并再强调一遍:
修法的关键在于区分:
count在某维度值上没有分组,答案无歧义就是 0(「有多少行匹配」在没有匹配时有确切答案);sumover 空集合大体也是 0;而avg/min/max确实是 null。请严格按聚合种类判别,不要一刀切填 0 —— 那会把「没有数据」和「平均值为零」混成一件事,制造一个新的、方向相反的谎。一个值得复核的现场细节:这个 bug 的偏置方向是最坏的一种 —— blank 的恰好是表现最差的行(赢了 0 场的
cold_call显示空白,而全赢的partner显示正常)。对仪表盘来说,把"最差"渲染成"无数据"比随机出错更糟。测试请覆盖这个不对称。并行批隔离:本轮在飞的另有
packages/objectql/src/integrity(#4747)、packages/metadata(#4728)、packages/types+verify+qa+cli/src/utils(#4700)。⛔packages/spec/**零改动。Generated by Claude Code
os-zhuang commentedon Aug 3, 2026
PM 复核:ACCEPT(待 changeset 修正 + CI 全绿) —— PR #4822。
过时前提核查又立了一功
dev 发现 issue 描述的机制一半已经在 main 上修好了(objectui#3136 曾在
executeSelection里加过按聚合种类的填充),用 issue 的复现表逐字跑真实 executor,四行全对。但那段代码一个测试都没有 —— 一个裸奔的修复,回退了也没人知道。所以本 PR 的第一半是把复现表原样钉成回归测试(5 条修复前就绿的,钉的正是这个)。第二半是真修复:同一个缺陷在
compareTo接缝上原封不动活着。对比合并会追加「只在对比窗口出现的 bucket」行,这些行上每一个 base measure 都缺失 —— 包括没有 filter 的。后果的偏置方向与 issue 抓的完全一致:上个周期有业绩、本期归零的渠道,渲染成「无数据」而不是「归零了」 —— 后者才是仪表盘要看的信号。验收逐条核过
count/count_distinct/sum→ 0,avg/min/max保持 null,映射复用 spec 的emptyGroupValueFor(只读,spec 零改动)——「一刀切填 0 会制造方向相反的新谎」这条守住了;__compare列同样填充)比 issue 字面范围宽三行,dev 主动点名请 reviewer 裁决 —— 我接受:同一个 merge 调用、同一套推理,拆开只会让下一个 issue 立刻开在隔壁那一行。超范围产出(已核实、已入队、已归域)
<measure>__comparesilently reports a different measure than the column beside it #4820(domain:services)——compareTo完全丢弃 measure 级 filter:won_count__compare数的是上个周期的全部行,不是赢单行。这比本 issue 更严重一档:本 issue 是留白,那条是数错 —— 并排两列是两个不同的度量,而没有任何信号。mergeByDimensionsjoins its dimension key with no delimiter, so two distinct groups can merge into one row #4821(domain:services)——mergeByDimensions维度键用空串拼接,多维度下'ab'|'c'与'a'|'bc'撞键合并成一行;隔壁cross-object-rebucket.ts已用 JSON 编码解决过同一个问题。一处要改
changeset 应标
minor:fillEmptyGroups从包根导出,是新增公共 API,按今晚 #4815 / #4791 的先例执行同一个 bar。已通知 dev,一行改动。修正推上、CI 全绿后送合并队列。按维护者指示,#4820/#4821 本会话不派发,留给暂停后的下一个 services 域会话。
Generated by Claude Code
os-zhuang commentedon Aug 3, 2026
状态更新:PR #4822 的 CI 已全绿(20/20),仅等 changeset 从
patch改为minor——fillEmptyGroups从包根index.ts导出,是新增公共 API。已通知 dev,改完即送合并队列。顺带记一句复核时读代码看到的:
fillEmptyGroups的调用点注释把「为什么放在所有 merge 之后、derived 求值之前」的三种缺列场景逐条列了出来(supplementary 查询漏掉被 filter 排除的组 / 后一个 supplementary 查询追加了前面没见过的维度键 / compareTo 追加只有上期存在的 bucket)。第三种正是本 PR 新修的那一半,而前两种在原实现里是靠「放在循环之后」偶然覆盖到的 —— 现在三种都由同一个位置显式覆盖,注释也说明了为什么不能再往前挪。这种把「位置为什么是这个位置」写清楚的注释,比代码本身更能防止下一个人把它挪回去。Generated by Claude Code
chore(changeset): 标 minor —— fillEmptyGroups 是新增公共导出 (#4708)
fix(service-analytics): 按聚合种类填充「查询从未报告过的分组」,并补上 compareTo 这一道接缝 (obje…
fix(service-analytics): key the dimension merge unambiguously, and st…