anatomy of decimal/numeric and float data type

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