[Bug]: Inconsistent behaviour of select sum over no rows

[Petkomat]

What type of bug is this?

Incorrect result

What subsystems and features are affected?

Other

What happened?

I have a rather simple continuous aggregate (the exact definition below) that contains

• two counting columns c1 and c2,
• column reference (UUID),
• time column min5_start.

My data refers to the year 2025.

The query

SELECT SUM(c1) AS total1, SUM(c2) AS total2
FROM agg5min
WHERE reference IN ('some uuid')
    AND min5_start >= '2025-08-12'
    AND min5_start < ' 2025-09-11';

returns no rows, even though PostgreSQL documentation explicitly says that except for count, these [aggregate] functions return a null value when no rows are selected. In particular, sum of no rows returns null.

However, if I change the conditions, so that they refer to the year 2020 (or whatever is "out of range"), a row with two null values is returned (as expected?) - probably due to a completely different query plan.

Shouldn't the result always be two null values?

The exact definition (slightly obfuscated):

CREATE TABLE IF NOT EXISTS raw
(
    reference             UUID         NOT NULL,
    window_start          timestamptz  NOT NULL,
    window_end            timestamptz  NOT NULL,
    c1                    INTEGER      NOT NULL,
    c2                    INTEGER      NOT NULL,
    last_updated          timestamptz  DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (reference, window_start)
);

SELECT create_hypertable('raw', by_range('window_start', INTERVAL '1 hour'), if_not_exists => TRUE);
SELECT add_retention_policy('raw', INTERVAL '1 days', if_not_exists => TRUE);

ALTER TABLE raw
    SET (timescaledb.compress, timescaledb.compress_segmentby = 'reference', timescaledb.compress_orderby = 'window_start');

CREATE MATERIALIZED VIEW IF NOT EXISTS agg5min
    WITH (timescaledb.continuous) AS
SELECT time_bucket('5 minutes', window_start)               AS min5_start,
       reference,
       SUM(c1)                                              AS c1,
       SUM(c2)                                              AS c2,
       MAX(last_updated)                                    AS last_updated
FROM raw
GROUP BY min5_start, reference;

SELECT add_continuous_aggregate_policy('agg5min',
                                       start_offset => INTERVAL '15 minutes',
                                       end_offset => INTERVAL '5 minutes',
                                       schedule_interval => INTERVAL '5 minutes');
SELECT add_retention_policy('agg5min', INTERVAL '1 month', if_not_exists => TRUE);

TimescaleDB version affected

2.15.0

PostgreSQL version used

16.3

What operating system did you use?

Debian GNU/Linux 11 (bullseye)

What installation method did you use?

Docker

What platform did you run on?

On prem/Self-hosted

Relevant log output and stack trace

How can we reproduce the bug?

Create an aggregate and try with a query over "out of time range" data, and with a query over "in time range" data (where still no rows satisfy the conditions).

[erimatnor]

@Petkomat Can you please provide a reproducible example, including sample data? Then provide both current output and exxpected output.