SQLite Forum

help! decimal field multiplication error?
Login
The floating point value 27.1 is 27.100000000000001421085471  
The floating point value 56.7 is 56.700000000000002842170943  

Multiplying them gives the result:  1536.5700000000001637090463

This is the result that you obtained.  It is merely "rounded" for your viewing pleasure.  

SQLite3 does not have a DECIMAL datatype.  IEEE compliant floating point is "computed exactly then rounded half-even in the least significant place".  

DECIMAL with a specified precision, however, must be "computed exactly then rounded half-even to the specified precision".

Assuming that the IEEE specified rounding rounds beyond the first guard digit then rounding half-even to the lesser precision will obtain the correct result.

In the "olden days" DECIMAL usually meant to store the number in BCD or Packed Decimal format (in the 1940's).  Nowadays, DECIMAL is usually emulated by judicious application of half-even rounding on floating point numbers.

Since computers are advanced technology and methods of computation are complicated beyond the understanding of most "computer users", there have been a lot of "old wives tales" that have popped up amongst the proletariat regarding "the evil of floating point numbers" and that "one cannot calculate accurately using floating point" to "you cannot use floating point for money".  These are all untrue.  Floating point works exactly as designed and it is merely lack of understanding that starts these sorts of rumours.

Using standard IEEE floating point to "compute money" by "emulating" the Packed Decimal type (DECIMAL) is a problem that has been solved for at least half-a-century.