MySQL Forums
Forum List  »  General

Re: Is it possible to get MORE results by adding an AND clause to a SQL query?
Posted by: Rick James
Date: November 07, 2010 02:32PM

The FLOAT that MySQL (and virtually every computer in use, not in a museum) uses is the IEEE-754 Standard. It has a precision of 24 binary bits, which is about 6-7 decimal digits. However, when converting between binary and decimal some precision is lost. Note that your literals are pushing the limit.

To further complicate things, MySQL usually does literals and compares (eg, latitude = ...) in DOUBLE. The IEEE DOUBLE has 53 bits of precision. The 24 FLOAT bits representing 42.3720 are not very unlikely to match the 53 DOUBLE bits, hence a mismatch.

General rule with using FLOAT or DOUBLE: Do not expect "=" to work. Nor should you expect 42.3720 <= 42.3720 where one of them is a field and the other is a literal.

Yes, switching to DECIMAL should work. 42.3720 can be stored exactly, and be compared correctly. And (with DECIMAL) 42.3720 = 42.372, as you would expect.

Options: ReplyQuote




Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.