Problem with accumulating decimal values

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
31 messages Options
12
Reply | Threaded
Open this post in threaded view
|

Problem with accumulating decimal values

Frank Millman
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
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Problem with accumulating decimal values

Rob Willett
I *think* this is due to you creating an integer when you first create
the entries

Try changing from

INSERT INTO fmtemp VALUES (1, 0);

to

INSERT INTO fmtemp VALUES (1, 0.0);

Just did

macpro:js rwillett$ sqlite3
SQLite version 3.8.10.2 2015-05-20 18:17:19
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.0);
sqlite> UPDATE fmtemp SET balance = balance + 123.45;
sqlite> SELECT bal FROM fmtemp;
Error: no such column: bal
sqlite> SELECT balance FROM fmtemp;
123.45
sqlite> UPDATE fmtemp SET balance = balance + 123.45;
sqlite> UPDATE fmtemp SET balance = balance + 123.45;
sqlite> UPDATE fmtemp SET balance = balance + 123.45;
sqlite> UPDATE fmtemp SET balance = balance + 123.45;
sqlite> UPDATE fmtemp SET balance = balance + 123.45;
sqlite> UPDATE fmtemp SET balance = balance + 123.45;
sqlite> UPDATE fmtemp SET balance = balance + 123.45;
sqlite> UPDATE fmtemp SET balance = balance + 123.45;
sqlite> UPDATE fmtemp SET balance = balance + 123.45;
sqlite> SELECT balance FROM fmtemp;
1234.5
sqlite> UPDATE fmtemp SET balance = balance + 123.45;
sqlite> SELECT balance FROM fmtemp;
1357.95
sqlite> UPDATE fmtemp SET balance = balance + 123.45;
sqlite> UPDATE fmtemp SET balance = balance + 123.45;
sqlite> UPDATE fmtemp SET balance = balance + 123.45;
sqlite> UPDATE fmtemp SET balance = balance + 123.45;
sqlite> UPDATE fmtemp SET balance = balance + 123.45;
sqlite> UPDATE fmtemp SET balance = balance + 123.45;
sqlite> UPDATE fmtemp SET balance = balance + 123.45;
sqlite> UPDATE fmtemp SET balance = balance + 123.45;
sqlite> UPDATE fmtemp SET balance = balance + 123.45;
sqlite> UPDATE fmtemp SET balance = balance + 123.45;
sqlite> UPDATE fmtemp SET balance = balance + 123.45;
sqlite> UPDATE fmtemp SET balance = balance + 123.45;
sqlite> UPDATE fmtemp SET balance = balance + 123.45;
sqlite> UPDATE fmtemp SET balance = balance + 123.45;
sqlite> UPDATE fmtemp SET balance = balance + 123.45;
sqlite> UPDATE fmtemp SET balance = balance + 123.45;
sqlite> UPDATE fmtemp SET balance = balance + 123.45;
sqlite> UPDATE fmtemp SET balance = balance + 123.45;
sqlite> UPDATE fmtemp SET balance = balance + 123.45;
sqlite> UPDATE fmtemp SET balance = balance + 123.45;
sqlite> UPDATE fmtemp SET balance = balance + 123.45;
sqlite> UPDATE fmtemp SET balance = balance + 123.45;
sqlite> UPDATE fmtemp SET balance = balance + 123.45;
sqlite> UPDATE fmtemp SET balance = balance + 123.45;
sqlite> UPDATE fmtemp SET balance = balance + 123.45;
sqlite> UPDATE fmtemp SET balance = balance + 123.45;
sqlite> SELECT balance FROM fmtemp;
4567.65
sqlite> UPDATE fmtemp SET balance = balance + 123.45;
sqlite> UPDATE fmtemp SET balance = balance + 123.45;
sqlite> UPDATE fmtemp SET balance = balance + 123.45;
sqlite> UPDATE fmtemp SET balance = balance + 123.45;
sqlite> UPDATE fmtemp SET balance = balance + 123.45;
sqlite> UPDATE fmtemp SET balance = balance + 123.45;
sqlite> UPDATE fmtemp SET balance = balance + 123.45;
sqlite> UPDATE fmtemp SET balance = balance + 123.45;
sqlite> UPDATE fmtemp SET balance = balance + 123.45;
sqlite> UPDATE fmtemp SET balance = balance + 123.45;
sqlite> UPDATE fmtemp SET balance = balance + 123.45;
sqlite> UPDATE fmtemp SET balance = balance + 123.45;
sqlite> UPDATE fmtemp SET balance = balance + 123.45;
sqlite> UPDATE fmtemp SET balance = balance + 123.45;
sqlite> UPDATE fmtemp SET balance = balance + 123.45;
sqlite> UPDATE fmtemp SET balance = balance + 123.45;
sqlite> UPDATE fmtemp SET balance = balance + 123.45;
sqlite> UPDATE fmtemp SET balance = balance + 123.45;
sqlite> UPDATE fmtemp SET balance = balance + 123.45;
sqlite> UPDATE fmtemp SET balance = balance + 123.45;
sqlite> UPDATE fmtemp SET balance = balance + 123.45;
sqlite> UPDATE fmtemp SET balance = balance + 123.45;
sqlite> UPDATE fmtemp SET balance = balance + 123.45;
sqlite> UPDATE fmtemp SET balance = balance + 123.45;
sqlite> UPDATE fmtemp SET balance = balance + 123.45;
sqlite> UPDATE fmtemp SET balance = balance + 123.45;
sqlite> UPDATE fmtemp SET balance = balance + 123.45;
sqlite> UPDATE fmtemp SET balance = balance + 123.45;
sqlite> UPDATE fmtemp SET balance = balance + 123.45;
sqlite> UPDATE fmtemp SET balance = balance + 123.45;
sqlite> UPDATE fmtemp SET balance = balance + 123.45;
sqlite> UPDATE fmtemp SET balance = balance + 123.45;
sqlite> UPDATE fmtemp SET balance = balance + 123.45;
sqlite> UPDATE fmtemp SET balance = balance + 123.45;
sqlite> UPDATE fmtemp SET balance = balance + 123.45;
sqlite> UPDATE fmtemp SET balance = balance + 123.45;
sqlite> UPDATE fmtemp SET balance = balance + 123.45;
sqlite> UPDATE fmtemp SET balance = balance + 123.45;
sqlite> UPDATE fmtemp SET balance = balance + 123.45;
sqlite> UPDATE fmtemp SET balance = balance + 123.45;
sqlite> UPDATE fmtemp SET balance = balance + 123.45;
sqlite> UPDATE fmtemp SET balance = balance + 123.45;
sqlite> UPDATE fmtemp SET balance = balance + 123.45;
sqlite> UPDATE fmtemp SET balance = balance + 123.45;
sqlite> UPDATE fmtemp SET balance = balance + 123.45;
sqlite> UPDATE fmtemp SET balance = balance + 123.45;
sqlite> SELECT balance FROM fmtemp;
10246.35
sqlite>

and it seems to work for me.

Rob

On 11 Dec 2015, at 14:21, Frank Millman 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
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Problem with accumulating decimal values

Bernardo Sulzbach
In reply to this post by Frank Millman
Hi Frank,

You want to store an INTEGER type using the lowest used unit (cents or mills).

This page https://www.sqlite.org/datatype3.html may be of assistance next time


--
Bernardo Sulzbach
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Problem with accumulating decimal values

John McKown
In reply to this post by Frank Millman
On Fri, Dec 11, 2015 at 8:21 AM, Frank Millman <[hidden email]> 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
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Problem with accumulating decimal values

Nelson, Erik - 2
In reply to this post by Frank Millman
Frank Millman Friday, December 11, 2015 9:21 AM
>
> I am having a problem accumulating decimal values.
>
 
> sqlite> UPDATE fmtemp SET balance = balance + 123.45; SELECT bal FROM
> sqlite> fmtemp;
> 6049.04999999999
>
> Can anyone explain what is going on, and is there a way to avoid it?
>

It's because of the way that floating point math and display happens.

Simply put, there's no way to avoid it if you really want to use floating point numbers.

Bernardo's suggestion about using integer math may be a fairly easy workaround, especially if you're just adding numbers.

----------------------------------------------------------------------
This message, and any attachments, is for the intended recipient(s) only, may contain information that is privileged, confidential and/or proprietary and subject to important terms and conditions available at http://www.bankofamerica.com/emaildisclaimer.   If you are not the intended recipient, please delete this message.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Problem with accumulating decimal values

Bernardo Sulzbach
Looks like you thought you could have a DECIMAL type (such as MySQL
DECIMAL) here. But SQLite does not allow for this.

My workaround usually is:

    create table accounts(account_number integer, balance integer);
    create view accounts_view as select account_number, balance /
100.0 from accounts;

You may want to use text (or another relational system) if you get to
gargantuan values as integer is limited to signed 8 bytes (which I
think means up to positive 9223372036854775807, needs confirmation).
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Problem with accumulating decimal values

Richard Hipp-3
In reply to this post by Frank Millman
On 12/11/15, Frank Millman <[hidden email]> wrote:
>
> Can anyone explain what is going on, and is there a way to avoid it?
>

Short answer:  https://www.sqlite.org/faq.html#q16

I don't have a longer answer readily at hand, but as questions about
floating point numbers come up a lot, probably I should write up a
tutorial.  I'll try to get that done before the end of the year...
--
D. Richard Hipp
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Problem with accumulating decimal values

Adam DeVita
A good start at the long answer can be found in the archives of this list.

http://sqlite.1065341.n5.nabble.com/Simple-Math-Question-td85140.html#a85157
also found at
https://www.mail-archive.com/sqlite-users@.../msg04587.html
(web search sqlite "simple math question")

It has background, theory, and they show how the conversions of
decimals to floating point and how they add works, using several
examples.



regards,
Adam D.



On Fri, Dec 11, 2015 at 9:55 AM, Richard Hipp <[hidden email]> wrote:

> On 12/11/15, Frank Millman <[hidden email]> wrote:
>>
>> Can anyone explain what is going on, and is there a way to avoid it?
>>
>
> Short answer:  https://www.sqlite.org/faq.html#q16
>
> I don't have a longer answer readily at hand, but as questions about
> floating point numbers come up a lot, probably I should write up a
> tutorial.  I'll try to get that done before the end of the year...
> --
> D. Richard Hipp
> [hidden email]
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



--
--------------
VerifEye Technologies Inc.
151 Whitehall Dr. Unit 2
Markham, ON
L3R 9T1
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Problem with accumulating decimal values

Scott Robison-2
On Fri, Dec 11, 2015 at 8:18 AM, Adam Devita <[hidden email]> wrote:

> A good start at the long answer can be found in the archives of this list.
>
>
> http://sqlite.1065341.n5.nabble.com/Simple-Math-Question-td85140.html#a85157
> also found at
>
> https://www.mail-archive.com/sqlite-users@.../msg04587.html
> (web search sqlite "simple math question")
>
> It has background, theory, and they show how the conversions of
> decimals to floating point and how they add works, using several
> examples.
>

+1

--
Scott Robison
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Problem with accumulating decimal values

Domingo Alvarez Duarte-2
Hello !then  

I did a modification to sqlite3 that basically define a new type
"sqlite_double" and use it instead of "double" (#define sqlite_double 
double) then I can redefine it to _Decimal64 (#define sqlite_double 
_Decimal64) this way with a modern C compiler we can have sqlite3 using
decimal arithmetic.  

There is some more macros not mentioned here (see at
https://github.com/mingodad/squilu/tree/master/SquiLu-ext), I just compiled
it with gcc 4.9.3 and executed the examples from this original thread and it
works fine.  


I'll say again that I still think that is a good idea to replace "double" by
"sqlite_double" to allow alternative decimal/floating point implementations
(on my case _Decimal64).  


Cheers !  

 

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Problem with accumulating decimal values

Frank Millman
In reply to this post by Frank Millman

From: Frank Millman
Sent: Friday, December 11, 2015 4:21 PM
To: [hidden email]
Subject: [sqlite] Problem with accumulating decimal values

> 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.
>


Many thanks for all the replies. I understand what is happening now.

I have found an effective workaround. The Python sqlite3 module allows you to create a user-defined function that you can use from within SQL statements. I wrote a function that uses the Python Decimal module to perform the arithmetic and return the result, and it seems to work just fine.

Thanks again.

Frank
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Problem with accumulating decimal values

Darren Duncan
In reply to this post by Frank Millman
Frank,

The problem you are having is due to SQLite not following the SQL standard
regarding non-integral numeric types.

The SQL standard specifies that the DECIMAL type is exact numeric and able to
represent decimal numbers exactly.  However, when you ask SQLite for a DECIMAL
column, that is not what it will give you; instead, it will silently "succeed"
but give you an inexact numeric type instead, a floating point number, as if you
had said FLOAT/etc instead of DECIMAL.

So the problem you are having is due to the actual numbers in the database not
being what you told it to store, but just an approximation.

Per another suggestion, the best workaround is to use an INTEGER type instead,
and store an even multiple of whatever your smallest currency unit size is, eg
cents rather than dollars.

-- Darren Duncan

On 2015-12-11 6:21 AM, Frank Millman wrote:

> 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
<snip>

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Problem with accumulating decimal values

Bernardo Sulzbach
On Sat, Dec 12, 2015 at 6:51 AM, Darren Duncan <[hidden email]> wrote:
>
> Per another suggestion, the best workaround is to use an INTEGER type
> instead, and store an even multiple of whatever your smallest currency unit
> size is, eg cents rather than dollars.
>

As I understood, he is doing the math in Python and saving strings
(that are produced by Python decimal arbitrary precision classes) to
the database, what should work perfectly.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Problem with accumulating decimal values

James K. Lowden
In reply to this post by Frank Millman
On Fri, 11 Dec 2015 16:21:30 +0200
"Frank Millman" <[hidden email]> wrote:

> sqlite> UPDATE fmtemp SET balance = balance + 123.45;
> sqlite> SELECT bal FROM fmtemp;
> 5925.59999999999

To a question like that you'll receive a lot of answers about numerical
accuracy.  And it's true that there are ways to "do the math" without
using floating point representation.  It's also true that it's rarely
necessary, which is why floating point representation exists and *is*
widely used.  You may find it works for you too, unless you have to
adhere to a specific rounding policy.  

Per your example, you're working with 2 decimal places of precision.
5925.59999999999 rounds off to 5925.60; it even rounds off to
5925.6000000000, not too shabby.  If you keep adding 123.45 to it,
you'll find you can go on forever before the answer is wrong in the
second decimal place.  

IEEE 754 is a solid bit of engineering.  It's capable of representing
15 decimal digit of precision.  That's good enough to measure the
distance to the moon ... in millimeters.  

You could have an exceptional situation, but that would be
exceptional.  Usually, double-precision math works just fine, provided
you have some form of round(3) at your disposal when it comes time to
render the value in decimal form.  

--jkl
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Problem with accumulating decimal values

E.Pasma
16 dec 2015,  James K. Lowden:

> On Fri, 11 Dec 2015 16:21:30 +0200
> "Frank Millman" <[hidden email]> wrote:
>
>> sqlite> UPDATE fmtemp SET balance = balance + 123.45;
>> sqlite> SELECT bal FROM fmtemp;
>> 5925.59999999999
>
> To a question like that you'll receive a lot of answers about  
> numerical
> accuracy.  And it's true that there are ways to "do the math" without
> using floating point representation.  It's also true that it's rarely
> necessary, which is why floating point representation exists and *is*
> widely used.  You may find it works for you too, unless you have to
> adhere to a specific rounding policy.
>
> Per your example, you're working with 2 decimal places of precision.
> 5925.59999999999 rounds off to 5925.60; it even rounds off to
> 5925.6000000000, not too shabby.  If you keep adding 123.45 to it,
> you'll find you can go on forever before the answer is wrong in the
> second decimal place.
>
> IEEE 754 is a solid bit of engineering.  It's capable of representing
> 15 decimal digit of precision.  That's good enough to measure the
> distance to the moon ... in millimeters.
>
> You could have an exceptional situation, but that would be
> exceptional.  Usually, double-precision math works just fine, provided
> you have some form of round(3) at your disposal when it comes time to
> render the value in decimal form.
>
> --jkl
Hello, so in short, rounding the column anywhere it is used, is  
another solution. I confirmed this below. Thanks, E. Pasma.

BEGIN;
UPDATE fmtemp SET bal = ROUND(bal,2) + 123.45;
(repeat a 1.000.001 times
END;
SELECT bal FROM fmtemp;
123450123.45


_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Problem with accumulating decimal values

Keith Medcalf

> Hello, so in short, rounding the column anywhere it is used, is
> another solution. I confirmed this below. Thanks, E. Pasma.
>
> BEGIN;
> UPDATE fmtemp SET bal = ROUND(bal,2) + 123.45;
> (repeat a 1.000.001 times
> END;
> SELECT bal FROM fmtemp;
> 123450123.45

Absolutely not!  You should NEVER round the value and store it back in the datastore.  Rounding is ephemeral for the convenience of ugly-bags-of-mostly-water who are fixed in their world-view so that data can be DISPLAYED to them in a format that fits their limited view.  

You should NEVER round as you have done above.  You may get lucky and the errors may cancel each other out, or you may get more usual results where the error equals the theoretical max of the sum of the absolute value of all the truncated values, which can be quite significant depending on the scale of the number you are dealing with (and theior scales relative to each other).




_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Problem with accumulating decimal values

Domingo Alvarez Duarte-2
Hello !  

I said once and I'll say again for some applications it would make sense to
use _Decimal64 (_Decimal32, _Decimal128) instead of floating points.  

Even if it's done in software the performance is acceptable on most common
cases.  

See a sqlite3.c/sqlite3.h modified to use "_Decimal64" instead of "double" at
https://github.com/mingodad/squilu/tree/master/SquiLu-ext using it we can
easily swap between "double"/"_Decimal64" by defining a macro
"-DSQLITE_USE_DECIMAL=1".  

I wish it would be part of the official sqlite3 !  

Cheers !  

 

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Problem with accumulating decimal values

E.Pasma
In reply to this post by Keith Medcalf
16 dec 2015, Keith Medcalf:

>> BEGIN;
>> UPDATE fmtemp SET bal = ROUND(bal,2) + 123.45;
>> (repeat a 1.000.001 times
>> END;
>> SELECT bal FROM fmtemp;
>> 123450123.45
>
> You should NEVER round as you have done above.  You may get lucky  
> and the errors may cancel each other out, or you may get more usual  
> results where the error equals the theoretical max of the sum of the  
> absolute value of all the truncated values, which can be quite  
> significant depending on the scale of the number you are dealing  
> with (and theior scales relative to each other).


Hello, I was only trying to digest JKL's post and the result looks  
good. The example prints the value as it is in the database and shows  
that there is no accumulated error there. I do not see a counter  
example (not yet).

Ok this does not work of any scale of numbers. But a solution with  
integers neither does

E.Pasma


_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Problem with accumulating decimal values

Bernardo Sulzbach
On Wed, Dec 16, 2015 at 9:43 AM, Keith Medcalf <[hidden email]> wrote:

>
>> Hello, so in short, rounding the column anywhere it is used, is
>> another solution. I confirmed this below. Thanks, E. Pasma.
>>
>> BEGIN;
>> UPDATE fmtemp SET bal = ROUND(bal,2) + 123.45;
>> (repeat a 1.000.001 times
>> END;
>> SELECT bal FROM fmtemp;
>> 123450123.45
>
> Absolutely not!  You should NEVER round the value and store it back in the datastore.  Rounding is ephemeral for the convenience of ugly-bags-of-mostly-water who are fixed in their world-view so that data can be DISPLAYED to them in a format that fits their limited view.
>

Although I agree about not rounding and updating the store with
"corrected" values. I don't think there is a need to call the
ugly-bags-of-mostly-water ugly-bags-of-mostly-water. Also, I wouldn't
want myself to see 22.9999999999 instead of 23.00 in the frontends I
use either. In a practical sense, I believe the latter reduces the
amount of processing my brain has to do and I can better focus on what
matters. But then again, just use string formatting on the view of the
project.

On Wed, Dec 16, 2015 at 12:05 PM, E.Pasma <[hidden email]> wrote:

> Ok this does not work of any scale of numbers. But a solution with integers
> neither does
>
> E.Pasma
>

Preferences aside, no solution ever devised will work with **any**
scale with numbers as we have finite data storage. That is very
pedantic, but just to be clear. I like integer better than floating
points and text for currencies, some will have other preferences, it
does not really matter as long as we are not working together.

--
Bernardo Sulzbach
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Problem with accumulating decimal values

Adam DeVita
Good day,
As a matter of interest, when calculating interest on a sum of money
expressed in pennies, how do you handle  int arithmetic truncating?
Is that an accounting design rule thing when dealing with fractions of
a penny to round?

Is this an arbitrary quantization?  Once upon a time there existed the Ha'penny
https://en.wikipedia.org/wiki/Halfpenny_%28British_pre-decimal_coin%29
https://en.wikipedia.org/wiki/Half_cent_%28United_States_coin%29



I think the ugly-bags-of-mostly-water indirection was humorous.  I
found it funny.

https://en.wikipedia.org/wiki/Home_Soil

live long and prosper.

Adam

On Wed, Dec 16, 2015 at 10:17 AM, Bernardo Sulzbach
<[hidden email]> wrote:

> On Wed, Dec 16, 2015 at 9:43 AM, Keith Medcalf <[hidden email]> wrote:
>>
>>> Hello, so in short, rounding the column anywhere it is used, is
>>> another solution. I confirmed this below. Thanks, E. Pasma.
>>>
>>> BEGIN;
>>> UPDATE fmtemp SET bal = ROUND(bal,2) + 123.45;
>>> (repeat a 1.000.001 times
>>> END;
>>> SELECT bal FROM fmtemp;
>>> 123450123.45
>>
>> Absolutely not!  You should NEVER round the value and store it back in the datastore.  Rounding is ephemeral for the convenience of ugly-bags-of-mostly-water who are fixed in their world-view so that data can be DISPLAYED to them in a format that fits their limited view.
>>
>
> Although I agree about not rounding and updating the store with
> "corrected" values. I don't think there is a need to call the
> ugly-bags-of-mostly-water ugly-bags-of-mostly-water. Also, I wouldn't
> want myself to see 22.9999999999 instead of 23.00 in the frontends I
> use either. In a practical sense, I believe the latter reduces the
> amount of processing my brain has to do and I can better focus on what
> matters. But then again, just use string formatting on the view of the
> project.
>
> On Wed, Dec 16, 2015 at 12:05 PM, E.Pasma <[hidden email]> wrote:
>
>> Ok this does not work of any scale of numbers. But a solution with integers
>> neither does
>>
>> E.Pasma
>>
>
> Preferences aside, no solution ever devised will work with **any**
> scale with numbers as we have finite data storage. That is very
> pedantic, but just to be clear. I like integer better than floating
> points and text for currencies, some will have other preferences, it
> does not really matter as long as we are not working together.
>
> --
> Bernardo Sulzbach
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



--
--------------
VerifEye Technologies Inc.
151 Whitehall Dr. Unit 2
Markham, ON
L3R 9T1
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
12