Problem with sqlite3 and Decimal

[Chris Angelico]

On Sat, Dec 12, 2015 at 4:10 PM, Frank Millman <frank at chagford.com> wrote:
> I can reproduce your example above. However, if I set the initial value to
> 5678.7, then the sequence goes
>
> 5678.7
> 5802.15
> 5925.6
> 6049.05
> 6172.5
>
> I would have thought that adding 123.45 to 5802.15 would always produce the
> same result, but here it seems to depend on prior events.
>
> Any idea why? Academic interest only, but I am curious.

You weren't adding 123.45 to 5802.15. Here's why.

The number 123.45 is actually represented as:

>>> 123.45.as_integer_ratio()
(8687021468732621, 70368744177664)

that is, 8687021468732621/2**46. The exact value you want is actually
a repeating binary fraction:

0b1111011.0111001100110011001100...

with the 1100 part repeating. Python rounds this up to
0b11110110111001100110011001100110011001100110011001101, with a
notation that this has an exponent of -46. (Presumably SQLite3 is
doing the same, but I'm using Python's introspection here. This is all
IEEE 754 floating point.)

So when you set the initial value to 0 and then add 123.45 fifty
times, you're adding that tiny bit of rounding error fifty times, too.
When you set your initial value to 5678.7, you're skipping most of the
accumulated error, and so the result still looks the same. When you
printed out something that looked fine, it's because it actually
rounded to the value you started with; that is to say, you weren't
working with 5802.15, but with something really REALLY close to it.
When you added one more of that rounding error, you tipped the sum
across a boundary mark, and suddenly it didn't look like the decimal
number you were expecting; but in reality, it never was.

There's a lot of academic interest to be found in this, and a lot of
fun to be had playing around. If you want to learn more, separate this
from SQLite3 and just play around in Python - you'll find it easier.

ChrisA