MySQL Forums
Forum List  »  General

Re: Is it possible to get MORE results by adding an AND clause to a SQL query?
Posted by: kris helenek
Date: November 05, 2010 10:36AM

I've narrowed down the problem a little more. Although this just makes the above problem even worse (it shouldn't have returned 88 results with that second join since the first and clause should have failed.)

select latitude, longitude from address where id = 42289;
42.3720, -71.1204

select id from address where latitude = 42.3720 and longitude = -71.1204;
0 rows fetched

Obviously one would expect the 2nd row to return 42289. Both columns are Float(8,4). I did an explain on the 2nd query to see what index it used, and then I dropped and recreated that index, thinking it might be corrupt.

On a whim I started doing range comparisons, and finally narrowed it down to this:
select id from address where latitude > 42.37195 and latitude < 42.37197 and longitude = -71.1204;
1 row fetched : 42289

If I did latitude = 42.37196 it did not work, and I could not narrow down any further.

So there is definitely a serious floating point bug in MySql. Also, this could be due to the fact that the value used to be 42.37196 but I changed the float column precision to 8,4. (I'm not sure that is true, just a hypothesis.) MySql might not actually change the value but only changes the output?

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.