pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
ROUND function ??
4+ messages / 4 participants
[nested] [flat]

* ROUND function ??
@ 2001-10-09 03:45 Saurabh Mittal <lattim@hotmail.com>
  2001-10-09 04:42 ` Re: ROUND function ?? Tom Lane <tgl@sss.pgh.pa.us>
  2001-10-09 05:03 ` Re: ROUND function ?? Joe Conway <joseph.conway@home.com>
  0 siblings, 2 replies; 4+ messages in thread

From: Saurabh Mittal @ 2001-10-09 03:45 UTC (permalink / raw)
  To: pgsql-sql

Hi, 

select ROUND(0.5) ; returns 0
select ROUND(1.5) ; returns 2;
select ROUND(2.5) ; returns 2;
select ROUND(3.5) ; returns 4;....so on . 
I'm sure you would have figured out what's happening !!! Why ??
How do I get to approximate any number x.5 as x+1 ??

Saurabh
 

^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: ROUND function ??
  2001-10-09 03:45 ROUND function ?? Saurabh Mittal <lattim@hotmail.com>
@ 2001-10-09 04:42 ` Tom Lane <tgl@sss.pgh.pa.us>
  2001-10-09 23:16   ` Re: ROUND function ?? Allan Engelhardt <allane@cybaea.com>
  1 sibling, 1 reply; 4+ messages in thread

From: Tom Lane @ 2001-10-09 04:42 UTC (permalink / raw)
  To: Saurabh Mittal <lattim@hotmail.com>; +Cc: pgsql-sql

"Saurabh Mittal" <lattim@hotmail.com> writes:
> select ROUND(0.5) ; returns 0
> select ROUND(1.5) ; returns 2;
> select ROUND(2.5) ; returns 2;
> select ROUND(3.5) ; returns 4;....so on .=20
> I'm sure you would have figured out what's happening !!! Why ??

Because the IEEE float math standard says so.  Round-to-nearest-even
is considered good practice.

> How do I get to approximate any number x.5 as x+1 ??

Try FLOOR(x + 0.5) if you really want the other behavior.

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: ROUND function ??
  2001-10-09 03:45 ROUND function ?? Saurabh Mittal <lattim@hotmail.com>
  2001-10-09 04:42 ` Re: ROUND function ?? Tom Lane <tgl@sss.pgh.pa.us>
@ 2001-10-09 23:16   ` Allan Engelhardt <allane@cybaea.com>
  0 siblings, 0 replies; 4+ messages in thread

From: Allan Engelhardt @ 2001-10-09 23:16 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; lattim@hotmail.com

Tom Lane wrote:

> [...]Because the IEEE float math standard says so.  Round-to-nearest-even
> is considered good practice.

I learn something new every day.  :-)

While it is true that IEEE 754 defaults to "round-to-nearest", which means rounding midway points to even, it is slightly confusing that this is implemented in the math library (libc) by the  rint  function (subject to compiler defaults and  fesetround  calls) while the  round  functions ((ll|l)?round(f|l)?) of the library does indeed 'round half-way cases away from zero to the nearest integer', which is also the mathematical behavior.  Try

    % info libc Arithmetic Rounding
    % info libc Arithmetic 'Arithmetic Functions' 'Rounding Functions'

on you local friendly U*ix clone and you shall find enlightenment...


The SQL standard seems to leave it implementation dependent ('92, sec 4.4.1):


         An approximation obtained by rounding of a numerical value N for
         an <exact numeric type> T is a value V representable in T such
         that the absolute value of the difference between N and the nu-
         merical value of V is not greater than half the absolute value
         of the difference between two successive numerical values repre-
         sentable in T. If there are more than one such values V, then it is
         implementation-defined which one is taken.


So PostgreSQL does "the right thing" (again!).


Allan.



^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: ROUND function ??
  2001-10-09 03:45 ROUND function ?? Saurabh Mittal <lattim@hotmail.com>
@ 2001-10-09 05:03 ` Joe Conway <joseph.conway@home.com>
  1 sibling, 0 replies; 4+ messages in thread

From: Joe Conway @ 2001-10-09 05:03 UTC (permalink / raw)
  To: Saurabh Mittal <lattim@hotmail.com>; pgsql-sql

> select ROUND(0.5) ; returns 0
> select ROUND(1.5) ; returns 2;
> select ROUND(2.5) ; returns 2;
> select ROUND(3.5) ; returns 4;....so on .
> I'm sure you would have figured out what's happening !!! Why ??
> How do I get to approximate any number x.5 as x+1 ??

Looks like a bug to me:

test=# select * from pg_proc where proname = 'round';
 proname | proowner | prolang | proisinh | proistrusted | proiscachable |
proisstrict | pronargs | proretset | prorettype | proargtypes | probyte_pct
| properbyte_cpu | propercall_cpu | prooutin_ratio |       prosrc       |
probin
---------+----------+---------+----------+--------------+---------------+---
----------+----------+-----------+------------+-------------+-------------+-
---------------+----------------+----------------+--------------------+-----
---
 round   |        1 |      12 | f        | t            | t             | t
|        1 | f         |        701 |         701 |         100 |
0 |              0 |            100 | dround             | -
 round   |        1 |      14 | f        | t            | t             | t
|        1 | f         |       1700 |        1700 |         100 |
0 |              0 |            100 | select round($1,0) | -
 round   |        1 |      12 | f        | t            | t             | t
|        2 | f         |       1700 |     1700 23 |         100 |
0 |              0 |            100 | numeric_round      | -
(3 rows)

test=# select round(2.5);
 round
-------
     2
(1 row)

test=# select round(2.5,0);
 round
-------
     3
(1 row)

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

When doing "select round(2.5)" the 2.5 gets cast as a float and the "dround"
function is used. When doing "select round(2.5,0)", or  "select
round(2.5::numeric)", the 2.5 gets cast as a numeric and the function
"numeric_round" is used, producing a different result. It looks like
"dround" simply calls the rint system function, so I'd guess the issue is
really there (and maybe platform dependent?). I do recall at least one
interpretation of rounding that calls for rounding a 5 to the even digit
(ASTM), so the rint behavior may not be strictly speaking a bug -- but
certainly having two different interpretations is.

In any case, use "select round(2.5,0)" for now.

Hope this helps,

Joe





^ permalink  raw  reply  [nested|flat] 4+ messages in thread


end of thread, other threads:[~2001-10-09 23:16 UTC | newest]

Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-10-09 03:45 ROUND function ?? Saurabh Mittal <lattim@hotmail.com>
2001-10-09 04:42 ` Tom Lane <tgl@sss.pgh.pa.us>
2001-10-09 23:16   ` Allan Engelhardt <allane@cybaea.com>
2001-10-09 05:03 ` Joe Conway <joseph.conway@home.com>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox