Thread: Strange round behaviour w/ more than 2 decimals

Strange round behaviour w/ more than 2 decimals

From
"Fernando Madruga Pinheiro"
Date:
Hi. I'm having some strange round behaviour when using 4 decimals.

If I use ROUND(1.8947, 2), it should return 1.90, but it does return 1.89. Rounding to 3 decimal places, then 2 (eg.: ROUND(ROUND(1.8947, 3), 2) ) then I get 1.90!
This way, when rounding, ROUND is considering only decimal places parameter + 1 to determine which is the rounding (ROUND(1.8951, 2) returns 1.90).

I've tested in 8.1.3 and 8.2.4.

Is it expected to work this way? There's a way to "force" the round to consider all the decimal places?

Thanks,
Fernando Pinheiro

Re: Strange round behaviour w/ more than 2 decimals

From
Peter Wiersig
Date:
On Wed, Jul 11, 2007 at 09:50:25AM -0300, Fernando Madruga Pinheiro wrote:
>  Hi. I'm having some strange round behaviour when using 4 decimals.
>
>  If I use ROUND(1.8947, 2), it should return 1.90, but it does return 1.89.

I asked my math teacher a similar question back in 7th grade:

If you round 1.8947 to 1.89 the error is 0.0047. If you round
1.8947 to 1.90 the error is 0.0053.

My numbers where 1.49 and the rounding I proposed was: round to 1
decimals, round to 0 decimals to get 2 out of 1.49. I think he is
correct and that the postgresql function is also correct.

Peter

Re: Strange round behaviour w/ more than 2 decimals

From
Tom Lane
Date:
"Fernando Madruga Pinheiro" <fernando.madrugapinheiro@gmail.com> writes:
> If I use ROUND(1.8947, 2), it should return 1.90, but it does return 1.89.

Why do you consider that wrong?  It's the closest approximation.
Do you know of any other software that would produce 1.90 from a
similar query?

> Rounding to 3 decimal places, then 2 (eg.: ROUND(ROUND(1.8947, 3), 2) ) then
> I get 1.90!

Since ROUND() is a lossy process by definition, you cannot expect
that different multi-step paths will always yield the same result.

            regards, tom lane

Re: Strange round behaviour w/ more than 2 decimals

From
"Fernando Madruga Pinheiro"
Date:
Hi Tom,

Sorry. After sending to group one friend of mine explained to me how round works -- not postgresql round, but round in math.
I do not know any software that would produce that output.

I thought that when 1.8947 was rounded to 2 decimal places, 7 would round-up 4, then "5" would round-up 9.

Reading http://www.ai.com.br/pessoal/indices/2A1A.HTM show the correct way (sorry, the page is in brazilian portuguese).

Anyway, the document shows: DO NOT DO RECURSIVE ROUNDS! hehe.

Thanks for the answer and sorry for the incorrect question.

Regards,
Fernando Pinheiro

2007/7/11, Tom Lane < tgl@sss.pgh.pa.us>:
"Fernando Madruga Pinheiro" < fernando.madrugapinheiro@gmail.com> writes:
> If I use ROUND(1.8947, 2), it should return 1.90, but it does return 1.89.

Why do you consider that wrong?  It's the closest approximation.
Do you know of any other software that would produce 1.90 from a
similar query?

> Rounding to 3 decimal places, then 2 (eg.: ROUND(ROUND(1.8947, 3), 2) ) then
> I get 1.90!

Since ROUND() is a lossy process by definition, you cannot expect
that different multi-step paths will always yield the same result.

                        regards, tom lane