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@strippedSubject: 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.1 | Martin | 22 Feb |

• Re: Odd rounding errors with 4.1 | Dan Nelson | 22 Feb |

• Re: Odd rounding errors with 4.1 [Duh, me!] | Martin | 22 Feb |

• Re: Odd rounding errors with 4.1 | Martin | 22 Feb |

• RE: Odd rounding errors with 4.1 | Gordon | 23 Feb |