Postgres Pro
Downloads
Home   >   mailing lists

Thread: Float/Double cast to int

Float/Double cast to int

From
Feng Tian
Date:
Hi, Hackers,

Here is a query, server was built witch GCC on Linux, AMD64.


ftian=#
ftian=# select 1.5::int, 1.5::double precision::int, 314.5::int, 314.5::double precision::int;
 int4 | int4 | int4 | int4
------+------+------+------
    2 |    2 |  315 |  314
(1 row)


I believe this is because rint is broken -- can some expert on IEEE754 please help confirm that this is a bug?

Thanks,
Feng

Re: Float/Double cast to int

From
Tom Lane
Date:
Feng Tian <ftian@vitessedata.com> writes:
> Here is a query, server was built witch GCC on Linux, AMD64.

> ftian=# select 1.5::int, 1.5::double precision::int, 314.5::int,
> 314.5::double precision::int;
>  int4 | int4 | int4 | int4
> ------+------+------+------
>     2 |    2 |  315 |  314
> (1 row)

> I believe this is because rint is broken -- can some expert on IEEE754
> please help confirm that this is a bug?

rint() is doing what the IEEE spec says, ie round to nearest even.
Your third expression is doing numeric-to-int, and that code doesn't
obey the IEEE spec.  We've had discussions (not too long ago) about
making these behaviors more consistent, but people seem to be too
afraid of backwards-compatibility problems if we change it.
        regards, tom lane

Re: Float/Double cast to int

From
Feng Tian
Date:
Ah, thanks!  I did not realize numeric comes into play.   But, this is even more interesting -- I would expect numeric is more consistent than float/double when dealing with stuff like rounding.   

I missed the not too long ago discussion, :-)   Regardless of the mechanisms underneath, it would be quite hard to explain this behavior to customer.  Maybe it is time to be brave, and be compatible with reality instead of backward?

Best,
Feng




On Thu, May 21, 2015 at 12:01 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Feng Tian <ftian@vitessedata.com> writes:
> Here is a query, server was built witch GCC on Linux, AMD64.

> ftian=# select 1.5::int, 1.5::double precision::int, 314.5::int,
> 314.5::double precision::int;
>  int4 | int4 | int4 | int4
> ------+------+------+------
>     2 |    2 |  315 |  314
> (1 row)

> I believe this is because rint is broken -- can some expert on IEEE754
> please help confirm that this is a bug?

rint() is doing what the IEEE spec says, ie round to nearest even.
Your third expression is doing numeric-to-int, and that code doesn't
obey the IEEE spec.  We've had discussions (not too long ago) about
making these behaviors more consistent, but people seem to be too
afraid of backwards-compatibility problems if we change it.

                        regards, tom lane

Re: Float/Double cast to int

From
Michael Paquier
Date:
On Fri, May 22, 2015 at 4:33 AM, Feng Tian <ftian@vitessedata.com> wrote:
> Ah, thanks!  I did not realize numeric comes into play.   But, this is even
> more interesting -- I would expect numeric is more consistent than
> float/double when dealing with stuff like rounding.
>
> I missed the not too long ago discussion, :-)   Regardless of the mechanisms
> underneath, it would be quite hard to explain this behavior to customer.
> Maybe it is time to be brave, and be compatible with reality instead of
> backward?

Here is some food for thought:
- The original thread of pgsql-bug where this has been discussed first

http://www.postgresql.org/message-id/flat/20150320194337.2573.72944@wrigleys.postgresql.org#20150320194337.2573.72944@wrigleys.postgresql.org
- Another thread to document this behavior more appropriately (patch pending)
http://www.postgresql.org/message-id/CAB7nPqRVCbvYAWL++vCfyzOUujEay21bcLbBk_Mb=+2JX7+e5A@mail.gmail.com
Regards,
--
Michael