Re: datatypes, calculations and DB experts
I have recently found a bizarre situation where numbers are not calculating correctly when the values are stored in FLOAT data types. This is not just a database-related issue - it's found in computer systems in general. Floating point numbers are by definition approximations. This often surprises people. As you suggested, the DECIMAL type is provided by many DBMS to get around exactly this problem. DECIMAL numbers are not approximations. They take up much more space to represent very large or very small numbers and aren't directly supported in hardware, so they are less efficient. Nick ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:240732 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Re: datatypes, calculations and DB experts
Nick de Voil wrote: I have recently found a bizarre situation where numbers are not calculating correctly when the values are stored in FLOAT data types. This is not just a database-related issue - it's found in computer systems in general. Floating point numbers are by definition approximations. This often surprises people. As you suggested, the DECIMAL type is provided by many DBMS to get around exactly this problem. DECIMAL numbers are not approximations. They take up much more space to represent very large or very small numbers and aren't directly supported in hardware, so they are less efficient. In addition, MySQL DECIMAL is broken in everything before version 5. When you get more then a few (8?) decimals it will switch to floats for calculations. Jochem ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:240790 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Re: datatypes, calculations and DB experts
Hmm, very interesting. So are you saying that I should never use floats (if so why are the even a data type)? I've used them in SQL server DBs in the past and never had this problem, is this just down to MySQL? Cheers I have recently found a bizarre situation where numbers are not calculating correctly when the values are stored in FLOAT data types. This is not just a database-related issue - it's found in computer systems in general. Floating point numbers are by definition approximations. This often surprises people. As you suggested, the DECIMAL type is provided by many DBMS to get around exactly this problem. DECIMAL numbers are not approximations. They take up much more space to represent very large or very small numbers and aren't directly supported in hardware, so they are less efficient. Nick ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:240851 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Re: datatypes, calculations and DB experts
Hmm, very interesting. So are you saying that I should never use floats (if so why are the even a data type)? I've used them in SQL server DBs in the past and never had this problem, is this just down to MySQL? Cheers I have recently found a bizarre situation where numbers are not calculating correctly when the values are stored in FLOAT data types. This is not just a database-related issue - it's found in computer systems in general. Floating point numbers are by definition approximations. This often surprises people. As you suggested, the DECIMAL type is provided by many DBMS to get around exactly this problem. DECIMAL numbers are not approximations. They take up much more space to represent very large or very small numbers and aren't directly supported in hardware, so they are less efficient. Nick ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:240852 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
RE: datatypes, calculations and DB experts
So are you saying that I should never use floats (if so why are the even a data type)? No, sometimes they are good enough; if they are good enough for your purpose, then they will be more efficiently stored than more precise datatypes such as DECIMAL. I've used them in SQL server DBs in the past and never had this problem, is this just down to MySQL? No, although the default precision may vary between the two database platforms. And as Nick said, this isn't even a problem just with databases, but with computers in general. Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ Fig Leaf Software provides the highest caliber vendor-authorized instruction at our training centers in Washington DC, Atlanta, Chicago, Baltimore, Northern Virginia, or on-site at your location. Visit http://training.figleaf.com/ for more information! ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:240854 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
datatypes, calculations and DB experts
MySQL: 4.1.14 CF: version irrelevant I have recently found a bizarre situation where numbers are not calculating correctly when the values are stored in FLOAT data types. The calculation was: 424/0.53 If you type this into any calculator or write it in an SQL query it always returns 800. However I had a DB query returning 800.43185. After some trail and error I thought the cause may be caused by the way the values were stored in the DB i.e. the data types. I duped the column which stored the 0.53 but this time saved it as a DECIMAL (length 10 with 3 decimals) instead of a FLOAT. When I ran the query again (this time from the new column) hey presto it return 800. All I want to discuss here is why the float was doing this. Please advise DB experts. Thanks ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:240729 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
