ANATOMY OF FLOAT AND DECIMAL TYPE
(float's binary fraction vs decimal's fraction)
------------------------------
Don't (or never) use float/real/double/single for money data type, f/r/d/s's fractions have bad representation in binary.
Instead, use decimal/numeric or money(same as decimal/numeric type with only a fixed fraction of 4 decimal places), this is more faithful to pencil and paper representation/calculation of number, just takes up more space.
declare @t table(
f1 float,
m1 money,
n1 numeric(22,9),
f2 float,
m2 money,
n2 numeric(22,9)
)
declare @i int
set @i = 1
while @i <= 100 begin
insert into @t(f1,m1,n1, f2, m2,n2)
select 0.1, 0.1, 0.1, 0.7, 0.7, 0.7
set @i = @i + 1
end
select
f1 = sum(f1), m1 = sum(m1), n1 = sum(n1) ,
f2 = sum(f2), m2 = sum(m2), n2 = sum(n2)
from @t
results:
f1 m1 n1 f2 m2 n2
9.9999999999999805 10.0000 10.000000000 70.000000000000128 70.0000 70.000000000
floating: not accurate
for f1: one hundred 0.1 is 9.9999999999999805
for f2: one hundred 0.7 is 70.000000000000128
decimal/numeric: accurate
m1,n1: one hundred 0.1 is 10
m2,n2: one hundred 0.7 is 70
----------------------------------------------------------------------------
-- double precision float: double in vb6, double in pascal/delphi, double in C, double in java
declare @f float
-- single precision float: single in vb6, real in pascal/delphi, float in C, float in java
declare @r real
-- paper and pencil arithmetic
declare @d decimal(22,12) -- same as numeric(22,12)
----
set @f = 8.987654321
set @r = 8.987654321
set @d = 8.987654321
select f = @f, r = @r, d = @d
result:
f r d
8.9876543210000008 8.9876547 8.987654321000
f and r loss precisions
d is exact
----
set @f = 2.110519763456
set @r = 2.110519763456
set @d = 2.110519763456
select f = @f, r = @r, d = @d
result:
f r d
2.1105197634559998 2.1105196 2.110519763456
f and r loss precisions
d is exact
-----------------
declare @a float, @b float, @c float, @d float
set @a = 2.1
set @b = 4.1
set @c = 3.1
set @d = 7.1
select a = @a, b = @b, c = @c, d = @d
a b c d
2.1000000000000001 4.0999999999999996 3.1000000000000001 7.0999999999999996
------------
declare @t table
(
a float, b float, c float, d float, e float, f float
)
insert into @t select 21.4, 76.8, 7.4, 15.4, 7.2
select * from @t
a b c d e
21.399999999999999 76.799999999999997 7.4000000000000004 15.4 7.2000000000000002
----------------
floating point data types are not a bug when you are using them for games or scientific/engineering simulations, and like when computing the progress bar indicator.
where performance is a concern, like games, number's fraction approximation in float(binary) would suffice and are fast.
but for financial values, using float(a number type where fraction can't be accurately represented in binary) is risky.
float/real/double/single fractions cannot be represented accurately in binary (divisible by 2's),
some handful of fractions that can be represented exactly in floats(binary):
ex:
base 10: 7.5
base 2: 111.1 or 7 and 1/2; fraction: .1 = 1/2 = 0.5
base 10: 5.75
base 2: 101.11 or 5 and 3/4; fraction: .11 = 1/2 + 1/4 = 0.50 + 0.25 = 0.75
base 10: 125.125
base 2: 01111101.001 or 125 and 1/8: fraction: .001 = 0/2 + 0/4 + 1/8 = .125
base 10: 3.25
base 2: 11.01 or 125 and 1/4; fraction: .01 = 1/4 = .25
base 10: 9.1 .1 is not divisible by a divisor multiples of 2
base 2: 1001.???????? think of a number when you divide in multiple of 2 that would result in 0.1;
answer is... no exact divisor of multiple 2 would result in exact 0.1
based on the applet(see url below) the ideal divisor that is a multiple of 2 that would result in approximately 0.1 is 134,217,728.
decimal/numeric/currency/money are more faithful to paper and pencil arithmetic of numbers, just takes more space.
if you have a number like 4,000,000,000 this would take up 10 bytes with decimal type, or 5 bytes(if 2 digits per byte)
whereas if you represent 4 billion in int or float type, this would only takes up 4 bytes, and is faster to calculate and summed, yet inaccurate in fraction parts.
-----------------------
references:
floating point in binary:
http://www.python.org/doc/tut/node16.html
anatomy of floating point number:
http://en.wikipedia.org/wiki/IEEE_754
mainsail and floating point:
http://www.xidak.com/mainsail/other_documentation/floatingpoint.html
mysql: problem with floating point
http://dev.mysql.com/doc/refman/5.0/en/problems-with-float.html
--------------------------------------------------------------------------------------------
floating point simulations:
java applet for converting the representation of floating point number in binary:
http://www.h-schmidt.net/FloatApplet/IEEE754.html
too small divisor:
let's try small divisor for 0.1, let's say 1024
if we would only use a small divisor, let's say 1024, 1024 x 0.1 the dividend would become 102.4, omit the .4, 102
102 / 1024 = 0.099609375 = too low to be capable of representing 0.1
if the dividend is 103:
103 / 1024 = 0.1005859375 = too high to be a good representation of 0.1, the extraneous fractions(5859375) is too near in the significant digits
if we would only use a divisor of ten binary places(1024, 2 ^ 10) for 0.1, the binary representation of 0.1 would be far from ideal.
102 = .0001100110
103 = .0001100111
102 / 1024 = 0.099609375
103 / 1024 = 0.1005859375
conclusion: 0.1 can't be accurately represented with too small divisor
------------------
let's try a longer divisor..
32 BIT: mssql's real, c's float, java's float, delphi/pascal's real, vb6's single, postgresql's float4, mysql's float
using the above java applet guide, the ideal divisor would be 134,217,728(for 32 bit) and the dividend would 13,421,773
0.1 representation in binary:
134,217,728 (is a multiple of 2), or 2 ^ 27
13,421,773 divide by 134,217,728 =
0.100000001490116119384765625 (good enough representation of 0.1 in floating binary), 27 binary places(less than 32 bit)
divisor representation in binary: like the base 10 counterpart of 1/3 which doesn't have exact decimal fraction representation, or 0.3333333333..., the 0.1 don't also extends infinitely and doesn't have exact binary representation
000110011001100110011001101...
-------------------
let's try a more longer divisor..
64 BIT: mssql's float, c's double, java's double, delphi/pascal's double, vb6's double, postgresql's float8, mysql's double
let's try a big divisor, 64 bit. 56 binary places
2 ^ 56 = 72,057,594,037,927,936
7,205,759,403,792,793 / 72,057,594,037,927,936
= 0.099999999999999991673327315311326; even if we use big divisor but the choice of dividend is poor, we won't be able to accurately represent 0.1
7,205,759,403,792,795 / 72,057,594,037,927,936
= 0.10000000000000001942890293094024; this is better than the 32 bit version, the extraneous fractions(1942890293094024) are far from the significant digit
then when printing the 64 bit float, stop at let's say.. 8th decimal place, the internal value of 0.10000000000000001942890293094024 would be printed as 0.10000000, but still, under the hood, it still has extra fractions
note that sql server stops printing values at 17th decimal place:
declare @d float
set @D = 0.1
select @d
output: 0.10000000000000001
set @d = 0.98765
select @d
output: 0.98765000000000003
--------------------------------------
FINAL CONCLUSIONS:
1. floating values are hairy creatures too be viable for money type, even the double precision float.
2. just use decimal or currency data type for money values.
to use decimal/currency:
vb6:
use Currency(a decimal with a scale of 4)
or declare variable as variant and then assign it with cdec cast.
example:
Dim Total As Variant 'Decimal
Total = CDec(0.1)
simulate the code below in VB6:
Dim f As Double ' or try Single
f = 0
Dim x As Integer
For x = 1 To 1000
f = f + 0.1
Next
MsgBox f
then this one:
Dim f As Variant 'Decimal
f = CDec(0)
Dim x As Integer
For x = 1 To 1000
f = f + 0.1
Next
MsgBox f
access: use Currency
.net: just use the intrinsic decimal type
java: use the Decimal class
c: create decimal struct, then create function for it
c++: roll your own Decimal class, then overload operators
mssql: use decimal/numeric or money(decimal/numeric with only 4 scale)
postgresl: use numeric
mysql: use numeric
oracle: use decimal
php: di ko makita kung pano decimal type sa php, perhaps i should learn this again
