Обсуждение: round() function wrong?

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

regards


jochen

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)

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

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 systems with IEEE 754 floating point, the default is
> normally to round to the nearest even number.

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 numericround
-------    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