citusdata / citusdata/postgresql-hll

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

Open
#129 0 comments 0 reactions 0 assignees View on GitHub
Dominant language
C
Stars
1.2k
Forks
115
PR merge metrics
No merged PRs in 30d

Description

This would make the behavior consistent with `count()` and the change in #2 .

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

Contributor guide

No contributing guide indexed for this repository

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.