Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

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

Open
ianthrive opened this issue Jul 25, 2022 · 0 comments

Comments

@ianthrive
Copy link

ianthrive commented Jul 25, 2022

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                                                                                                                            │
└────────────┴─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
None yet
Projects
None yet
Development

No branches or pull requests

1 participant