Aggregate NULLs

Base Rows with NULLs

Visible rows come before aggregate facts.

highlighted = computed this step

Base rows with NULLs

Start with visible rows. Some quantity cells are NULL.

rows first\text{rows first}

Read the rows

The pinned table has 6 source rows and 1 all-rows bucket.

rows=6\text{rows}=6

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

Base rows with NULLs: base rowsidregionqtynote1east2ok2eastNULLmissing3west5ok4westNULLNULL5northNULLmissing6northNULLNULL Group bucketskeysourceRowsrowCount[all_rows][0, 1, 2, 3, 4, 5]6 Aggregate resultskeysourceRowsaggregates[all_rows][0, 1, 2, 3, 4, 5]{rows_seen:6, qty_seen:2, qty_sum:7} NULL-skip detailskeyaggregateseencounted/addednullSkippedresult[all_rows]rows_seen6606[all_rows]qty_seen6242[all_rows]qty_sum6247 Output rowsrows_seenqty_seenqty_sum627 Aggregate NULL factsfactvalueinputRowCount6groupCount1aggregateCount3outputRowCount1countStarCountsEveryRowyescountColumnSkipsNullyessumSkipsNullyesallNullSumGivesNULLgroupOrderfirst_seen_input_orderoutputOrderdeterministic_group_order

No hidden input

The aggregate facts are recomputed from exactly the displayed source rows.

no hidden rows\text{no hidden rows}

Summary

NULL cells stay visible before any aggregate skips them.

NULL visible first\text{NULL visible first}