IEEE Binary Floats

According to the IEEE Standard for Binary Floating-Point Arithmetic, "single-" and "double-precision" numbers are defined as follows:

(* The most significant mantissa bit is assumed to be 1. It is not stored.)

You'd find the same specification in, say, an Intel FPU reference text or a C++ manual. But we found discrepancies when looking through documents for Java (where the exponent range is between -35 and +38), Delphi (where the exponent range is between -45 and +38 for single-precision and between -324 and -308 for double- precision), FIPS SQL (where the FLOAT exponent+size are 9+47 and the REAL exponent+size are 7+23). So, for portability reasons, it would be a good idea to avoid the extremes of the IEEE range.

Most DBMSs support IEEE float formats because FIPS requires that the decimal ranges be supported and because the DBMS code itself is written in a language that supports IEEE floats. But never does an official SQL standard tell vendors "how to store the data". So it might be that your DBMS actually uses the IEEE sizes or it might be that your DBMS actually stores float decimal literals (as xBase does) and processes with base-10 arithmetic. If so, the following information doesn't apply to you.

[Obscure Information] applies for the rest of this section.

Binary Floats are not exact. The danger with these numbers is easy to observe in a simple arithmetic exercise:

1. Represent the number one-third (1/3) in decimal. The maximum number of post-decimal digits (the scale) is large but not infinite. Result: 0.333333
2. Take the sum of three occurrences of this number. Result: 0.333333 + 0.333333 + 0.333333 = 0.999999
3. Note that the number is wrong (three thirds should equal 1). Increase the scale. Try again. You'll never get the correct result because you can't accurately represent 1/3 as a decimal fraction.

Now consider what would happen if your number was decimal, e.g.: one-hundredth (1/100). Try to represent that number as a binary fraction. If you have 16 binary digits (a 16-bit "word"), there are only 2^16 discrete values you can represent, so you are dealing in dividends which are sixty-five-thousand-five-hundred-and-thirty-sixths. The closest number to 1/100 is thus 655/65536 -- i.e.: you have to store 655 in your word. This is a bit small. (Actually 655/65536 is closer to 0.09945, so our error is about one part in a thousand.) In other words: you cannot represent 1/100 as a binary fraction. Worse, if you now convert back to decimal, you will probably get 1/100 again (the smart computer rounds up) so you won't see the inaccuracy. Now consider the result of this SQL code:

SUM(column_containing_the_fractional_value_one_hundredth)

If your Table has 1000 rows, then the conversion to binary happens 1000 times -- cumulating the inaccuracy each time -- and the conversion back to decimal happens only once, when the final SUM is returned. Rounding won't save you, because the result -- 99.45 -- is good to the nearest hundredth. And you won't check the result in your head. Yet the result is "wrong".

In theory, this arithmetic exercise is not a "floating point" problem. We introduced the inaccuracy by converting a decimal fraction to binary. Both fixed-point and floating-point binary fractions have the same danger of inaccuracy, because the danger lies in the fact that we're dealing with binary numbers -- not in the fact that we're dealing with floating-point numbers. So, in theory, the same "wrong" result could be returned for a DECIMAL Column or a NUMERIC Column. In practice, though, the better SQL DBMSs won't use binary fractions for DECIMAL or NUMERIC values. Instead, like COBOL with "PIC 9V99", they actually store an integer with an implied decimal point -- so the number 1/100 is, internally, 1. No conversion occurs because an integral number of hundredths are being stored, rather than a fraction.

TIP: Because of this, for all financial transactions, both money and interest ought to be DECIMAL or NUMERIC. The frequency of definitions like:

CREATE TABLE Table_1 (salary FLOAT);

is a mistake, justified only by the fact that, in C or Pascal, it's normal to define big or non-integer variables as floating-point.

Note:

Portions of the text in this entry are Copyright © 1999 by Ocelot Computer Services Incorporated. Used by permission.