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

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@comcast.net> wrote:

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

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@sss.pgh.pa.us> wrote: