time_bucket_gapfill in select from continuous aggregate

[cracksalad]

Relevant system information:

• OS: CentOS 8
• PostgreSQL version: 13.2
• TimescaleDB version: 2.2.1
• Installation method: dnf install

Context
Assume there is a continuous aggregate view like this:

CREATE MATERIALIZED VIEW "measurement_hourly" ("timestamp", "sensor_uuid", "avg", "sum", "min", "max", "count") 
    WITH (timescaledb.continuous) AS 
        SELECT time_bucket('1 hour', "timestamp") AS "hourly", "sensor_uuid", AVG("value"), SUM("value"), MIN("value"), MAX("value"), COUNT("value") 
            FROM "measurement" GROUP BY hourly, sensor_uuid 
    WITH DATA;

Now I want to do a time_bucket_gapfill statement on it like this:

SELECT time_bucket_gapfill('1 hour', "timestamp") AS "hourly", "avg" 
  FROM "measurement_hourly" 
  WHERE "sensor_uuid"=123 AND "timestamp">=now() - interval '1 month' 
  ORDER BY "timestamp" ASC;

Because some sensors measure only once a day. So I really want to add interpolate but therefore I need a time_bucket_gapfill first.

Expected behavior

hourly	avg
2021-04-07 11:00:00	2750
2021-04-07 12:00:00
2021-04-07 13:00:00
2021-04-07 14:00:00
2021-04-07 15:00:00
2021-04-07 16:00:00
...	...

Actual behavior

hourly	avg
2021-04-07 11:00:00	2750
2021-04-08 11:00:00	2760
2021-04-09 11:00:00	2740
2021-04-10 11:00:00	2730
2021-04-11 11:00:00	2770
2021-04-12 11:00:00	2760
...	...

Additional information

When I add the interpolate aggregation I get the same result.

[cracksalad]

If you read this comment on another issue (#1324)
or this note in the docs:

time_bucket_gapfill is not allowed in continuous aggs, but may be run in a SELECT from the continuous aggregate view.

(see docs about continuous aggregates)

you probably think that the problem is either an edge case or a bug, right?

[cracksalad]

I found the source of the problem: I forgot to add the GROUP BY-clause because logically I do not want to group the result since there is already at most one value per hour due to the view definition. When I add the GROUP BY, I also have to add an AVG aggregate function to the "avg" since it is of course not part of the GROUP BY statement. Then I am able to add the interpolate-aggregate, too.

The final query looks like this:

SELECT time_bucket_gapfill('1 hour', "timestamp") AS "hourly", interpolate(AVG("avg")) AS "value", "sensor_uuid" 
  FROM "measurement_hourly" 
  WHERE "sensor_uuid"=1 AND "timestamp">=now() - interval '1 week' AND "timestamp"<=now() 
  GROUP BY hourly, sensor_uuid 
  ORDER BY "hourly" ASC;

So it is not very intuitive and might be optimized but I guess, this issue should be closed.