On Friday 01 February 2002 14:19, Jim Dickenson wrote: > Am I to assume that based on your response that one should never use a > float field type if you ever want to select the data?
Er, no, 12.33999999 < float_var < 12.34000000 works fine. It is the = operation that doesn't. Remember that floating point numbers are binary numbers. What looks like a limited number of digits in base ten is not necessarily in base 2. Equality has a very carefully defined meaning in the IEEE floating point specifications. However, that definition is based on the binary representation of a number, not the decimal representation. Use DECIMAL types if you really want to compare numbers like this. > This causes a big problem for the way MyODBC 3.51 has been implemented. > I was actually debugging a problem I had in MyODBC when I ran across > this. The way MyODBC works is that is generates a native SQL statement. > In my case the statement was: I can't speak to this as I do not use ODBC. > UPDATE `junk` SET `record`= 91 WHERE record=1 AND title='This is item > one' AND num1=12.3 AND num2=134 AND num3=0.100 AND code='abc' AND > sdate='1991-11-30' AND stime='17:45:00' LIMIT 1 This'll fail almost certainly. You might have it work if the value of num3 is an exact integer, maybe. If the value was generated by any kind of calculations, you could be one bit off (few floating point libraries get all the rounding right all the time). So, 4 could actually be 3.99999999... > Since the float compare did not work (field num1), the record I wanted > changed did not get changed. > > The way our software works is that it fixes the variable number (num1) > to the number of decimals in the constant number before the compare is > done. There are ways for compares to be programmed so they do work. We > are using computers after all. Yep, but anything that relies on floating point having values you can print nicely is going to break. If I had scars for all the times I've been burned by not understanding how floating point really works... If you really need to get a floating point equality, you have to do something like this: let e = a really small number, preferably the smallest number you can express with a floating point number before you hit denormals. Note that this value depends on the magnitude of the floating point values you are comparing. The higher the magnitude, the greater e has to be. equal(a,b) = a-e <= b <= a+e I.e. you cannot test for exact equality, but need to test for whether a is in a range of numbers around b. e (really epsilon to make the math people happy) is the acceptable level of error that you'll allow in order to declare two numbers "equal". Floating point numbers work great for a few applications and are very annoying for others. There are some good papers on floating point and how to really use it correctly on the web. Use google or some search engine. I don't have any links in front of me. If you can take the speed hit, you can use FORMAT on both numbers to make them the same length: UPDATE `junk` SET `record`= 91 WHERE record=1 AND title='This is item one' AND format(num1,2)=format(12.3,2) ... the above tells MySQL to compare the two strings that result when you format the numbers to two decimal places. Those strings should match. Best, Kyle -- Quicknet's MicroTelco fax and voice service has just added another carrier giving MicroTelco users more low rates to choose from. This new carrier is Altair Telecom - a low cost, worldwide voice telephony carrier that is available by up-grading at no additional cost to Internet SwitchBoard v6.0 or MicroTelco Gateway v2.5. http://www.quicknet.net/download/ Cut costs, Fax smart. Use iPrint2Fax worldwide and save! ========================================== FREE software download available at www.iPrint2Fax.com ========================================== --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
