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]
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]
Re: Odd Rounding?
Andrew Kuebler [EMAIL PROTECTED] wrote on 23/01/2004 17:13:13: Can anyone explain why: SELECT ROUND(.012345, 5) - .01234 Why doesn't mysql round the 5 up to .01235? How do I get it to round up? I've tried manipulating ceil, floor, round and truncate and I can't seam to find an easy way to do this. I read in the manual that this behavior depends on the C library implementation, but how do you change it? To me, this is not normal behavior. Can anyone help? Thanks in advance... As stated in the manual, the behaviour of rounding a final digit 5 varies with the C library. To change it you need to relink with a different C library. If I remember correctly, Unix rounds up, whereas Windows rounds towards even. Both behaviours can be justified. The Unix behaviour is justifiable on the grounds that the (unknown) digits after the closing 5 in a floating point calculation are most unlikely to be exactly zero, and therefore rounding up is more likely to be correct than rounding down. The Windows behaviour is justified by saying that this will introduce a bias into the rounding, so that the sum of a large number of rounded numbers will always be larger than the rounded sum of those numbers. If this is, for example, an interest calculation, more interest will be paid than is stricly necessary. By rounding towards even, this bias is removed in a determinate and arguably fair manner. To some extent, it depends uponn your application which is right: engineering vs. finance. Your system appears to be showing Windows behaviour. You could, I suppose, try rebuilding MySQL under Cygwin to get Unis-style behaviour. But this seems a sledgehammer to crack a nut. Alec Cawley -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Odd Rounding?
-Original Message- From: Andrew Kuebler [mailto:[EMAIL PROTECTED] Can anyone explain why: SELECT ROUND(.012345, 5) - .01234 Why doesn't mysql round the 5 up to .01235? How do I get it to round up? Oftentimes standard practice is to round up if the digit before the 5 is odd, and round down if it's even. (This averages out the bias you otherwise get with 5/4 rounding.) To see if this is what you're seeing, try rounding 0.012335 to 5 places...I bet it'll round up to 0.01234. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Odd Rounding?
Actually, no, it rounds to .01233. -Original Message- From: Andrew Kuebler [mailto:[EMAIL PROTECTED] Can anyone explain why: SELECT ROUND(.012345, 5) - .01234 Why doesn't mysql round the 5 up to .01235? How do I get it to round up? Oftentimes standard practice is to round up if the digit before the 5 is odd, and round down if it's even. (This averages out the bias you otherwise get with 5/4 rounding.) To see if this is what you're seeing, try rounding 0.012335 to 5 places...I bet it'll round up to 0.01234. -- 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]
Re: Odd Rounding?
Hi, I have email from Georg Richter about this problem in my MySQL archive: From: Georg Richter [EMAIL PROTECTED] Hi, From http://www.mysql.com/doc/en/Mathematical_functions.html: Note that the behaviour of ROUND() when the argument is half way between two integers depends on the C library implementation. Some round to the nearest even number, always up, always down, or always toward zero. If you need one kind of rounding, you should use a well-defined function like TRUNCATE() or FLOOR() instead. Regards Georg Mikhail. - Original Message - From: Andrew Kuebler [EMAIL PROTECTED] To: 'David Brodbeck' [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Friday, January 23, 2004 6:33 PM Subject: RE: Odd Rounding? Actually, no, it rounds to .01233. -Original Message- From: Andrew Kuebler [mailto:[EMAIL PROTECTED] Can anyone explain why: SELECT ROUND(.012345, 5) - .01234 Why doesn't mysql round the 5 up to .01235? How do I get it to round up? Oftentimes standard practice is to round up if the digit before the 5 is odd, and round down if it's even. (This averages out the bias you otherwise get with 5/4 rounding.) To see if this is what you're seeing, try rounding 0.012335 to 5 places...I bet it'll round up to 0.01234. -- 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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Odd Rounding?
On 23 Jan 2004 at 12:13, Andrew Kuebler wrote: Can anyone explain why: SELECT ROUND(.012345, 5) - .01234 Why doesn't mysql round the 5 up to .01235? How do I get it to round up? I've tried manipulating ceil, floor, round and truncate and I can't seam to find an easy way to do this. This comment from the documentation on TRUNCATE applies to other functions as well: | Note that as decimal numbers are normally not stored as exact | numbers in computers, but as double-precision values, you may be | fooled by the following result: | | mysql SELECT TRUNCATE(10.28*100,0); |- 1027 | | The above happens because 10.28 is actually stored as something | like 10.2799. That's how floating-point numbers work generally, not just in MySQL. Why are you so concerned about it? Presumably your 0.012345 isn't an exact number but a measurement of some sort, so it could just as well be 0.0123449 or 0.0123451, and 0.01234 is perfectly fine as a rounded value. If you do have exact numbers with six decimal places, you're probably better off storing them as some sort of integer and adding the decimal point when necessary for display. -- Keith C. Ivey [EMAIL PROTECTED] Tobacco Documents Online http://tobaccodocuments.org -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Odd Rounding?
Hi Andrew Consider using NUMERIC or DECIMAL to maintain precision. From the manual: The NUMERIC and DECIMAL types are implemented as the same type by MySQL, as permitted by the SQL-92 standard. They are used for values for which it is important to preserve exact precision, for example with monetary data. When declaring a column of one of these types the precision and scale can be (and usually is) specified; for example: salary DECIMAL(5,2) Regards, Matt -- That's how floating-point numbers work generally, not just in MySQL. Why are you so concerned about it? Presumably your 0.012345 isn't an exact number but a measurement of some sort, so it could just as well be 0.0123449 or 0.0123451, and 0.01234 is perfectly fine as a rounded value. If you do have exact numbers with six decimal places, you're probably better off storing them as some sort of integer and adding the decimal point when necessary for display. --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.562 / Virus Database: 354 - Release Date: 1/16/2004 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
