set_agg and histogram disagree on NULL handling, and there is no keys-only distinct aggregation

[kaikalur]

Summary

set_agg and histogram disagree on NULL handling, and as a result neither is a good building block for an exact distinct count. There is also no aggregation that keeps just the distinct key set, so histogram is commonly used for that and pays for per-key counts that are then discarded.

1. Inconsistent NULL handling

histogram ignores NULL inputs; set_agg treats NULL as a set element:

SELECT approx_distinct(x), cardinality(histogram(x)), cardinality(set_agg(x)), set_agg(x)
FROM (VALUES 1, 1, 2, NULL) t(x);
--  2  |  2  |  3  |  [1, 2, null]

SELECT approx_distinct(x), cardinality(histogram(x)), cardinality(set_agg(x))
FROM (VALUES NULL, NULL) t(x);
--  0  |  NULL  |  1

count(DISTINCT x) and approx_distinct(x) both ignore NULLs, and histogram agrees with them. set_agg does not, which is surprising for two functions that are otherwise "collect the distinct values".

Whatever the intended behaviour, it would help to state it explicitly in the docs for both functions — today neither page mentions NULLs.

2. No keys-only distinct aggregation

cardinality(histogram(x)) is a natural way to get an exact distinct count per group, and it is the only builtin that gets the NULL semantics right. But histogram maintains a count per key that this use throws away. In GroupedTypedHistogram that is a LongBigArray counts alongside the key BlockBuilder, plus the chaining arrays, so roughly 28-32 bytes per distinct entry where SetOfValues (backing set_agg) uses roughly 13-19.

For an aggregation whose state is proportional to the number of distinct values, that is about a 2x memory difference, and it also materializes a map on output only for cardinality() to read its size.

A dedicated aggregation returning bigint directly — SetOfValues storage with approx_distinct's NULL semantics and no count array or map output — would be roughly half the memory and would remove the intermediate map entirely.

Context

This came out of tuning a workload with many conditional approx_distinct calls over one high cardinality column, where per-group state dominated memory. cardinality(histogram(hash(x))) was substantially cheaper than the alternatives, but the wasted count array is a known overhead, and set_agg could not be substituted because of the NULL difference above.

Related: #28363 adds an optimizer rule that rewrites approx_distinct to coalesce(cardinality(histogram(hash(x))), 0); it would use a keys-only aggregation instead if one existed.