On Fri, Dec 11, 2015 at 8:21 AM, Frank Millman <frank at chagford.com> wrote:
> Hi all > > I am having a problem accumulating decimal values. > > I am actually using Python, but I can reproduce it in the sqlite3 > interactive terminal. > > SQLite version 3.8.6 2014-08-15 11:46:33 > Enter ".help" for usage hints. > Connected to a transient in-memory database. > Use ".open FILENAME" to reopen on a persistent database. > > sqlite> CREATE TABLE fmtemp (acno INT, balance DECIMAL); > sqlite> INSERT INTO fmtemp VALUES (1, 0); > > sqlite> UPDATE fmtemp SET balance = balance + 123.45; > sqlite> SELECT bal FROM fmtemp; > 123.45 > > sqlite> UPDATE fmtemp SET balance = balance + 123.45; > sqlite> SELECT balance FROM fmtemp; > 246.9 > > I repeat this a number of times, and it runs fine, until this happens - > > sqlite> UPDATE fmtemp SET balance = balance + 123.45; > sqlite> SELECT bal FROM fmtemp; > 5802.15 > > sqlite> UPDATE fmtemp SET balance = balance + 123.45; > sqlite> SELECT bal FROM fmtemp; > 5925.59999999999 > > sqlite> UPDATE fmtemp SET balance = balance + 123.45; > sqlite> SELECT bal FROM fmtemp; > 6049.04999999999 > > Can anyone explain what is going on, and is there a way to avoid it? > > Thanks > > Frank Millman > > ?This is a common problem. It has been discussed here, and elsewhere, quite a bit. Basically, you want _decimal_ accuracy from a _binary_ floating point format. But _decimal_ floating point numbers may not have an exact _binary_ floating point representation. Perhaps these will be of some help: http://dba.stackexchange.com/questions/62491/why-does-sqlite-return-incorrect-sum http://stackoverflow.com/questions/2100490/floating-point-inaccuracy-examples http://docs.oracle.com/cd/E19957-01/806-3568/ncg_goldberg.html (generic despite being from Oracle Corp.) The real solution is IEEE 754-2008 decimal floating point implementation. https://en.wikipedia.org/wiki/Decimal_floating_point https://software.intel.com/en-us/articles/intel-decimal-floating-point-math-library The only _hardware_ implementation that I know of for this format is from IBM, on their Power6 (and after) and z9 (and after) series machines. It is definitely not (yet) available on an Intel based machine. -- Schrodinger's backup: The condition of any backup is unknown until a restore is attempted. Yoda of Borg, we are. Futile, resistance is, yes. Assimilated, you will be. He's about as useful as a wax frying pan. 10 to the 12th power microphones = 1 Megaphone Maranatha! <>< John McKown
