Aggregate NULLs

Honest by Construction

Expected aggregate NULL facts are assertions only.

highlighted = computed this step

Honest by construction

Expected aggregate facts are assertions only.

compiler is source of truth\text{compiler is source of truth}

Render checked groups

The guarded aggregate query renders 3 checked groups after recomputation.

checked groups=3\text{checked groups}=3

Aggregate NULL examples are tiny finite table transforms; SQL dialect completeness, optimizer behavior, indexing, product behavior, and performance claims are out of scope.

Honest by construction: base rowsidregionqtynote1east2ok2eastNULLmissing3west5ok4westNULLNULL5northNULLmissing6northNULLNULL Group bucketskeysourceRowsrowCount[east][0, 1]2[west][2, 3]2[north][4, 5]2 Aggregate resultskeysourceRowsaggregates[east][0, 1]{rows_seen:2, qty_seen:1, qty_sum:2}[west][2, 3]{rows_seen:2, qty_seen:1, qty_sum:5}[north][4, 5]{rows_seen:2, qty_seen:0, qty_sum:NULL} NULL-skip detailskeyaggregateseencounted/addednullSkippedresult[east]rows_seen2202[east]qty_seen2111[east]qty_sum2112[west]rows_seen2202[west]qty_seen2111[west]qty_sum2115[north]rows_seen2202[north]qty_seen2020[north]qty_sum202NULL Output rowsregionrows_seenqty_seenqty_sumeast212west215north20NULL Aggregate NULL factsfactvalueinputRowCount6groupCount3aggregateCount3outputRowCount3countStarCountsEveryRowyescountColumnSkipsNullyessumSkipsNullyesallNullSumGivesNULLgroupOrderfirst_seen_input_orderoutputOrderdeterministic_group_order

Try to tamper

Wrong expected facts, derived sidecars, missing group columns, duplicate aliases, bad SUM columns, bools, and floats fail closed.

tamper rejected\text{tamper rejected}

Summary

This book shows exact finite COUNT star, COUNT column, and integer SUM NULL behavior only.

scoped aggregate NULL model\text{scoped aggregate NULL model}