[Design] Supporting Predicates in Aggregation Functions

[Selvomega]

Why

Neither the pre- nor the post-ASAP IR can give an aggregate function its own predicate. AggIntent has no filter, and SummaryAgg has none either. A row predicate can only sit on the relation (Scan.predicates or Filter), and there it applies to every measure.

So this query, which is one scan and one grouping, cannot be planned at all:

SELECT l_shipmode, count(CASE WHEN l_returnflag = 'R' THEN 1 END), sum(l_quantity)
FROM lineitem GROUP BY l_shipmode

It parses, and lowering rejects it:

unsupported feature: COUNT of a nullable expression or with FILTER requires explicit per-aggregate null/filter semantics

The frontend has no choice. AggIntent::Count counts rows and takes no argument, so "count only the rows where l_returnflag = 'R'" has nowhere to go. Lowering it as a plain Count would silently drop the condition.

The two workarounds available today, both rewritten by hand in the SQL:

• Split it into two Aggregates and join them back. A Filter below an Aggregate changes which groups exist, so the join must be Left and the count must be coalesced. One operator becomes three, plus a second scan.
• Encode the predicate in a derived column, which is how countIf is lowered: Sum(CASE WHEN p THEN 1 ELSE 0 END). Only count has such a rewrite. Every other function needs CASE WHEN p THEN x END and relies on NULL-skipping, which the IR does not define. The predicate also disappears from the IR.

What

A measure can carry a predicate, with SQL FILTER (WHERE …) semantics: only rows where it is TRUE update that measure, and groups are still formed from all rows. The example becomes:

Aggregate(by l_shipmode, [Count, Sum(l_quantity)], filters: [Some(l_returnflag = 'R'), None])
  Scan lineitem

How

Crate	Change
asap-types	Aggregate and ExactOperation::Aggregate gain filters: Vec<Option<Predicate>>, parallel to measures, #[serde(default)]. SummaryAgg gains filter: Option<Predicate>. Resolution, canonicalization and CSE include them. POST_ASAP_DAG_WIRE_VERSION goes from 2 to 3
asap-frontend-sql	Lower count(CASE WHEN p THEN x END) to Count with filter p AND x IS NOT NULL, and lower an aggregate's FILTER (WHERE …) into filters. Drop the COUNT/corr … FILTER rejections. (Parsing FILTER also needs a sqlparser dialect with supports_filter_during_aggregation; the generic one has it off. That is a parser setting, not an IR change)
asap-aware-mapping	Filtered measures are not bindable yet and stay in KeepPreAsap

• A parallel vector, not a Measure struct, following output_names. Existing readers of measures keep compiling.
• SummaryAgg gets a field, not a Filter child, so sketches with different predicates can still share one child.
• The wire version bumps so that an old consumer fails loudly instead of reading a filtered SummaryAgg as unfiltered.

Tests

TODO — once implemented.

• Serde round-trip; IR serialized before this PR still reads.
• CSE does not share two aggregates that differ only in one predicate.
• The example above lowers to one Aggregate, with no Join.

Not in this PR

• Binding filtered measures to summaries.
• NULL semantics of unfiltered inputs.
• ClickHouse sumIf/avgIf/….