List:General Discussion« Previous MessageNext Message »
From:Michael Widenius Date:October 17 1999 7:40pm
Subject:Re: Floating Point Discrepancy
View as plain text  
>>>>> "Bob" == Bob Kline <bkline@stripped> writes:

Bob> On Thu, 14 Oct 1999, MySQL Server wrote:
>> >Description:
>> When using certain floating point values in a column of type "float"
>> the value displayed differs from the actual value.  For example, a
>> column of type "float(9,7)" with value of "0.283" will be displayed
>> as "0.2830000", yet the actual value is less than "0.282999999" and
>> greater than "0.28299999".  One would expect the value to be equal
>> to the one displayed in the table (or at least the value initially
>> put into the table).  But this is not the case.
>> >How-To-Repeat:
>> Below is a short series of SQL queries to run, to demonstrate the
>> above mentioned problem.
>> create table floatTest(test1 float(9,7), test2 float(10,8));
>> insert into floatTest values(0.283, 0.283);
>> select * from floatTest;
>> select * from floatTest where test1 = 0.283;
>> select * from floatTest where test2 = 0.283;
>> select * from floatTest where test1 > 0.28299999 and test1 < 0.282999999;
>> select * from floatTest where test2 > 0.28299999 and test2 < 0.282999999;
>> drop table floatTest;

Bob> FLOAT is used for approximate values.  You need to use DECIMAL or
Bob> NUMERIC (which are different only in very subtle ways in ISO/ANSI SQL
Bob> and not at all different in MySQL) for fixed-precision values.

Bob> As a footnote, I believe the precision bug in the SQL engine itself has
Bob> been fixed in 3.23, but the online documentation still has it wrong.
Bob> I'd submit a patch for the docs, but the same passage has some funky
Bob> language about the FLOAT type, which may be what threw off the original
Bob> poster for this thread.  I believe the standard allows a single number
Bob> in parentheses following the keyword FLOAT, specifying precision in
Bob> bits.  The table above in section 7.2.2 appears to use that number to
Bob> specify size of value (not precision) in bytes.  Section 7.2.5 talks
Bob> about FLOAT as if it took the same precision/scale specifiers as are
Bob> used by DECIMAL or NUMERIC.  Furthermore, the engine appears to accept
Bob> this unorthodox syntax.  So while I believe I could write a
Bob> documentation patch which describes what the software *should* do, it
Bob> obviously won't be appropriate to apply such a patch without first
Bob> making any necessary modifications to the software itself.  Let me know
Bob> if you want a separate patch for the DECIMAL/NUMERIC precision
Bob> documentation, ignoring the last paragraph dealing with FLOATs.

Bob> Hope this helps.


Sorry, but FLOAT/DOUBLE are still approximated values.  There isn't
that much one can do about these without a lot of trouble as this is
how floating point values work on computers.

The difference between MySQL 3.22 and MySQL 3.23, is that 3.22 always
rounds the value to the number of decimals while MySQL 3.23 also
supports true floating point values (without rounding) if one uses
FLOAT(4), (= FLOAT) and FLOAT(8) ( =DOUBLE).

(I couldn't from C.J.Dates book figure out exactly how he wanted to
declare FLOAT and DOUBLE;  As all values are 'implemention defined'
it sounded ok to just use 4 and 8 :)

Floating Point DiscrepancyMySQL Server15 Oct
  • Re: Floating Point DiscrepancyBob Kline15 Oct
    • Re: Floating Point DiscrepancyMichael Widenius17 Oct
      • Re: Floating Point DiscrepancyBob Kline17 Oct