round behavior differs between 8.1.5 and 8.3.7

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

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(at)comcast(dot)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(at)comcast(dot)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(at)comcast(dot)net

Perhaps there are platform differences, since the version I am using was
built with Microsoft Visual Studio:

SELECT version()
PostgreSQL 8.3.7, compiled by Visual C++ build 1400
The v8.1.5 server I'm using was compiled with GCC:
PostgreSQL 8.1.5 on i686-pc-mingw32, compiled by GCC gcc.exe (GCC) 3.4.2
(mingw-special)

I would like to understand how this difference affects rounding in more
detail so that I may explain caveats to customers.

Thanks,
Robert

On Mon, Apr 20, 2009 at 5:36 PM, Adrian Klaver <aklaver(at)comcast(dot)net> wrote:

> 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(at)comcast(dot)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(at)comcast(dot)net
>

Robert Morton <morton2002(at)gmail(dot)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

Thanks for the explanation, that's exactly the level of detail I need.
-Robert

On Tue, Apr 21, 2009 at 12:00 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Robert Morton <morton2002(at)gmail(dot)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
>