Optimizer aggregate pushdown oracle review

Reviewed implementation: 37350e5b58b6006f24d9e7c68b894bac7b45ec46. Baseline with the original optimizer: 33a194941c8d51f8f98babb999fef2987dd6ff8b. The review record and changeset follow the reviewed implementation. They do not change the oracle or production code.

Claim and evidence

An outer predicate on an aggregate subquery must run after aggregation. The optimizer may move it below the aggregate only when that move preserves the result. The original selectHasAggregates missed add(sum(v), 0) and aggregates inside caseWhen. The optimizer moved isUndefined(s.k) to the source row's k field. The global aggregate omits k from its public row. The moved predicate rejected both source rows and removed the public result.

The oracle uses source rows {k:1,v:10} and {k:2,v:20}. Independent recomputation gives total 30. A materialized aggregate Collection and a nested aggregate under a left join must both expose the same post-filter row. The direct aggregate and grouped query are nearby controls. A left join keeps the aggregate row when no matching joined row exists. An earlier inner-join probe returned no row even without a WHERE clause for both direct and wrapped aggregates, so it could not isolate optimizer pushdown.

On baseline, the direct and grouped controls passed. The arithmetic wrapper, the caseWhen condition-only aggregate, and the caseWhen default-only aggregate failed the public toArray assertion. Each returned [] instead of the modeled row. These are assertion kills at the intended checkpoint. After the fix, all five oracle cases passed. The surrounding query directory passed 87 files and 3,440 tests before the two final conditional cases were added. The final focused optimizer and oracle run passed 51 tests. TypeScript, ESLint, and Prettier checks passed.

ORC-001 through ORC-011

Requirement	Outcome
ORC-001 Contract authority and limits	SQL aggregate semantics and the optimizer's documented safety rule require post-aggregate filtering. The owner checks the listed expressions and public snapshot under a left join. The coverage map names the omitted predicates, wrappers, join types, and incremental histories.
ORC-002 Independent judgment	The expected sum comes from Array.reduce over source rows. The expected outer result follows from applying the predicate after projection. Neither computation imports the optimizer's aggregate detector.
ORC-003 Distinguishable responsibilities	The oracle file states the law, stateless sum model, bounded expression grammar, public Collection driver, and toArray refinement check at synchronous publication.
ORC-004 Generated-history controls	Not applicable. The file enumerates fixed cases and does not claim generated-history coverage.
ORC-005 Production path and observation	createLiveQueryCollection compiles nested aggregate and outer join queries. The test compares exact public rows after startSync: true. Direct and materialized controls show the public path reaches the aggregate and predicate.
ORC-006 Checker calibration	The original optimizer is the wrong-design control. Its wrapped and conditional cases fail by value assertion at the named checkpoint. The direct and grouped controls pass on the same baseline.
ORC-007 Fixed/random replay	Not applicable. The owner has no important generated property.
ORC-008 Stateful-model minimality	Not applicable. The model recomputes one sum and retains no transition state.
ORC-009 Vocabulary mapping	No model-only lifecycle or state term is introduced. “Source rows,” “public row,” and “public snapshot” follow the glossary.
ORC-010 Failure fidelity and cleanup	Not applicable. The fixed cases do not shrink, capture mutable traces, or run cleanup that can replace the assertion failure.
ORC-011 Second formulation	The materialized aggregate Collection and the nested subquery have the same public projection and predicate. The former prevents cross-boundary pushdown and catches the plausible shared-fault risk in the sum model. Each expected result has one row, so order and multiplicity do not need normalization.

The bounded repair covers the contract × fixed source rows × nested left-join path × first public snapshot cells above. It does not prove all optimizer rewrites safe. The coverage map owns the remaining cells and the separate global-aggregate inner-join observation.