On Sun, 17 Oct 1999, Michael Widenius wrote:
> >>>>> "Bob" == Bob Kline <bkline@stripped> writes:
>
> Bob> On Thu, 14 Oct 1999, MySQL Server wrote:
> >> When using certain floating point values in a column of type "float"
> >> the value displayed differs from the actual value. ....
>
> 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.
>
>
> 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.
>
My apologies for not being clearer in my last paragraph. I didn't in
any way mean to imply that FLOAT or DOUBLE should be exact-precision
types (that role is reserver for DECIMAL and NUMERIC). The precision
bug I was referring to in my last paragraph was the mistaken
interpretation of the precision specifier in DECIMAL or NUMERIC
declarations as including the decimal separator character and the sign
character. This problem was corrected in 3.23.0, but the manual still
reflects the old behavior. I noticed this when I was checking the
manual for a citation to pass on to the original poster in this thread,
and I was offering to submit a documentation patch. But then I noticed
the problems with the language dealing with FLOAT types, which triggered
the rest of that paragraph above.
> 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 :)
>
Well, for starters, FLOAT(4) and FLOAT(8) would be asking for 4-bit and
8-bit precision, respectively (not 4-byte and 8-byte precision). Page
81 from the 4th edition of Date:
FLOAT(p) Floating point number N, say, represented by a signed
binary fraction f of m binary digits (-1 < f < +1,
0 < p <= m ....
The "<= m" part just means that the implementation can use more binary
digits of precision, but not fewer. The important phrase for this
discussion is "binary digits."
From reading what follows it becomes clear that there are basically four
different ways of specifying a floating-point (approximate) type:
1. FLOAT(p) - the user explicitly asks for precision of at least p
bits.
2. FLOAT - the user does not explicitly specify a desired precision;
the implementation gets to choose (Date's exact language is: "FLOAT
is an abbreviation for FLOAT(p), where p is implementation-
defined").
3. REAL - as with FLOAT, the implementation gets to pick the precision.
4. DOUBLE PRECISION - ditto, except that the precision must be greater
than the precision used for REAL.
So, yes there are a number of places where the precision for the
approximate numeric data types is "implementation-defined," but:
* REAL and DOUBLE PRECISION are not intended to be written with
explicit precision specification (precision is left to the
implementation) -- I think we're all in agreement here.
* The implementation only has a completely free hand when precision
is not requested for FLOAT types.
* When precision is specified for FLOAT, the precision is always in
bits.
* In no case does the grammar allow the presence of a second number
following the precision for FLOAT types (the passage at the end of
7.2.5 in the manual notwithstanding -- "Suppose you have a column
type of FLOAT(8,2)" -- which doesn't agree with the standard or
even with MySQL's own table in 7.2.2 above).
It looks as if there has been some muddling of the concepts and syntax
for floating-point and fixed-decimal data types.
Cheers,
Bob
