Re: round behavior differs between 8.1.5 and 8.3.7

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
>