Re: Precision/scale of a numeric attribute of a new data type are not handled correctly when the type is returned by a function - Mailing list pgsql-bugs

From Tom Lane
Subject Re: Precision/scale of a numeric attribute of a new data type are not handled correctly when the type is returned by a function
Date
Msg-id 14300.1574639128@sss.pgh.pa.us
Whole thread Raw
In response to Precision/scale of a numeric attribute of a new data type are nothandled correctly when the type is returned by a function  (Petr Fedorov <petr.fedorov@phystech.edu>)
List pgsql-bugs
Petr Fedorov <petr.fedorov@phystech.edu> writes:
> create type public.test_type as (  value1 numeric(35,6),     value2
> numeric(35,6) );

> create or replace function public.test(    )    returns test_type   
> language  'sql'  as $body$   select 7.136178319899999964,
> 7.136178319899999964;  $body$;

> select value1, value2 from test();
> Expected:  7.136178 7.136178
> Actual:  7.136178319899999964 7.136178319899999964

Hm.  In general, this isn't inconsistent with the rule that the
output of a function doesn't have any particular typmod
(unless it's a length-coercion function).  However, it's certainly
not good that you then get

> select value1::numeric(35,6), value2::numeric(35,2) from test();
> Expected:  7.136178 7.14
> Actual: 7.136178319899999964 7.14

showing that some part of the system does believe that the output
columns have a particular typmod, and hence don't need further
coercion.

Blame for this might be laid on check_sql_fn_retval(), which
pays no particular attention to typmods.  However, I don't think
it'd be reasonable to fix it by just extending that function's
existing behavior to insist on typmod as well as type match.
That would cause this example to throw an error until you put
explicit coercions onto the constants; which I bet nobody would
love us for.

A better idea, perhaps, is to get rid of the insistence that
the outputs of a SQL function be exactly of the target type
(modulo binary compatibility, which is a concept that has no
place in user-visible semantics anyway).  If we had the ability
to insert coercion functions, we could allow any case where
there's an implicit (or, perhaps, assignment) coercion defined,
including applying a length coercion if needed.

This seems like it'd require some nontrivial surgery in
functions.c, though.  The code path wherein check_sql_fn_retval()
is allowed to modify the tlist wouldn't be so hard to fix, but
that only applies for inlining transformations.  The main SQL
function execution engine isn't prepared to do anything smarter
than applying a junkfilter to the query outputs, and we'd have
to change that.  Maybe drop the junkfilter bit entirely in favor
of plastering another projection step atop the finished plan?

            regards, tom lane



pgsql-bugs by date:

Previous
From: Tomas Vondra
Date:
Subject: Re: Failed assertion clauses != NIL
Next
From: Michael Paquier
Date:
Subject: Re: BUG #16132: PostgreSQL 12.1 and PLV8 2.3.13 => PostgreSQL crashes