Thread: round behavior differs between 8.1.5 and 8.3.7

On Monday 20 April 2009 2:21:31 pm Robert Morton wrote:
> Howdy,
> None of the discussions about rounding so far have addressed what appears
> to be a significant change that occurred at some point between PostgreSQL
> v8.1.5 and v8.3.7.  Can someone explain to me the difference between the
> two resultsets below?  Additionally I would like to understand what option
> will consistently provide a banker's-round in v8.3.7, if possible.
>
> Here is the query, followed by the resultset for each version:
>  SELECT
>    round(3.5::numeric) as "rn3.5",
>    round(3.5::float8) as "rf3.5",
>    dround(3.5::numeric) as "dn3.5",
>    dround(3.5::float8) as "df3.5",
>    CAST(3.5::numeric as INTEGER) as "cn3.5",
>    CAST(3.5::float8 as INTEGER) as "cf3.5",
>    round(4.5::numeric) as "rn4.5",
>    round(4.5::float8) as "rf4.5",
>    dround(4.5::numeric) as "dn4.5",
>    dround(4.5::float8) as "df4.5",
>    CAST(4.5::numeric as INTEGER) as "cn4.5",
>    CAST(4.5::float8 as INTEGER) as "cf4.5"
>
> v8.1.5:
> rn3.5,rf3.5,dn3.5,df3.5,cn3.5,cf3.5,rn4.5,rf4.5,dn4.5,df4.5,cn4.5,cf4.5
> 4,4,4,4,4,4,5,4,4,4,5,4
> v8.3.7:
> rn3.5,rf3.5,dn3.5,df3.5,cn3.5,cf3.5,rn4.5,rf4.5,dn4.5,df4.5,cn4.5,cf4.5
> 4,3,3,3,4,3,5,4,4,4,5,4
>
>
> Thanks,
> Robert

Well it wasn't 8.3.5 :) because:

postgres=# SELECT version();
                                            version
------------------------------------------------------------------------------------------------
 PostgreSQL 8.3.5 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 4.2.4 (Ubuntu
4.2.4-1ubuntu3)
(1 row)


postgres=# SELECT
postgres-# round(3.5::numeric) as "rn3.5",
postgres-# round(3.5::float8) as "rf3.5",
postgres-# dround(3.5::numeric) as "dn3.5",
postgres-# dround(3.5::float8) as "df3.5",
postgres-# CAST(3.5::numeric as INTEGER) as "cn3.5",
postgres-# CAST(3.5::float8 as INTEGER) as "cf3.5",
postgres-# round(4.5::numeric) as "rn4.5",
postgres-# round(4.5::float8) as "rf4.5",
postgres-# dround(4.5::numeric) as "dn4.5",
postgres-# dround(4.5::float8) as "df4.5",
postgres-# CAST(4.5::numeric as INTEGER) as "cn4.5",
postgres-# CAST(4.5::float8 as INTEGER) as "cf4.5"
postgres-# ;
 rn3.5 | rf3.5 | dn3.5 | df3.5 | cn3.5 | cf3.5 | rn4.5 | rf4.5 | dn4.5 | df4.5 |
cn4.5 | cf4.5
-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------
     4 |     4 |     4 |     4 |     4 |     4 |     5 |     4 |     4 |     4 |
5 |     4
(1 row)




--
Adrian Klaver
aklaver@comcast.net

On Monday 20 April 2009 5:20:47 pm Adrian Klaver wrote:
> On Monday 20 April 2009 2:21:31 pm Robert Morton wrote:
> > Howdy,
> > None of the discussions about rounding so far have addressed what appears
> > to be a significant change that occurred at some point between PostgreSQL
> > v8.1.5 and v8.3.7.  Can someone explain to me the difference between the
> > two resultsets below?  Additionally I would like to understand what
> > option will consistently provide a banker's-round in v8.3.7, if possible.
> >
> > Here is the query, followed by the resultset for each version:
> >  SELECT
> >    round(3.5::numeric) as "rn3.5",
> >    round(3.5::float8) as "rf3.5",
> >    dround(3.5::numeric) as "dn3.5",
> >    dround(3.5::float8) as "df3.5",
> >    CAST(3.5::numeric as INTEGER) as "cn3.5",
> >    CAST(3.5::float8 as INTEGER) as "cf3.5",
> >    round(4.5::numeric) as "rn4.5",
> >    round(4.5::float8) as "rf4.5",
> >    dround(4.5::numeric) as "dn4.5",
> >    dround(4.5::float8) as "df4.5",
> >    CAST(4.5::numeric as INTEGER) as "cn4.5",
> >    CAST(4.5::float8 as INTEGER) as "cf4.5"
> >
> > v8.1.5:
> > rn3.5,rf3.5,dn3.5,df3.5,cn3.5,cf3.5,rn4.5,rf4.5,dn4.5,df4.5,cn4.5,cf4.5
> > 4,4,4,4,4,4,5,4,4,4,5,4
> > v8.3.7:
> > rn3.5,rf3.5,dn3.5,df3.5,cn3.5,cf3.5,rn4.5,rf4.5,dn4.5,df4.5,cn4.5,cf4.5
> > 4,3,3,3,4,3,5,4,4,4,5,4
> >
> >
> > Thanks,
> > Robert
>
> Well it wasn't 8.3.5 :) because:
>
> postgres=# SELECT version();
>                                             version
> ---------------------------------------------------------------------------
>--------------------- PostgreSQL 8.3.5 on i686-pc-linux-gnu, compiled by GCC
> gcc (GCC) 4.2.4 (Ubuntu 4.2.4-1ubuntu3)
> (1 row)
>
>
> postgres=# SELECT
> postgres-# round(3.5::numeric) as "rn3.5",
> postgres-# round(3.5::float8) as "rf3.5",
> postgres-# dround(3.5::numeric) as "dn3.5",
> postgres-# dround(3.5::float8) as "df3.5",
> postgres-# CAST(3.5::numeric as INTEGER) as "cn3.5",
> postgres-# CAST(3.5::float8 as INTEGER) as "cf3.5",
> postgres-# round(4.5::numeric) as "rn4.5",
> postgres-# round(4.5::float8) as "rf4.5",
> postgres-# dround(4.5::numeric) as "dn4.5",
> postgres-# dround(4.5::float8) as "df4.5",
> postgres-# CAST(4.5::numeric as INTEGER) as "cn4.5",
> postgres-# CAST(4.5::float8 as INTEGER) as "cf4.5"
> postgres-# ;
>  rn3.5 | rf3.5 | dn3.5 | df3.5 | cn3.5 | cf3.5 | rn4.5 | rf4.5 | dn4.5 |
> df4.5 | cn4.5 | cf4.5
> -------+-------+-------+-------+-------+-------+-------+-------+-------+---
>----+-------+------- 4 |     4 |     4 |     4 |     4 |     4 |     5 |
> 4 |     4 |     4 | 5 |     4
> (1 row)
>
>
>
>
> --
> Adrian Klaver
> aklaver@comcast.net


I upgraded to 8.3.7 and I still don't see what you see. There must be something
else going here.

postgres=# SELECT version();
                                            version
------------------------------------------------------------------------------------------------
 PostgreSQL 8.3.7 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 4.2.4 (Ubuntu
4.2.4-1ubuntu3)
(1 row)

postgres=# SELECT
round(3.5::numeric) as "rn3.5",
round(3.5::float8) as "rf3.5",
dround(3.5::numeric) as "dn3.5",
dround(3.5::float8) as "df3.5",
CAST(3.5::numeric as INTEGER) as "cn3.5",
CAST(3.5::float8 as INTEGER) as "cf3.5",
round(4.5::numeric) as "rn4.5",
round(4.5::float8) as "rf4.5",
dround(4.5::numeric) as "dn4.5",
dround(4.5::float8) as "df4.5",
CAST(4.5::numeric as INTEGER) as "cn4.5",
CAST(4.5::float8 as INTEGER) as "cf4.5"
;
 rn3.5 | rf3.5 | dn3.5 | df3.5 | cn3.5 | cf3.5 | rn4.5 | rf4.5 | dn4.5 | df4.5 |
cn4.5 | cf4.5
-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------
     4 |     4 |     4 |     4 |     4 |     4 |     5 |     4 |     4 |     4 |
5 |     4
(1 row)


--
Adrian Klaver
aklaver@comcast.net

Robert Morton <morton2002@gmail.com> writes:
> Perhaps there are platform differences, since the version I am using was
> built with Microsoft Visual Studio:

Ah, now you tell us.

round(float8) just calls the platform's rint() function.  At least on
platforms that have rint(), which maybe Windows doesn't.  In that case
it's going to come down to src/port/rint.c, which is not particularly
careful about the exactly-0.5 case.  I think fully-standards-conformant
versions of rint() are probably going to use a "round to nearest even
integer" rule in such cases.  But by and large, float8 arithmetic *is*
going to have platform-specific behaviors; you're living in a fantasy
world if you think otherwise.

            regards, tom lane