RE: Odd rounding errors with 4.1
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.492.48 2.5950 2.602.60 2.7700 2.772.77 7.8500 7.867.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:[EMAIL PROTECTED] Sent: Monday, February 21, 2005 7:49 PM To: Hassan Schroeder; mysql@lists.mysql.com 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.858950958 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/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Odd rounding errors with 4.1
Version: Using 4.1.10 on WinXP pro, currently interacting with it using the Query Browser for testing. I have a table set up that contains a column of DECIMAL(15,12) -- financial data, where the precision is highly important. I am building up a series of SQL statements, and I noticed that when doing SUM() on this decimal column, I get a strange rounding error (see below), and was hoping that someone out there can help me with this. My recent test involved the following three values from the column: 2.4950 2.5950 2.7700 (Chosen, for this example, as they are precise at few decimal places). When I use a SUM() on these I get: 7.860 If I switch the column over to a FLOAT, then the SUM() becomes 7.858950958 Using Excel to test the numbers, or hand-calculating, I get: 7.8550. Shouldn't the SUM() remain with the precision of the DECIMAL type and not try to round to 2 decimal places? Anyway, any help is appreciated. Martin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
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.858950958 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/[EMAIL PROTECTED]
Re: Odd rounding errors with 4.1
In the last episode (Feb 21), Martin said: Version: Using 4.1.10 on WinXP pro, currently interacting with it using the Query Browser for testing. I have a table set up that contains a column of DECIMAL(15,12) -- financial data, where the precision is highly important. I am building up a series of SQL statements, and I noticed that when doing SUM() on this decimal column, I get a strange rounding error (see below), and was hoping that someone out there can help me with this. My recent test involved the following three values from the column: 2.4950 2.5950 2.7700 (Chosen, for this example, as they are precise at few decimal places). When I use a SUM() on these I get: 7.860 If I switch the column over to a FLOAT, then the SUM() becomes 7.858950958 Using Excel to test the numbers, or hand-calculating, I get: 7.8550. You hand calculations (and Excel) are wrong, apparently. Here's my hand-calculation: 1 11 1 2.495 5.090 + 2.595 + 2.770 === === 5.090 7.860 , which matches MySQL's results. -- Dan Nelson [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Odd rounding errors with 4.1 [Duh, me!]
Yeah, I checked the math after another responder. Either I was smoking crack at work today (a state likely induced by too much/too little caffeine) or I mis-noted my numbers. I'm going to check again when I get back there tomorrow. But thanks :) Martin Dan Nelson wrote: In the last episode (Feb 21), Martin said: Version: Using 4.1.10 on WinXP pro, currently interacting with it using the Query Browser for testing. I have a table set up that contains a column of DECIMAL(15,12) -- financial data, where the precision is highly important. I am building up a series of SQL statements, and I noticed that when doing SUM() on this decimal column, I get a strange rounding error (see below), and was hoping that someone out there can help me with this. My recent test involved the following three values from the column: 2.4950 2.5950 2.7700 (Chosen, for this example, as they are precise at few decimal places). When I use a SUM() on these I get: 7.860 If I switch the column over to a FLOAT, then the SUM() becomes 7.858950958 Using Excel to test the numbers, or hand-calculating, I get: 7.8550. You hand calculations (and Excel) are wrong, apparently. Here's my hand-calculation: 1 11 1 2.495 5.090 + 2.595 + 2.770 === === 5.090 7.860 , which matches MySQL's results. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
