Description of the float rounding issue:
Float types are designed for precision over accuracy.
A number like this: 8813.021351 , can turn out like: 8813.021350999999413033947349 when cast as a float.
Decimal types exists in many languages and databases. This type prefers accuracy over precision and thus 8813.021351 would remain the same.
However, the implementation of the decimal type can vary greatly.
Python:In Python, this was the issue I was running into:
>>> from decimal import *
>>> txin = Decimal(8813.021351)
>>> print txin
8813.021350999999413033947349
>>> txout = Decimal(9813.021351)
>>> print txout
9813.021350999999413033947349
>>> print type(txin)
>>> print type(txout)
This module has some strange behaviours including this strange conversion when casting a decimal. It appears to convert the literal to a float and then decimal, causing the strangeness above.
The module does not show this behaviour if casting the decimal from a string:
>>> txin = Decimal(str(8813.021351))
>>> txout = Decimal(str(9813.021351))
>>> print txin
8813.021351
>>> print txout
9813.021351
>>> print type(txin)
>>> print type(txout)
There could be several reasons for this and I have researched quite a bit. In my opinion ,the reasoning is quite obtuse.
MySQL:MySQL also has some strange behavior when using float or double type fields especially with more then 7 digits in either direction.
The decimal type in MySQL appears to work as one would expect and is the reason the experimental branch of the database loader has been created.
The integer solution:One way of dealing with the float/decimal issue is to use the same method as the coin daemons. This method involves converting all numbers to integers of the smallest unit thereby using integer math exclusively.
The integers are then converted to their decimal equivalent when displayed to the user.
This goes against the principal of human readable databases that I have for CCE. Values are no longer truly human readable because all numerical values have to be converted to be read correctly.
Master branch implemented solution:
I used decimal types in the database. Whenever the database loader gets a numerical value from the database, it will double cast when necessary (String -> Decimal).
I wanted to avoid this solution as I do not like the idea of purposely miscasting a type to solve a computation issue. To me, it is a hacky solution, but it works well.
Against my better judgement, this is the solution implemented.
I am sure once the code becomes available on Github, somebody will have a better method for dealing with this.