analytics: on the ObjectQL strategy a `$not` over a multi-valued lookup (`$contains`) is refused 400, because the NULL-safe guard reaches driver-sql as `$ne: null` on a JSON column, where the engine answers the rows

[objectstack-fleet]

Filing gate: ① a product defect with a measured reach:. Finding class (a).

reach: the public door POST /api/v1/analytics/query, on a query the ObjectQL strategy serves (a date-bucketed query, or a host with no raw SQL). #20887's dev measured it at 5bb764181 (PR #20916's branch, report 5916988260 on #20887, out_of_scope_findings[1]). The readings are the dev's, and this seat did not re-run them.

Filed by the domain:services execution seat (#6021, session_01XY5uCwTjZj7884yYtyur4H) from #20887's dev report. ⛔ Filed bare: routing and grading belong to triage. ⛔ Not a claim.

What happens

On a fixture with owners, a multiple lookup:

• The ObjectQL strategy refuses: where: { $not: { owners: { $contains: 'u1' } } } answers 400 INVALID_FILTER ("Operator $ne on field owners WAS NOT APPLIED ... JSON TEXT column").
• The engine answers: engine.find answers d2, d4 for the same filter.
• The native strategy answers: b, d.

Mechanism, as the dev read it:

1. The analytics where door adds a NULL-safe guard under $not, { owners: { $null: false } }, and it becomes a set leaf.
2. ObjectQLStrategy.convertFilter (packages/services/service-analytics/src/strategies/objectql-strategy.ts) hands that leaf to the engine as { $ne: null }.
3. driver-sql refuses $ne over a multi-valued JSON column.

PR #20916 routes the nested-relation form around the guard (shieldNestedRelations). A direct operator on a multi-valued column still meets it.

Scope for whoever takes it (⛔ not a ruling)

• The ObjectQL strategy answers the engine's rows for a $not over a multi-valued column. The guard reaches the engine in a form driver-sql applies, or the strategy does not add a guard the engine already carries. ⛔ No driver change to accept $ne: null on a JSON column as a special case.
• Pins: $not over $contains on a multiple lookup answers the engine's rows on both strategies, plus a single-valued control.
• Serial: PR fix(service-analytics)!: the nested-relation filter gets the engine's answer on every analytics face — the related object read as the caller, capped (#20887) #20916 (#20802 analytics half (domain:services): the cube read and the analytics read scope answer { relation: { field: value } } as the engine seam now serves it — as the caller, capped, one answer on every face #20887, in flight) edits filter-normalizer.ts and objectql-strategy.ts. This card is dispatched after it lands.

Reader who acts

Triage (grade and route; the files are domain:services' service-analytics), then the owning seat.

Dedupe

mcp__github__search_issues, repo-scoped, open and closed, in the act that filed this card:

• 「analytics ObjectQL strategy $not $contains multiple lookup $ne null refused JSON column」: 21 hits.
  • [finding] a per-aggregation filter with $contains on a multiple lookup counts 0 on every driver while the same where finds the rows: the engine's aggregation evaluator never matches a stored array #20873 (open) is the per-aggregation filter's $contains on a multiple lookup counting 0 in the engine's aggregation evaluator: a neighbour at another position.
  • driver-sql: a declared operator on a multiple: true (JSON array) column silently answers wrong — $in/$eq always zero rows, $nin returns the rows it was asked to EXCLUDE #7398, driver-sql (PG): every DELETE of an object targeted by a multiple: true reference 500s — the cascade probe's $contains compiles to LIKE against a json column (42883) #18172, analytics: read-scope-sql's ruled $not-over-$in-empty residue has no open card — and #13640 turned it into an echo-vs-execution disagreement on the ObjectQL strategy #13926 and ObjectQLStrategy.execute cannot see a cross-object filter nested in a conjunct, so it accepts a set /analytics/sql rejects #10759 are closed, at other positions.
  • None is the analytics $not guard.
• 「analytics where NULL-safe $not guard $null false multi-valued lookup ObjectQL convertFilter」: 22 hits, all closed ([finding] a no-operator object under a lookup, master_detail or json field answers per driver: the declared nested-relation filter returns no rows on memory and a 400 on SQL, and a json object comparand deep-equals on memory and is refused on SQL #20745, spec: FilterConditionSchema (the save-time door) admits a non-boolean $null / $exists flag, so a stored dataset or widget filter carrying one saves clean and is refused 400 on every query face #20116, service-analytics: ObjectQLStrategy.convertFilter has no icontains arm, so any $icontains (a valid comparand included) on a query the ObjectQL strategy serves throws a bare Error with no code or status #20098 and others). None covers it.

Dedupe words: analytics $not multiple lookup refused · convertFilter $ne null JSON column · analytics NULL guard multi-valued

---

Generated by Claude Code

[objectstack-fleet]

Triage: first grade — bug · priority:p2 · domain:services · area:reports · pm:blocked. Direction: the ObjectQL strategy answers the engine's rows for a $not over a multi-valued column, after #20887's PR lands

Triage seat (objectstack-wide, seat post #6015) · session_01AavokzJ5DndAwitDXvKy4U · 2026-09-30T19:53Z. ⛔ Not a claim, ⛔ not a dispatch.

Blocked-by: #20887

Re-read at origin/main.

• packages/services/service-analytics/src/strategies/objectql-strategy.ts:1872 hands the set leaf to the engine as { $ne: null }.
• driver-sql refuses a scalar comparison over a multi-valued (JSON) column with the "WAS NOT APPLIED" refusal (sql-driver.ts:3434 / :3443).

So the mechanism the dev read holds at source. The readings themselves are the dev's (5916988260).

Why p2. A public door, POST /api/v1/analytics/query, refuses with 400 a filter that the engine answers and the native strategy answers. It reaches every date-bucketed query and every host without raw SQL. Nothing leaks: it is a wrong refusal, not a wrong answer. So there is no security, and it is not p1.

Direction. It is the card's scope, confirmed:

• The guard reaches the engine in a form driver-sql applies, or the strategy stops adding a guard the engine already carries. The dev picks which, from measurement.
• ⛔ No driver special case for $ne: null on a JSON column.
• Pins: $not over $contains on a multiple lookup gives the engine's rows on both strategies, plus a single-valued control.
• Neighbour, not folded: [finding] a per-aggregation filter with $contains on a multiple lookup counts 0 on every driver while the same where finds the rows: the engine's aggregation evaluator never matches a stored array #20873 (domain:engine) is the per-aggregation filter's $contains counting 0 in the engine's aggregation evaluator. It sits at a different position and lane, and each card stays whole.

Why blocked. PR #20916 (#20887, in flight) edits objectql-strategy.ts and filter-normalizer.ts, which are hot files. This card goes after it lands, as the card itself asks.

---

Generated by Claude Code

[objectstack-fleet]

Claim: PM loop round 1 · 2026-10-01T01:55Z
Session: session_01XY5uCwTjZj7884yYtyur4H
Account: os-justin (the seat's linked user as GET /user answers it; the card's assignee)
Branch: claude/issue-20918-objectql-not-multivalue
Worktree: objectstack-issue-20918
Domain: domain:services
Seat: domain:services (seat 1, seat post #6021)
Unlock: triage's Blocked-by: #20887 (5918567230) is met. #20887 is closed, and its PR #20916 landed as 8d329f02 on main. This act moves the card from pm:blocked straight to pm:dispatched. Batch: the maintainer raised this seat's concurrency to 5 (「任务很多，并发加到5」, in this seat's session, 2026-10-01T01:5xZ).
File surface:

• packages/services/service-analytics/src/strategies/objectql-strategy.ts (convertFilter, where the set leaf becomes { $ne: null }).
• packages/services/service-analytics/src/strategies/filter-normalizer.ts, only if the measured fix is that the strategy stops adding a guard the engine already carries, at the guard's source.
• Pins: service-analytics/src/__tests__/ (new files), and a new route pin beside packages/rest/src/analytics-*.test.ts. The client census ledger and its declared-inputs pair are touched only if a new pin adds a censused call site.
• .changeset/20918-*.md.
• ⛔ Not the surfaces this seat holds in the same package:
  • [finding] analytics NativeSQL answers a multi-value dimension one group per serialized array on SQLite and 500 on PostgreSQL, and a count_distinct over a JSON-stored field 2 / 500; the engine door #20808 adds does not see it #20912 (structured-json-dimension-door.ts, dataset-compiler.ts and two analytics-service.ts call sites);
  • [finding] analytics: on PostgreSQL the native-SQL path answers a measure the response declares number as a string (count: "2"), where SQLite answers 2 — the class #20335 closed at the engine door #20889 (strategies/native-sql-strategy.ts execute, packages/core/src/utils/aggregate-answer.ts, and driver-sql's two regions).
• ⛔ Not packages/drivers/**: triage rules out a driver special case for $ne: null on a JSON column. Not packages/objectql/src/** or packages/spec/src/**. If the fix needs one of these, the dev stops and reports.
  (stop on a breach outside these; explain in the report)
  Container & model: M, mode:subagent, model: opus (dispatch-gates --tier: no path-derived mandate, floor sonnet · default opus · ceiling fable). The contract review runs at CONTRACT_REVIEW_TIER.
  Clause-②: yes
• Why yes: a $not over a multi-valued column that the ObjectQL strategy refused with 400 is then answered. That widens what the door accepts. The dev measures it, and the PR's line 2 carries the measured grammar.
  Thread-read: 5918567230
  Serial constraints cleared:
• PR fix(service-analytics)!: the nested-relation filter gets the engine's answer on every analytics face — the related object read as the caller, capped (#20887) #20916 (#20802 analytics half (domain:services): the cube read and the analytics read scope answer { relation: { field: value } } as the engine seam now serves it — as the caller, capped, one answer on every face #20887), the serial holder triage named, has landed.
• Open PRs (21 read at this stamp): none touches objectql-strategy.ts, filter-normalizer.ts, analytics-service.ts or the rest analytics pins.
• The area:reports axis: [finding] analytics NativeSQL answers a multi-value dimension one group per serialized array on SQLite and 500 on PostgreSQL, and a count_distinct over a JSON-stored field 2 / 500; the engine door #20808 adds does not see it #20912 and [finding] analytics: on PostgreSQL the native-SQL path answers a measure the response declares number as a string (count: "2"), where SQLite answers 2 — the class #20335 closed at the engine door #20889 (both this seat's) are in flight in the same package, on files disjoint from this surface, which the exclusion above holds out.
• Neighbour, not folded: [finding] a per-aggregation filter with $contains on a multiple lookup counts 0 on every driver while the same where finds the rows: the engine's aggregation evaluator never matches a stored array #20873 (domain:engine, the engine's aggregation evaluator), as triage ruled.

---

Generated by Claude Code

[objectstack-fleet]

os-dev-report
{
"issue": 20918,
"status": "done",
"branch": "claude/issue-20918-objectql-not-multivalue",
"pr": "#21036",
"session": "session_01XY5uCwTjZj7884yYtyur4H (mode:subagent, the dispatching session's id)",
"premise_still_valid": true,
"summary": "ObjectQLStrategy.convertFilter now hands the engine the set / notSet leaves as { $null: false } / { $null: true }, the spellings the engine's own lowering (lowerFilterCondition, run at engine.ts:1204) emits for the same NULL-safe guards. On the base they were { $ne: null } (objectql-strategy.ts:1920) and the bare null (:1921), which driver-sql's JSON-column gate refuses for any comparand. So { $not: { owners: { $contains: 'u1' } } } on a multiple lookup answered 400 INVALID_FILTER on the ObjectQL strategy, where engine.find and the native strategy answer b, d, e. Route chosen from measurement (triage's either/or): 'stop adding the guard' would leave the author's own null tests ({ owners: { $null: false | true } }, $exists) refused, because they carry no guard and become the same set / notSet leaf. It would also split the shared normalizer per strategy. The spelling fix serves every row, and the engine now recognises the guard as its own (the driver receives 2 guards for the card's filter, where it received 3). The notSet arm is fixed beside the set arm: same function, same defect class, and it is the only arm that serves a $not over $notContains on a multi-valued column (the card's scope). No driver, engine or spec file is touched. The native strategy is unchanged (G4).",
"tests": "All at b5a2e44 (origin/main 2f2fa11 merged, no rebase, no surface overlap), every run under os-verify-lock with each exit code captured before any pipe. service-analytics: test exit 0, 151 files, 3467 passed; typecheck exit 0, with 151/151 test files in the tsc program (--listFiles). rest: typecheck exit 0 (tsc + check:test-typecheck); analytics-.test.ts 16 files, 225 passed / 3 skipped. runtime consumer sweep (analytics- + cross-field-refusal-operand-withhold): 5 files, 38 passed / 4 skipped. Pins red first at f45d92d: the new route pin rest/src/analytics-not-multivalue-null-guard.test.ts was 5 red (ObjectQL multi-valued rows) / 7 green (native + single-valued control) on the base, and is now 12/12. The new unit pin objectql-null-predicate-json-column.test.ts was 6 red / 3 green, and is now 9/9. Ten cases in five existing pin files recorded the old engine-input spelling and were respelled; no row expectation moved. Ablations, predicted before running, at b6c4893 (b6c4893..b5a2e44 touches nothing under packages/services or packages/rest). Each mutated via scripts/ablation-replace.mjs (anchor 1→0, blob 63ce11d10a04 → 75b05cae8989 / aeab45c348ec / 3f893a607ea2), rebuilt service-analytics, and passed ablation-dist-preflight (old arm present in dist). The restore proved blob == HEAD 63ce11d10a04, git diff HEAD empty and the tree clean, then rebuilt and passed preflight --absent per marker (exit 0). A0 (both arms reverted): full analytics suite 16 red, exactly the 6 new + 10 re-judged cases, 3451 passed; route 5 red. A1 (set arm): unit 7 red, route 2 red. A2 (notSet arm): unit 13 red, route 3 red. Every count equals its prediction. Gates: dispatch-gates --commands derived 62, run as one locked sequential script. 61 exit 0: check:type-check-debt first exited 3 (runtime and driver-turso unbuilt), then exited 0 after building that closure, 'none above its recorded number'. 1 NOT MEASURED: check:dual-build-cjs-loads, exit 3, reason: prerequisite is a whole-workspace build (38 packages with no dist/), outside this order's build-closure bound. The 4 roster families exit 0 (check-changeset-fixed, check:authz-resolver, check:error-code-casing, check:filter-alias-parity). dispatch-gates --ran: 62 derived, 61 run, 1 NOT-MEASURED, 0 UNRUN. Lint narrowed and proved: population = the 8 changed .ts files, isPathIgnored false for all 8, parserOptions.project/projectService unset for all 8 (no type-aware lint, so no untouched file's verdict moves); eslint --no-inline-config --format json: 8 files, 0 errors, 0 warnings. CI: in_progress at report time (31 check runs: 11 success, 3 skipped, 17 in progress, 0 failed).",
"mcp_calls": "0",
"api_writes": "3 relay strokes via scripts/pm/fleet-write (each one POST /repos/objectstack-ai/objectstack/dispatches executed as objectstack-fleet[bot]): (1) pr_create → POST /repos/objectstack-ai/objectstack/pulls (draft #21036); (2) label-write.mjs --assign os-justin → POST /repos//issues/21036/assignees; (3) post-stamped.mjs → POST /repos//issues/20918/comments (this report). Plus git push (not REST): 5 pushes to claude/issue-20918-objectql-not-multivalue (empty branch, pins + fix, re-judged pins, changeset, merge of main). No label other than the assignee was written: the order narrowed the label budget to the assignee, and a changeset is present, so there is no skip-changeset.",
"open_questions": [],
"out_of_scope_findings": [
"carrier: none · noted, not filed: rows 8-9 of the PR table. { owners: { $ne: null } } and the bare { owners: null } on a multi-valued lookup are now answered by both analytics strategies (the native one already answered them), while engine.find refuses them. driver-sql's JSON-column gate reads the operator, not the comparand, and triage ruled out a driver special case for $ne: null on a JSON column. Recorded in the PR's Acceptance notes.",
"carrier: #20873 (domain:engine, open) · noted, not filed: measured evidence for that card. A dataset measure filter { $not: { owners: { $contains: 'u1' } } } and { owners: { $notContains: 'u1' } } counts 5 through engine.aggregate's per-aggregation filter, where engine.find over the same where (and both analytics strategies, after this PR) count 3. The same fixture is in rest/src/analytics-not-multivalue-null-guard.test.ts. Dedupe words: aggregation filter $contains multiple lookup · engine.aggregate per-aggregation filter multi-valued",
"carrier: none · noted, not filed (NOT MEASURED): an external object's datetime column on driver-sql's legacy-repair path, where { f: null } and { f: { $null: true } } take different driver arms (applyNormalizedComparison vs whereNull). This PR changes which spelling the ObjectQL strategy sends there, and the answer was not measured. Recorded in Acceptance notes."
],
"deviations": [
"Surface: the claim named new pin files only. Five existing pin files under service-analytics/src/tests/ were edited, because their assertions recorded the engine-input spelling the fix changes (filter-normalizer-not-null-safe, filter-value-type-fidelity, objectql-contains-canonical-operator, objectql-daterange, where-boolean-flag-refusal). None is #20912's or #20889's. Declared in the PR body.",
"Order G3 wording ('excludes NULL rows'): measured, the guard makes the negation answer the NULL row (row e is in b, d, e). The engine already does this. The PM's ~:1872 location is :1920 at the base.",
"The full @objectstack/rest local suite was not run, narrowed to its 16 analytics-*.test.ts files: the diff adds one test file there, and those files are the ones that load service-analytics. check:dual-build-cjs-loads is NOT MEASURED (prerequisite: a whole-workspace build). Both are left to CI."
]
}

---

Generated by Claude Code

[objectstack-fleet]

ACCEPT: #20918 · PR #21036 at b5a2e448 · domain:services seat (#6021) · session_01XY5uCwTjZj7884yYtyur4H · 2026-10-01T02:48Z

The dev report is 5923712419, read on GitHub.

Checklist, read on GitHub rather than from the report:

• Shape: a draft onto main, 9 files, +535 / −29.
  • The fix is two lines of ObjectQLStrategy.convertFilter (objectql-strategy.ts): the set and notSet leaves reach the engine as { $null: false } / { $null: true }, the spelling the engine's own shared lowering emits for the same NULL-safe guards. They were { $ne: null } and the bare null, which driver-sql refuses over a JSON column.
  • Line 1 is Fixes #20918, and line 2 is Clause-②: yes, measured. No governed path.
• Triage's either/or ("the guard reaches the engine in a form driver-sql applies, or the strategy stops adding a guard the engine already carries"):
  • The measurement picked the first.
  • Dropping the guard would leave an author's own null tests on such a column refused, because they become the same leaf, and it would split the shared normalizer per strategy.
  • ⛔ No driver, engine or spec file is touched, as triage required.
• Surface amendment, accepted as declared: five existing pin files in service-analytics/src/__tests__/ recorded the engine-input spelling the fix changes. Ten cases were respelled, and no row expectation moved. None of the files belongs to [finding] analytics NativeSQL answers a multi-value dimension one group per serialized array on SQLite and 500 on PostgreSQL, and a count_distinct over a JSON-stored field 2 / 500; the engine door #20808 adds does not see it #20912's or [finding] analytics: on PostgreSQL the native-SQL path answers a measure the response declares number as a string (count: "2"), where SQLite answers 2 — the class #20335 closed at the engine door #20889's surface.
• Red first:
  • the route pin: 5 red / 7 green, then 12 / 12;
  • the unit pin: 6 red / 3 green, then 9 / 9.
• Ablations, each matching its prediction: both arms reverted (16 red); the set arm alone; the notSet arm alone. Each restore was proven, with a dist preflight both ways.
• Green: service-analytics 151 files, 3,467 passed, typecheck exit 0; the rest analytics pins, 225 passed; the runtime consumer sweep, 38 passed.
• Gates: 62 derived. 61 exit 0. check:dual-build-cjs-loads is NOT MEASURED (whole-workspace build), and CI owns it.

Findings, line by line:

• Rows 8-9 ($ne: null and the bare null on a multi-valued lookup are now answered by both analytics strategies, while engine.find refuses them): Acceptance notes. The at-tier review judges whether that leniency is right.
• engine.aggregate's per-aggregation filter counts a $not / $notContains over a multiple lookup differently from engine.find: measured evidence for [finding] a per-aggregation filter with $contains on a multiple lookup counts 0 on every driver while the same where finds the rows: the engine's aggregation evaluator never matches a stored array #20873 (domain:engine). A pointer goes there; no new card.
• An external object's datetime column on driver-sql's legacy-repair path (the two null spellings take different driver arms; NOT MEASURED): Acceptance notes.

Landing waits for every check green on the head and the at-tier contract review. After the merge, this seat checks that Fixes #20918 closed the card.

---

Generated by Claude Code