Wattage problem based on incoherent understanding of how floating point numbers are stored.
Not an actual problem if you do your comparisons properly: SQLite version 3.16.0 2016-11-30 05:08:59 Enter ".help" for usage hints. Connected to a transient in-memory database. Use ".open FILENAME" to reopen on a persistent database. sqlite> CREATE TABLE transactions (amount REAL); sqlite> INSERT INTO transactions VALUES (0.1),(0.1),(0.1); sqlite> INSERT INTO transactions VALUES (-0.3); sqlite> SELECT CASE WHEN (SELECT feq(0, sum(amount)) FROM transactions) ...> THEN 'zero' ...> ELSE 'not zero' ...> END; zero sqlite> sqlite> SELECT sum(amount) FROM transactions; 2.77555756156289e-17 sqlite> Note that if you do not have a function that does floating-point comparisons properly, you can always do something like this: sqlite> CREATE TABLE transactions (amount REAL); sqlite> INSERT INTO transactions VALUES (0.1),(0.1),(0.1); sqlite> INSERT INTO transactions VALUES (-0.3); sqlite> SELECT CASE WHEN (SELECT sum(amount) FROM transactions) < 0.005 ...> THEN 'zero' ...> ELSE 'not zero' ...> END; zero sqlite> sqlite> SELECT sum(amount) FROM transactions; 2.77555756156289e-17 The representational limit of 0 is: sqlite> select ulp(0); 1.11022302462516e-16 which is this far from the sum(amount) sqlite> select ulps(0, sum(amount)) from transactions; -0.25 Note that the IEEE754 value of sum(amount) is less than 1 ulp from 0.0 (1/4 ULP in my case, 1/2 ULP in your case). > -----Original Message----- > From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] > On Behalf Of Simon Slavin > Sent: Wednesday, 30 November, 2016 18:27 > To: SQLite mailing list > Subject: Re: [sqlite] Datatype for prices (1,500) > > > On 30 Nov 2016, at 10:43pm, Keith Medcalf <kmedc...@dessus.com> wrote: > > >> You were given a good recommendation save everything in "cents". Which > >> might also be a good solution depending on the underlying language you > >> use. as you can't store money in a float! > > > > And why can you not store money in a float? > > Because this: > > SQLite version 3.14.0 2016-07-26 15:17:14 > Enter ".help" for usage hints. > sqlite> CREATE TABLE transactions (amount REAL); > sqlite> INSERT INTO transactions VALUES (0.1),(0.1),(0.1); > sqlite> INSERT INTO transactions VALUES (-0.3); > sqlite> SELECT CASE WHEN (SELECT sum(amount) FROM transactions) = 0 > THEN 'zero' > ELSE 'not zero' > END; > not zero > sqlite> SELECT sum(amount) FROM transactions; > 5.55111512312578e-17 > sqlite> > > Please note that this is not just a problem with SQLite. One can > demonstrate the equivalent problem in many programming languages and > databases. > > Simon. > _______________________________________________ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
