SQLite Forum

> Well, that is because whatever platform/compiler is being used does not know how to do arithmetic correctly (ie, it is not generating IEEE compliant operations). Here are two results with exactly the same code, both run on the same machine on the same Operating System, one after each. (FYI the hardware is an Intel Xeon E3-1503M v6 with the latest microcode and the OS is Windows 10 2004 build 19041.329 x64).
> Actually, on checking, I also modified func.c to use extended precision for the intermediate in the sum. When I compile with GCC (MinGW) with the extended precision turned off, I get the same result as MSVC.

I think this is a perfect example of the complexity of floating-point accuracy. If you could be so sure that the error I was describing was impossible.

Fundamentally, the issue boils down to the fact that when decimal numbers are input into a system using binary floating-point, by necessity, we can't store most numbers exactly, and resort to using approximations. Once we start on the path of approximation and start to do a lot of math with these numbers, the errors can grow surprisingly fast (and this example used no magnitude cancelation to get us to big errors faster.

The fundamental advantage of the Decimal number types is that they are able to store our decimal inputs exactly. In many domains, like most scientific ones, that isn't important, but in others, like finance, it is critical. The basic workflow was established long ago with people working with paper and pencils writing out numbers as decimals, so that has become the basic RULE for doing it.

It isn't that Decimal arithmetic is more precise, the epsilons are very close to the same (by design), but that the input numbers can start off precise, and then the intermediate values can stay precise, and thus we can get an answer that is precise as long as we don't exceed our resolution.

I suppose that one big reason computers don't have as good of support for decimal math is that the financial people who do need it, don't really need machines that do it blindingly fast, just machines that can handle a lot of data, so the machines that are made for the other types that do need speed, but don't need this exact decimal accuracy, are good enough for them as they likely are memory bottlenecking even with software-based math routines.

I suppose the other interesting thing is that we absolutely wouldn't accept for the database to slightly change our text fields to something 'close enough', but we accept it for our floating-point number fields.