Aggregate NULLs

No Hidden Rows and Order

Source rows and display order stay visible.

highlighted = computed this step

No hidden rows

The display order is deterministic so the visual stays checkable.

deterministic display\text{deterministic display}

Read source rows

All 6 source rows are accounted for, and group order is first-seen input order.

all rows accounted\text{all rows accounted}

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

No hidden rows and order: 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

Order is model display

First-seen group order is a finite display convention here, not a product output-order claim.

display convention\text{display convention}

Summary

The buckets list source row indexes, so no row disappears silently.

source rows listed\text{source rows listed}