From: Tom Lane <tgl@sss.pgh.pa.us>
To: Petr Fedorov <petr.fedorov@phystech.edu>
Cc: pgsql-bugs@postgresql.org
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: Sun, 24 Nov 2019 18:45:28 -0500
Message-ID: <14300.1574639128@sss.pgh.pa.us> (raw)
In-Reply-To: <b2dbfbd0-8f12-4cca-a0b5-9ee3f00385b4@phystech.edu>
References: <b2dbfbd0-8f12-4cca-a0b5-9ee3f00385b4@phystech.edu>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-bugs@postgresql.org
Cc: tgl@sss.pgh.pa.us, petr.fedorov@phystech.edu
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
In-Reply-To: <14300.1574639128@sss.pgh.pa.us>
References: <b2dbfbd0-8f12-4cca-a0b5-9ee3f00385b4@phystech.edu> <14300.1574639128@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox