citusdata / postgresql-hll

PostgreSQL extension adding HyperLogLog data structures as a native data type
http://blog.aggregateknowledge.com/2013/02/04/open-source-release-postgresql-hll/
Apache License 2.0
1.14k stars 116 forks source link

hll_add_agg() with FILTER and no input rows should return hll_empty() instead of NULL #129

Open ianthrive opened 2 years ago

ianthrive commented 2 years ago

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                                                                                                                            │
└────────────┴─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘