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


Wed, 22 Oct 2003 11:47:02 +0200
"Jochen Westland [invigo]" <jochen.westland@invigo.de>
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)


Fri, 24 Oct 2003 11:53:22 -0600 (MDT)
"scott.marlowe" <scott.marlowe@ihs.com>
Re: round() function wrong?
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)
> 
> 



Fri, 24 Oct 2003 14:29:19 -0400
Michael Brusser <michael@synchronicity.com>
Re: round() function wrong?
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


24 Oct 2003 15:07:41 -0400
Robert Treat <xzilla@users.sourceforge.net>
Re: round() function wrong?
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


Fri, 24 Oct 2003 21:16:34 +0200 (CEST)
Peter Eisentraut <peter_e@gmx.net>
Re: round() function wrong?
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.

Fri, 24 Oct 2003 22:26:21 +0300
Hannu Krosing <hannu@tm.ee>
Re: round() function wrong?
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.




Fri, 24 Oct 2003 14:50:51 -0600 (MDT)
"scott.marlowe" <scott.marlowe@ihs.com>
Re: round() function wrong?

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



Sun, 26 Oct 2003 16:19:16 +0800
Christopher Kings-Lynne <chriskl@familyhealth.com.au>
Re: round() function wrong?
"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

Mon, 27 Oct 2003 17:53:04 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: round() function wrong? 