[Bug]: Numeric type widening in hyperfunctions

[jgiacobbi]

What type of bug is this?

Incorrect result

What subsystems and features are affected?

Gapfill

What happened?

Writing hyperfunctions is cumbersome, so I am working on some helper functions for less technical team members. In particular we need to forward fill and gap fill. However since plpgsql is statically typed the query must return the table type. It's a lot of code but a pseudocode summary would be something like:

create function forward_fill(tbl anyelement, end timestamptz)
returns setof anyelement as $$
declare midnight := datefunc(end)
begin
    return query execute "
    select 
        end as time, 
        last(col1, time), 
        last(col2, time)
     from pg_typeof(tbl)::text
     where time in tstzrange(midnight, end)
    "
end;
$$ language plpgsql;

While using last and locf I got the following error message:

ERROR:  structure of query does not match function result type
DETAIL:  Returned type numeric does not match expected type numeric(8,3) in column 3.

I was able to work around this for last queries as follows:

        CASE
            WHEN data_type = 'numeric' AND numeric_precision is not null AND numeric_scale > 0 THEN
                format('(last(%1$s, %2$s) filter(where %1$s is not null))::numeric(%3$s, %4$s) as %1$s',
                column_name, time_index, numeric_precision, numeric_scale)
            WHEN data_type = 'numeric' AND numeric_precision is not null AND numeric_scale = 0 THEN
                format('(last(%1$s, %2$s) filter(where %1$s is not null))::numeric(%3$s) as %1$s',
                column_name, time_index, numeric_precision)
            ELSE
                format('last(%1$s, %2$s) filter(where %1$s is not null) as %1$s', column_name, time_index)
        END as clauses

However this doesn't work for gapfilling because you can't cast a gapfill result directly in the query.

I can find a workaround, but I don't really want to cast a gapfill result, I'm just trying to get the type that the column was in the first place.

TimescaleDB version affected

2.17.2

PostgreSQL version used

17.2

What operating system did you use?

Ubuntu 22.04 arm

What installation method did you use?

Deb/Apt

What platform did you run on?

On prem/Self-hosted

Relevant log output and stack trace

select * from gap_fill(NULL::my_table, '2019-12-02 15:00:00+00', 10201224);
NOTICE:  Time index: time
NOTICE:  Primary key column: <NULL>
NOTICE:  Index column: my_id
NOTICE:  Generated
    SELECT
        time_bucket_gapfill('5 min', time) as time,
        my_id,
        locf(min(data_col), treat_null_as_missing => true) as data_col,
    FROM
        my_table
    WHERE
        time <@ '["2019-12-02 05:00:00+00","2019-12-02 15:00:00+00"]'::tstzrange
        AND my_id = '10201224'
    GROUP BY
        time_bucket_gapfill('5 min', time), my_id

ERROR:  structure of query does not match function result type
DETAIL:  Returned type numeric does not match expected type numeric(8,3) in column 3.
CONTEXT:  SQL statement "
    SELECT
        time_bucket_gapfill('5 min', time) as time,
        my_id,
        locf(min(data_col), treat_null_as_missing => true) as data_col,
    FROM
        my_table
    WHERE
        time <@ '["2019-12-02 05:00:00+00","2019-12-02 15:00:00+00"]'::tstzrange
        AND my_id = '10201224'
    GROUP BY
        time_bucket_gapfill('5 min', time), my_id
"
PL/pgSQL function gap_fill(anyelement,timestamp with time zone,integer) line 3 at RETURN QUERY

How can we reproduce the bug?

Create a table with a column that has a bound numeric type (ex `numeric(10,5)`, not just `numeric`)

Create a plpgsql function with a return type of that table.

Execute a select against that table applying `locf` or `last` to the data and return the result.