Continuous aggregation calculation error

What type of bug is this?

Incorrect result

What subsystems and features are affected?

Continuous aggregate, Query executor

What happened?

There is a transformer and every 15 minutes the collector automatically records its readings. Its readings are downsampled to the maximum hourly, daily, monthly, and yearly, respectively. But it has an incredible number of data on continuous aggregation.

Step 1, the original table

select time, epad0cc
from transformer_meter2
where meter_code = 'METXJNG1BYQA4'
order by time desc limit 5;

source We see that the current maximum readings are: 6339985.166

STEP 2, CREATE HOURLY DOWNSAMPLING (HERE I USED THE TIMESCALEDB.MATERIALIZED_ONLY = FALSE PARAMETER)

CREATE MATERIALIZED VIEW TRANSFORMER_DOWNSAMPLING_HOUR
    WITH (TIMESCALEDB.CONTINUOUS,
        TIMESCALEDB.MATERIALIZED_ONLY = FALSE) AS
SELECT TIME_BUCKET('1 hour'::INTERVAL, "time") AS RECODE_TIME,
       METER_CODE,
       MAX(EPAD0CC)                            AS MAX_READING
FROM TRANSFORMER_METER2
GROUP BY TIME_BUCKET('1 hour'::INTERVAL, "time"), METER_CODE;

We see that its data is correct：6339985.166

Step 3, create a built-in daily downsampling (create a continuous aggregate on top of another continuous aggregate)

CREATE MATERIALIZED VIEW TRANSFORMER_DOWNSAMPLING_DAY
    WITH (TIMESCALEDB.CONTINUOUS,
        TIMESCALEDB.MATERIALIZED_ONLY = FALSE) AS
SELECT TIME_BUCKET('1 day'::INTERVAL, "recode_time") AS RECODE_TIME,
       METER_CODE,
       MAX(MAX_READING)                              AS MAX_READING
FROM TRANSFORMER_DOWNSAMPLING_HOUR
GROUP BY TIME_BUCKET('1 day'::INTERVAL, "recode_time"), METER_CODE;

Its data is also correct：6339985.166

Step 3, Create a Built-in Monthly Downsampling (Create a Continuous Aggregate on Top of Another Continuous Aggregate)

CREATE MATERIALIZED VIEW TRANSFORMER_DOWNSAMPLING_MTH
    WITH (TIMESCALEDB.CONTINUOUS,
        TIMESCALEDB.MATERIALIZED_ONLY = FALSE) AS
SELECT TIME_BUCKET('1 month'::INTERVAL, "recode_time") AS RECODE_TIME,
       METER_CODE,
       MAX(MAX_READING)                                AS MAX_READING
FROM TRANSFORMER_DOWNSAMPLING_DAY
GROUP BY TIME_BUCKET('1 month'::INTERVAL, "recode_time"), METER_CODE;

The data here confuses me：6329992.613。 I think it should be：6339985.166

Excuse me, what is the reason for this? How should it be resolved?

TimescaleDB version affected

2.15.0

PostgreSQL version used

PostgreSQL 16.3

What operating system did you use?

(Ubuntu 16.3-1.pgdg22.04+1

What installation method did you use?

Deb/Apt

What platform did you run on?

Other

Relevant log output and stack trace

No response

How can we reproduce the bug?

Here's the script：

-- hour
CREATE MATERIALIZED VIEW TRANSFORMER_DOWNSAMPLING_HOUR
    WITH (TIMESCALEDB.CONTINUOUS,
        TIMESCALEDB.MATERIALIZED_ONLY = FALSE) AS
SELECT TIME_BUCKET('1 hour'::INTERVAL, "time") AS RECODE_TIME,
       METER_CODE,
       MAX(EPAD0CC)                            AS MAX_READING
FROM TRANSFORMER_METER2
GROUP BY TIME_BUCKET('1 hour'::INTERVAL, "time"), METER_CODE;

-- day
CREATE MATERIALIZED VIEW TRANSFORMER_DOWNSAMPLING_DAY
    WITH (TIMESCALEDB.CONTINUOUS,
        TIMESCALEDB.MATERIALIZED_ONLY = FALSE) AS
SELECT TIME_BUCKET('1 day'::INTERVAL, "recode_time") AS RECODE_TIME,
       METER_CODE,
       MAX(MAX_READING)                              AS MAX_READING
FROM TRANSFORMER_DOWNSAMPLING_HOUR
GROUP BY TIME_BUCKET('1 day'::INTERVAL, "recode_time"), METER_CODE;

-- month
CREATE MATERIALIZED VIEW TRANSFORMER_DOWNSAMPLING_MTH
    WITH (TIMESCALEDB.CONTINUOUS,
        TIMESCALEDB.MATERIALIZED_ONLY = FALSE) AS
SELECT TIME_BUCKET('1 month'::INTERVAL, "recode_time") AS RECODE_TIME,
       METER_CODE,
       MAX(MAX_READING)                                AS MAX_READING
FROM TRANSFORMER_DOWNSAMPLING_DAY
GROUP BY TIME_BUCKET('1 month'::INTERVAL, "recode_time"), METER_CODE;

timescale / timescaledb