`avg` over a boolean field returns `null` on driver-memory and a number on sqlite — same rows, same dataset, same executor

[os-sam]

Measured while working objectstack-ai/hotcrm#1213. Filed as a finding, unassigned — I am not proposing a fix, and the severity call belongs to triage.

What was measured

One AnalyticsService.queryDataset call, one dataset, one set of rows, run twice — once on InMemoryDriver, once on SqliteWasmDriver — against @objectstack/* 17.1.0 as pinned by hotcrm.

The measure under test is an average over a boolean column:

{ name: 'avg_sla_violated', label: 'SLA Violation Rate',
 aggregate: 'avg', field: 'is_sla_violated', format: '0.0%' }

Rows: 5 crm_case records — 4 closed (3 with is_sla_violated: false, 1 true), 1 open with true.

[memory] unfiltered {"avg_sla_violated":null, "closed_count":4,"sla_met_count":3}
[memory] closed-filter {"avg_sla_violated":null, "closed_count":4,"sla_met_count":3}
[sqlite] unfiltered {"avg_sla_violated":0.4, "closed_count":4,"sla_met_count":3}
[sqlite] closed-filter {"avg_sla_violated":0.25, "closed_count":4,"sla_met_count":3}

SQLite's numbers are the arithmetically correct ones (2/5 unfiltered, 1/4 over closed cases). driver-memory returns null for the same question.

Two controls in the same rows show the divergence is specific to averaging a boolean, not to booleans generally and not to the rows:

• count measures carrying boolean predicates (filter: { is_closed: true, is_sla_violated: false }) agree exactly across both drivers (closed_count 4, sla_met_count 3) — so boolean filtering is fine on both.
• A derived ratio built on those counts computes identically on both (0.75).

Why it is worth recording

null and a number are not two spellings of one answer. A dashboard tile bound to such a measure renders a value on one driver and a blank on the other, with no error on either path — and the blank is indistinguishable from "no matching rows". hotcrm ships exactly this shape: its Service dashboard's Open Cases by Priority table has an SLA Violation Rate column bound to this measure, which renders in the app (SQLite) and would be blank against driver-memory.

It also silently weakens tests: a suite that pins a rate measure on the in-memory driver asserts against null and cannot fail in the direction that matters. That is how I hit it — an assertion written against the violation rate passed on SQLite and read null on memory.

What I did not determine

I did not read the driver or objectql aggregate source, so I am not claiming where the divergence lives (driver-memory's aggregate implementation, the ObjectQL aggregate bridge, or a deliberate refusal to average a non-numeric column). If it is deliberate, the two faces still disagree, and a refusal would be more useful than null.

Related but distinct cells of the same "drivers disagree about an aggregate" family, both closed: #6814 (count_distinct), #6815 (AggregationNode.distinct).

Reproduction

test/knowledge-deflection.test.ts and test/sla-compliance-gauge.test.ts in objectstack-ai/hotcrm both build the two-driver harness this used (ObjectQL.create + AnalyticsService with the executeAggregate bridge the plugin wires at boot); adding an avg measure over is_sla_violated to either reproduces it.

---

Generated by Claude Code

[os-zhuang]

Triage: promoted to Bug, pm:queue, routed domain:engine. Rationale: two executors answer the same aggregate over the same rows differently, and the divergence is user-visible (a dashboard tile renders a rate on SQLite and a blank on driver-memory, indistinguishable from "no rows") and test-corrupting (in-memory suites assert against null and cannot fail in the direction that matters). The measurement isolates the cell precisely: boolean filtering agrees, derived ratios agree, only avg over a boolean diverges — and SQLite's numbers are the arithmetically correct ones. Dispatch is diagnose-first: locate where the divergence lives (driver-memory's aggregate, the ObjectQL aggregate bridge, or a deliberate non-numeric refusal). Mechanism assumption to verify: align driver-memory to numeric coercion of booleans (0/1) matching the SQL arm; if the divergence turns out to be a deliberate refusal to average non-numerics, the two faces must still agree — and a loud refusal on both beats null on one (the card says the same). Reproduction is already packaged in the hotcrm two-driver harness the card names. Size S. Related closed cells #6814/#6815 are precedent that these get fixed as consistency bugs.

---

Generated by Claude Code

[os-zhuang]

Claim: PM loop round 4
Session: session_01RfyXxZ2WPjcjhuXpiQQc3y
Branch: claude/issue-11065-avg-boolean-memory-driver
Worktree: objectstack-issue-11065
Domain: domain:engine
File surface: packages/drivers/driver-memory/src/ only — memory-driver.ts (computeAggregate :1240) and, if the repro reaches it, memory-analytics.ts (buildAggregator :1196, mingo $avg) + tests + changeset (stop on breach; explain in the report)
Container & model: S, mode:subagent
Clause-②: no — see "the refusal branch is dead" below. This card returns a value where one was wrongly null; it changes no accept/reject behaviour and widens no public surface.
Serial constraints cleared: in-flight sibling #11009 holds packages/metadata-core + packages/objectql + packages/drivers/driver-sql + packages/services/service-messaging. This card's surface is packages/drivers/driver-memory — disjoint from all four. packages/objectql is the near miss and the reason for the ⛔ boundary below. PR #11139 holds packages/metadata-protocol. Lane in-flight at claim: 1.

Premise verified on origin/main @ acb4dbc09 — and it collapses triage's three-way diagnosis to one

Triage dispatched this diagnose-first: "locate where the divergence lives (driver-memory's aggregate, the ObjectQL aggregate bridge, or a deliberate non-numeric refusal)". That read is answered before dispatch, because the answer changes both the file surface and the tier. There are three in-memory-or-SQL implementations of avg in this repo, and they do not two-way disagree — driver-memory is the lone outlier:

implementation	expression	avg over a boolean column
SQLite (SqliteWasmDriver)	AVG(col)	0.4 / 0.25 — the card's measured, arithmetically correct numbers
packages/objectql/src/in-memory-aggregation.ts:219	values.filter(v => v != null).map(toNumber)	already correct — toNumber (:255) is Number(v) gated on Number.isFinite, so Number(true) === 1
packages/drivers/driver-memory/src/memory-driver.ts:1246	values.filter(v => typeof v === 'number')	null — every boolean is filtered out, nums.length === 0, and the arm returns null

// memory-driver.ts:1245 — the defect, verbatim
case 'sum':
case 'avg': {
 const nums = values.filter(v => typeof v === 'number');
 const sum = nums.reduce((a, b) => a + b, 0);
 if (func === 'sum') return sum;
 return nums.length > 0 ? sum / nums.length : null;
}

That is the card's symptom exactly, including why the two controls agreed: boolean filtering never passes through this arm, and the derived ratio is computed from count measures, whose arm counts != null rather than typeof === 'number'.

The refusal branch is dead — do not spend time on it

Triage's third possibility ("a deliberate refusal to average a non-numeric column") and the card's own fallback position ("a loud refusal would be more useful than null") are both foreclosed by the table above: this repo's other in-memory averaging path already coerces booleans and answers the SQL number. A refusal would have to break in-memory-aggregation.ts too, and that would be a contract change needing its own ruling. So the fix direction is settled and narrow: align driver-memory to the two implementations that already agree.

⚠️ sum shares the same values.filter(...) line. Aligning avg without deciding sum leaves the same defect one arm over (sum over a boolean = "how many true", 0 today). Decide it deliberately and say which you chose and why — do not leave it unexamined.

⛔ Boundaries

1. ⛔ Do not edit packages/objectql. It is held by in-flight A compare-and-set where on a by-id update is silently inert — the extra predicate keys never reach the driver, and SqlHttpOutbox.redeliver's status guard is one of them #11009 (same package, engine.ts). in-memory-aggregation.ts is the reference implementation here, not a work item — read it, match its answer, do not import from it (that would add a cross-package dependency this card has no mandate for) and do not refactor the two into one.
2. ⛔ Do not adopt toNumber's wider behaviour. It maps a non-numeric string to 0, silently averaging garbage as zero. Whether that is right is a separate question this card does not open. Fix the boolean case.
3. If memory-analytics.ts's mingo $avg path proves to carry the same divergence, it is in this package and in scope — fix both faces or say why only one.
4. Any other driver found sharing the defect ⇒ report it, ⛔ do not fix it here.

Acceptance

A regression pin that fails in the direction that matters — the card's own reason for existing is that an in-memory suite asserting null cannot fail correctly. Pin the card's measured shape: 5 rows, 4 closed, avg over is_sla_violated ⇒ 0.4 unfiltered and 0.25 under the closed filter, on driver-memory. Include the reversing/positive control the card's own method used (a count measure over the same rows) so a driver that stopped aggregating entirely does not keep it green.

⚠️ Freeze interaction — recorded, not waved

This card's fix lands in driver-memory, which is under the maintainer's 2026-08-05 investment freeze (cross-issue anchor #5499). That anchor's standing triage rule would normally send this card straight to pm:on-hold:

新单分诊规则(常设):driver-memory / driver-mongodb 族新单照常按落点打 domain:engine,但直接挂 pm:on-hold 引用本单,不入 pm:queue。

It is dispatched anyway because the same ruling carries an explicit exception whose premise this card states in its own body:

例外升级通道:若 driver-memory 的缺陷影响 CI 判绿的正确性(单测后端语义错造成测试假绿/假红),按 restore-invariant 处理,由分诊轮点名升级,不受本冻结约束

The card's third section is that exception, in the reporter's words: "a suite that pins a rate measure on the in-memory driver asserts against null and cannot fail in the direction that matters. That is how I hit it — an assertion written against the violation rate passed on SQLite and read null on memory." Triage promoted it to Bug + pm:queue in a triage round on exactly that rationale ("test-corrupting"), which is the 「由分诊轮点名升级」 the exception requires — though it did not cite #5499, which is why this seat is writing the interaction down rather than letting a merge into a frozen package read later as freeze erosion. Scope stays restore-invariant: align one arm to the two faces that already agree. ⛔ No other driver-memory investment rides along.

---

Generated by Claude Code