On 11/30/2016 11:42 AM, Richard Hipp wrote:
On 11/30/16, Chris Locke <chrisjlo...@gmail.com> wrote:
I recently had this problem. Values stored as real values. Had to check
records in the database to see if any value had changed, and needed
updating. Even though all values in my code were singles, I had bad
rounding problems where (as an example) 0.1+2.2 did not equal 2.3 in the
database. Aargh.
Storing as integers is the way to go.
Just to be clear, this is a property of binary floating-point numbers,
not a quirk of SQLite.
Using the IEEE 64-bit floating point format, there is no way to
represent values 0.1, 2.2, and 2.3. The closest you can get are the
following:
0.1: 0.1000000000000000055511151231257827021181583404541015625
2.2: 2.20000000000000017763568394002504646778106689453125
2.3: 2.29999999999999982236431605997495353221893310546875
If you add the first numbers you get:
2.300000000000000266453525910037569701671600341796875
which is not equal to the third number. Points to remember:
(1) Floating point numbers are usually approximations, not exact values.
(2) Never compare two floating point numbers for equality
Excellent example. I remember when I was taking a digital fundamentals
course, one of the exercise problems was to show the basic binary
representation of 0.1 (decimal) using in a "flat binary" number. (For
example, 0.1 binary would be 0.5 decimal--half, 0.01 binary would be
0.25 decimal, etc.) So, I set out to find 0.1 by this method. (This was
a long time ago.) I found that by the 10th binary decimal place, I was
getting tired of attempting this problem. I was young and dumb at the
time, so the question was asked the next day in class and it was
described as a property of binary floating point concepts. This also
lead to a discussion of binary coded decimal.
The only thing I would add to point #2 (or maybe add it as point #3) is
if you do need to compare floating point numbers make sure to use some
sort of tolerance value that is acceptable.
Ryan
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
