round() function wrong?

On Wed, 22 Oct 2003, Jochen Westland [invigo] wrote:

Hi All,
i'm running Postgresql 2.2x, so i am not quitse sure wether the bug i am reporting is already fixed
in newer versions or not.

In my version
select round(2.5); returns 2;
select round(2.5000001) returns 3;

refering to my math professor thats wrong, at least in germany.
select round(2.5); should return 3

I just tried that on my 7.2.4 and 7.4 beta 4 machines and I get 2 for
round(2.5)

But this seems to work correctly on 7.3.2 and 7.3.4:
psql -c "select round (2.5)"
Password:
round
-------
3
(1 row)

=============

On Fri, 2003-10-24 at 13:53, scott.marlowe wrote:

On Wed, 22 Oct 2003, Jochen Westland [invigo] wrote:

Hi All,
i'm running Postgresql 2.2x, so i am not quitse sure wether the bug i am reporting is already fixed
in newer versions or not.

In my version
select round(2.5); returns 2;
select round(2.5000001) returns 3;

refering to my math professor thats wrong, at least in germany.
select round(2.5); should return 3

I just tried that on my 7.2.4 and 7.4 beta 4 machines and I get 2 for
round(2.5)

architecture dependent?

qqq74=# select round(2.5), version();
round | version
-------+----------------------------------------------------------------
3 | PostgreSQL 7.4beta4 on i686-pc-linux-gnu, compiled by GCC 2.96
(1 row)

qqq74=# \q
[rob@camel bin]$ uname -a
Linux camel 2.4.20-20.7 #1 Mon Aug 18 15:00:59 EDT 2003 i686 unknown
[rob@camel bin]$

Robert Treat
--
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL

Jochen Westland [invigo] writes:

In my version
select round(2.5); returns 2;
select round(2.5000001) returns 3;

refering to my math professor thats wrong, at least in germany.
select round(2.5); should return 3

The convention that .5 values should be rounded up is just that, a
convention. On systems with IEEE 754 floating point, the default is
normally to round to the nearest even number.

--
Peter Eisentraut peter_e@gmx.net

Peter Eisentraut kirjutas R, 24.10.2003 kell 22:16:

Jochen Westland [invigo] writes:

In my version
select round(2.5); returns 2;
select round(2.5000001) returns 3;

refering to my math professor thats wrong, at least in germany.
select round(2.5); should return 3

The convention that .5 values should be rounded up is just that, a
convention.

Also, which way is up ?

hannu=# select round(0.5);
round
-------
1
(1 row)

hannu=# select round(-0.5);
round
-------
-1
(1 row)

On Fri, 24 Oct 2003, Michael Brusser wrote:

But this seems to work correctly on 7.3.2 and 7.3.4:
psql -c "select round (2.5)"
Password:
round
-------
3
(1 row)

=============

I just tried that on my 7.2.4 and 7.4 beta 4 machines and I get 2 for
round(2.5)

Ackkk. I accidentally tested it on my 7.3.4 box, not my 7.4 beta 4 box.
but the output is the same. The original, I believe, had '' marks in it.

Anyway, it seems to matter about the ''s in 7.3.x:

In pgsql 7.2:

select round(2.5::float);
round
-------
2
(1 row)

select round(2.5::numeric);
round
-------
3
(1 row)

select round(2.5); <-- would appear to be coerced to float here)
round
-------
2
(1 row)

select round('2.5');
ERROR: Function 'round(unknown)' does not exist
Unable to identify a function that satisfies the given argument
types
You may need to add explicit typecasts

pgsql 7.3.4:

select round(2.5::float);
round
-------
2
(1 row)

select round(2.5::numeric);
round
-------
3
(1 row)

select round(2.5); <-- would appear to be coerced to numeric
round
-------
3
(1 row)

select round('2.5');
round
-------
2
(1 row)

pgsql 7.4 beta5: behaves the same as 7.3.4

So it would appear to be that the automatic assumptions about what is
float and what is numeric changed from 7.2 to 7.3, i.e. it's assumed that
numeric is the input type.

But I'm just guessing here.

refering to my math professor thats wrong, at least in germany.
select round(2.5); should return 3

Well, I thought mathematics theory says that you should round to the
nearest even number for a 0.5 value, so as to avoid biasing your data...?

I just tried that on my 7.2.4 and 7.4 beta 4 machines and I get 2 for
round(2.5)

What does 3.5 do?

And -2.5 and -3.5?

Chri

"scott.marlowe" <scott.marlowe@ihs.com> writes:

So it would appear to be that the automatic assumptions about what is
float and what is numeric changed from 7.2 to 7.3, i.e. it's assumed that
numeric is the input type.

That's correct.

Looking at the code, round(numeric) always rounds xxx.5 values away from
zero (0.5 -> 1, -0.5 -> -1, etc). The behavior of round(float) is
platform-dependent, but round-to-nearest-even is the rule used by IEEE
compliant platforms.

regards, tom lane