postgresql-hll
postgresql-hll copied to clipboard
hll_add_agg() with FILTER and no input rows should return hll_empty() instead of NULL
This would make the behavior consistent with count()
and the change in #2 .
select
count(v) as count,
count(v) filter (where v = 0) as count_input_0_rows,
count(v) filter (where v > 0) as count_input_5_rows,
hll_cardinality(hll_add_agg(hll_hash_integer(v))) as hll,
hll_cardinality(hll_add_agg(hll_hash_integer(v)) filter (where v > 0)) as hll_input_5_rows,
hll_cardinality(hll_add_agg(hll_hash_integer(v)) filter (where v > 4)) as hll_input_1_rows,
hll_cardinality(hll_add_agg(hll_hash_integer(v)) filter (where v = 0)) as hll_input_0_rows,
hll_cardinality(coalesce(hll_add_agg(hll_hash_integer(v)) filter (where v = 0), hll_empty())) as hll_input_0_rows_coalesce
from (values (1), (2), (3), (4), (5)) as data(v)
\gx
┌─[ RECORD 1 ]──────────────┬────────┐
│ count │ 5 │
│ count_input_0_rows │ 0 │
│ count_input_5_rows │ 5 │
│ hll │ 5 │
│ hll_input_5_rows │ 5 │
│ hll_input_1_rows │ 1 │
│ hll_input_0_rows │ (null) │
│ hll_input_0_rows_coalesce │ 0 │
└───────────────────────────┴────────┘
The value for hll_input_0_rows
should be 0
but instead returns NULL
because hll_add_agg()
returns NULL
instead of hll_empty()
due to no input rows.
A workaround is to wrap it in coalesce(..., hll_empty())
.
select version(), extversion from pg_extension where extname = 'hll' \gx
┌─[ RECORD 1 ]─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│ version │ PostgreSQL 14.1 (Ubuntu 14.1-1.pgdg18.04+1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 7.5.0-3ubuntu1~18.04) 7.5.0, 64-bit │
│ extversion │ 2.16 │
└────────────┴─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘