pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Joe Conway <joseph.conway@home.com>
To: Saurabh Mittal <lattim@hotmail.com>
To: pgsql-sql@postgresql.org
Subject: Re: ROUND function ??
Date: Mon, 8 Oct 2001 22:03:50 -0700
Message-ID: <003a01c1507f$c99cf520$0205a8c0@jecw2k1> (raw)
References: <OE25NZwwPcMFlF8HGmC0000b0ea@hotmail.com>

> 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





view thread (4+ messages)  latest in thread

Message-ID: <003a01c1507f$c99cf520$0205a8c0@jecw2k1>
Permalink:  ../003a01c1507f$c99cf520$0205a8c0@jecw2k1/
Also on:    postgresql.org/message-id/003a01c1507f$c99cf520$0205a8c0@jecw2k1

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-sql@postgresql.org
  Cc: joseph.conway@home.com, lattim@hotmail.com
  Subject: Re: ROUND function ??
  In-Reply-To: <003a01c1507f$c99cf520$0205a8c0@jecw2k1>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

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