List:General Discussion« Previous MessageNext Message »
From:Gordon Date:February 23 2005 4:02pm
Subject:RE: Odd rounding errors with 4.1
View as plain text  
This reminded me of one more difference between Windows and Linux/Unix.
MySQL use the round function out of the host libraries. If you are on a
Windows box the rule for rounding is if the column immediately to the right
of the column you are rounding to is a 5 then round up 
   i.e. make 2.485 >>> 2.49
        make 2.595 >>> 2.60
If you are on a Linux/Unix box the rule for rounding is if the column
immediately to the right of the column you are rounding to is a 5 then round
up if the column you are rounding to is odd and round down if the column you
are rounding to is even
   i.e. make 2.485 >>> 2.49
        make 2.595 >>> 2.59

	Windows	Linux/Unix
2.4850	2.49	2.48
2.5950	2.60	2.60
2.7700	2.77	2.77
7.8500	7.86	7.85

This was run on a RedHat server

mysql> select round(2.4850,2), round(2.5950,2), round(2.7700,2),
round(2.4850,2)+round(2.5950,2)+round(2.7700,2);
+-----------------+-----------------+-----------------+---------------------
----------------------------+
| round(2.4850,2) | round(2.5950,2) | round(2.7700,2) |
round(2.4850,2)+round(2.5950,2)+round(2.7700,2) |
+-----------------+-----------------+-----------------+---------------------
----------------------------+
|            2.48 |            2.60 |            2.77 |
7.85 |
+-----------------+-----------------+-----------------+---------------------
----------------------------+
1 row in set (0.00 sec)

-----Original Message-----
From: Martin [mailto:martin.y@stripped] 
Sent: Monday, February 21, 2005 7:49 PM
To: Hassan Schroeder; mysql@stripped
Subject: Re: Odd rounding errors with 4.1

Huh, you know.  Now that I'm not at work and therefore don't have my 
numbers to check against, you're right.

Man, I must need more coffee.

Never mind me. :)

May be back tomorrow, though, when I have the numbers in front of me.  I 
know they didn't add up earlier...

Martin

Hassan Schroeder wrote:
> Martin wrote:
> 
>> My recent test involved the following three values from the column:
>> 2.4950
>> 2.5950
>> 2.7700
> 
> 
>> When I use a SUM() on these I get: 7.860
> 
> 
> Sounds good to me...
> 
>> If I switch the column over to a FLOAT, then the SUM() becomes 
>> 7.8599998950958
>>
>> Using Excel to test the numbers, or hand-calculating, I get:
>> 7.8550.
> 
> 
> Time for a hand upgrade, I think :-)  5 + 5 = 5???  I don't even
> want to think about how Excel would come up with this...
> 
>> Shouldn't the SUM() remain with the precision of the DECIMAL type and 
>> not try to round to 2 decimal places?
> 
> 
> My own, possibly suspect, hand calculations show that SUM() is right;
> and it's common knowledge that floating point isn't the right thing to 
> use for situations like this -- that's why there *is* a DECIMAL type.
> 
> FWIW!

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/mysql?unsub=1


Thread
Odd rounding errors with 4.1Martin22 Feb
  • Re: Odd rounding errors with 4.1Dan Nelson22 Feb
    • Re: Odd rounding errors with 4.1 [Duh, me!]Martin22 Feb
Re: Odd rounding errors with 4.1Martin22 Feb
  • RE: Odd rounding errors with 4.1Gordon23 Feb