On Friday, 14 December, 2018 23:49, Frank Millman <[email protected]> wrote:
> I know that floating point is not precise and not suitable for
> financial uses.
This is debatable. In the "old days" of what was called "fast floating point"
it was certainly true since the epsilon of a "fast floating point" number is
about 2.2e-08 which meant that representation errors became significant at
about $1000.0000 assuming that the floating point operations were carried out
to the maximum epsilon of the representation and with a maximum error of a
single ULP (and the specifications did not require 1 ULP accuracy, the accuracy
of the floating point calculations often being up to about 20 to 200 ULP even
for simple arithmetic). Add in the propensity for the proletariat to do
questionable "rounding" of intermediates, and you quite quickly end up with
huge errors.
Those of us who "cared" about accuracy bought computers with "math
co-processors" that could perform proper floating point arithmetic in
accordance with the IEEE-754 standard using "double precision" floating point
numbers which had an epsilon of 2.22044604925031e-16 and guaranteed the
accuracy of all arithmetic operations within 1 ULP (or even longer precision
with the same 1 ULP guarantee). These little devices would minimally DOUBLE
the cost of the computer and cut its performance merely in half.
Outside of the scientific and engineering worlds this was little known and the
MBA types would use inaccurate "fast floating point" and "intermediate
rounding" to do monetary calculations with the expected result: the answer was
not that which what would be obtained via paper and pencil methods. Due to the
difficulties inherent in explaining the why and wherefor of this, the general
proletariat simply summarized as "don't use floating point for money instead
use paper and pencil methods such as scaled integer (fixed point) or packed BCD
arithmetic" because it was much easier to remember than the actual reason for
the difficulty, and generally cheaper (cost wise and compute time wise) than
using "proper" floating point properly (plus the fact that a degree in
mathematics and an understanding of how computers worked was not required,
significantly reducing the cost of implementation).
Fast forward 25 years and you would these days be hard pressed to find a
computer that DOES NOT use proper IEEE-754 floating point and that DOES NOT
default to a minimum of double precision representation and that DOES NOT use
extended precision internally to ensure that the 1 ULP guarantee is ensured.
However, the memory of the proletariat is long: Even though the original issue
leading to the quaint "do not use floating point for money" aphorism no longer
exists the problem of knowing "why this was so" still exists. And the
propensity to apply intermediate rounding still exists.
> Even so, I am curious about the following -
>SQLite version 3.26.0 2018-12-01 12:34:55
>Enter ".help" for usage hints.
>Connected to a transient in-memory database.
>Use ".open FILENAME" to reopen on a persistent database.
>sqlite> .open /sqlite_db/ccc
>sqlite> select sum(amount_cust) from ar_trans where cust_row_id = 4
>and tran_date between '2015-05-01' and '2015-05-31';
>211496.26
>Python 3.7.0 (v3.7.0:1bf9cc5093, Jun 27 2018, 04:59:51) [MSC v.1914
>64 bit (AMD64)] on win32
>Type "help", "copyright", "credits" or "license" for more
>information.
>>>> import sqlite3
>>>> sqlite3.sqlite_version
>'3.26.0'
>>>> conn = sqlite3.connect('/sqlite_db/ccc')
>>>> cur = conn.cursor()
>>>> cur.execute("select sum(amount_cust) from ar_trans where
>cust_row_id = 4 and tran_date between '2015-05-01' and '2015-05-31'")
><sqlite3.Cursor object at 0x000002C1D6BBCF80>
>>>> cur.fetchone()
>(211496.25999999992,)
>With the same version of sqlite3 and the same select statement, why
>does python return a different result from sqlite3.exe?
They do not. The value is the same. The SQLite3 shell is simply applying some
type of "rounding for display" which is using different rules than the
"rounding for display" that is being used by the Python interpreter.
Neither is the "actual value" but is a base-10 aproximate representation of the
base-2 number.
Given that the number is an IEEE-754 double precision floating point number
with a 53 bit mantissa, Python is displaying the number rounded to 17 digits of
base-10 decimal precision, and the SQLite3 interpreter is "rounding out" the
result to 8 digits of base-10 decimal precision.
---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a
lot about anticipated traffic volume.
_______________________________________________
sqlite-users mailing list
[email protected]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
