Postgres Pro
Downloads
Home   >   mailing lists

Thread: round() function wrong?

round() function wrong?

From
"Jochen Westland [invigo]"
Date:
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

Re: round() function wrong?

From
"scott.marlowe"
Date:
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)

Re: round() function wrong?

From
Michael Brusser
Date:
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)
> 
>

Re: round() function wrong?

From
Robert Treat
Date:
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

Re: round() function wrong?

From
Peter Eisentraut
Date:
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

Re: round() function wrong?

From
Hannu Krosing
Date:
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.

Re: round() function wrong?

From
"scott.marlowe"
Date:
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.

Re: round() function wrong?

From
Christopher Kings-Lynne
Date:
>>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

Re: round() function wrong?

From
Tom Lane
Date:
"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