pg.ddx.io  pgsql-bugs@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Precision/scale of a numeric attribute of a new data type are not handled correctly when the type is returned by a function
2+ messages / 2 participants
[nested] [flat]

* Precision/scale of a numeric attribute of a new data type are not handled correctly when the type is returned by a function
@ 2019-11-24 14:10 Petr Fedorov <petr.fedorov@phystech.edu>
  2019-11-24 23:45 ` Re: Precision/scale of a numeric attribute of a new data type are not handled correctly when the type is returned by a function Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 1 reply; 2+ messages in thread

From: Petr Fedorov @ 2019-11-24 14:10 UTC (permalink / raw)
  To: pgsql-bugs

Hello,

Steps to reproduce:


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


select value1::numeric(35,6), value2::numeric(35,2) from test();

Expected:  7.136178 7.14

Actual: 7.136178319899999964 7.14


I'm on Centos 7,  Postgresql 11.6



   







^ permalink  raw  reply  [nested|flat] 2+ messages in thread

* Re: Precision/scale of a numeric attribute of a new data type are not handled correctly when the type is returned by a function
  2019-11-24 14:10 Precision/scale of a numeric attribute of a new data type are not handled correctly when the type is returned by a function Petr Fedorov <petr.fedorov@phystech.edu>
@ 2019-11-24 23:45 ` Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 0 replies; 2+ messages in thread

From: Tom Lane @ 2019-11-24 23:45 UTC (permalink / raw)
  To: Petr Fedorov <petr.fedorov@phystech.edu>; +Cc: 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





^ permalink  raw  reply  [nested|flat] 2+ messages in thread


end of thread, other threads:[~2019-11-24 23:45 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2019-11-24 14:10 Precision/scale of a numeric attribute of a new data type are not handled correctly when the type is returned by a function Petr Fedorov <petr.fedorov@phystech.edu>
2019-11-24 23:45 ` Tom Lane <tgl@sss.pgh.pa.us>

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