0

I want to retrieve a data based on certain condition in database : 

SELECT * FROM `chatRoom` WHERE 5.27680128733988 + 100.488135965769 = 105.764937253109

This actually produced 0 result when there we know that the value of the addition is 105.764937253109

To prove that the addition is equals to 105.764937253109 I actually update one of the row from the table to get the correct value : 

UPDATE `chatRoom` SET `latitude`= 5.27680128733988 + 100.488135965769 WHERE Room_ID = 2

The updated column shows the result of the addition is 105.764937253109 but why when I try to select the results as in the first database query, it shows 0 data found?

Thank you

dramasea
  • 3,370
  • 16
  • 49
  • 77
  • 2
    You definitely need to read https://en.wikipedia.org/wiki/Floating_point#Accuracy_problems — trying to assess equality on floating point numbers will lead you to disaster. Don't. – jcaron Feb 05 '16 at 16:27
  • Possible duplicate of [Is floating point math broken?](http://stackoverflow.com/questions/588004/is-floating-point-math-broken) – jcaron Feb 05 '16 at 16:30
  • @jcaron do you know is there anyway to assess equality on longitude and latitude? because as far as i know, they all represented in floating point – dramasea Feb 05 '16 at 16:38
  • 1
    Several options: don't use floating point numbers, but store them as integers with an appropriate multiplier, e.g. store thousandth of degrees or millionths of degrees rather than degrees), i.e. do implicit fixed point. Or use MySQL's `numeric`/`decimal` fixed-point data types. Or add a margin of error, e.g. `abs(lat1-lat2) < 0.001` (or whatever the margin of error you want to use might be) instead of `lat1=lat2`. In any case, you probably don't want exact equality for lat/long, so the last option is probably your best bet. – jcaron Feb 05 '16 at 16:49

1 Answers1

0

Because 5.27680128733988 + 100.488135965769 = 105.7649372531089; you're missing an "8"

sfell77
  • 976
  • 1
  • 9
  • 15
  • so the correct answer is actually: 105.76493725310888 neither excel nor calculator (windows) could figure it out -- wrote it out by hand :) – sfell77 Feb 05 '16 at 16:49
  • There is no correct answer. Not all decimal numbers can be represented in floating point format (actually, most can't), they're all rounded up or down, and changing the order of operations can change the result. – jcaron Feb 05 '16 at 16:52