Multi-Column Groups

NULL as a Key Component

NULL key components stay visible.

highlighted = computed this step

NULL in the key

A NULL key component is still displayed as part of the group key.

NULL key component\text{NULL key component}

Read NULL key buckets

The compiler sees 2 NULL key components across 5 buckets.

NULL key cells=2\text{NULL key cells}=2

Multi-column grouping examples are tiny finite table transforms; SQL dialect completeness, optimizer behavior, indexing, product behavior, and performance claims are out of scope.

NULL as a key component: base rowsidregionstatusqty1eastopen22eastopen33eastclosed14westopen45westNULL56eastNULLNULL7westopen1 Group bucketskeysourceRowsrowCount(east, open)(0, 1)2(east, closed)(2)1(west, open)(3, 6)2(west, NULL)(4)1(east, NULL)(5)1 Aggregate resultskeysourceRowsaggregates(east, open)(0, 1){n:2, total_qty:5}(east, closed)(2){n:1, total_qty:1}(west, open)(3, 6){n:2, total_qty:5}(west, NULL)(4){n:1, total_qty:5}(east, NULL)(5){n:1, total_qty:NULL} Aggregate detailskeyaggregateseenaddednullSkippedresult(east, open)n202(east, open)total_qty2205(east, closed)n101(east, closed)total_qty1101(west, open)n202(west, open)total_qty2205(west, NULL)n101(west, NULL)total_qty1105(east, NULL)n101(east, NULL)total_qty101NULL Output rowsregionstatusntotal_qtyeastopen25eastclosed11westopen25westNULL15eastNULL1NULL Multi-group factsfactvalueinputRowCount7keyColumnCount2groupCount5aggregateCount2outputRowCount5nullKeyComponents2groupOrderfirst_seen_input_orderoutputCarriesKeyColumnsyes

Visible, not guessed

Rows with NULL status form visible key tuples instead of being hidden.

NULL is visible\text{NULL is visible}

Summary

This finite model treats NULL as a visible group-key component.

visible key cell\text{visible key cell}