Skip to content

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

Open
@ianthrive

Description

@ianthrive

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

Metadata

Metadata

Assignees

No one assigned

    Labels

    No labels
    No labels

    Type

    No type

    Projects

    No projects

    Milestone

    No milestone

    Relationships

    None yet

    Development

    No branches or pull requests

    Issue actions