
123

That is why integer money amounts have a value/scale pair instead of mantissa/exponent. I don't expect you would be given change in the dimension of 1 "zimba" if you need 100 trillion to pay for a tank of gas.
The principle behind money = v * 10 ^ s is just the same as float = m * 2 ^e, but avoids the rounding inherent in transforming from base 2 to base 10.
Ursprüngliche Nachricht
Von: sqliteusers [mailto: [hidden email]] Im Auftrag von Wout Mertens
Gesendet: Sonntag, 16. Dezember 2018 15:55
An: SQLite mailing list < [hidden email]>
Betreff: [EXTERNAL] Re: [sqlite] Question about floating point
On Sat, Dec 15, 2018 at 7:13 PM Keith Medcalf < [hidden email]> wrote:
>
> >And yet ... here we are. The post which started this thread summed
> >currency amounts and reached a total of 211496.25999999992 .
>
> >Yes, you can say 'that would have been rounded before it was
> >printed'. But then you're into the old questions: do you round at
> >every step, or only at the end ? Do you round or truncate ? Where
> >does the fraction go ? etc. etc..
>
> You apply halfeven rounding (not elementary school 4/5 rounding) only
> for display (output) and never round intermediates. The "fraction"
> does not exist ... Though if you do 4/5 rounding rather than halfeven
> rounding the accumulated errors will amount to quite a sum.
>
TIL, thanks!
I'd also like to point out a problem with integer money: inflation. For USD it's been OK so far, but imagine having to handle the Zimbabwean Dollar, which ended up having 100 trillion dollar notes. Good way to overflow your integers.
With floating point, that's not a problem.
_______________________________________________
sqliteusers mailing list
[hidden email]
http://mailinglists.sqlite.org/cgibin/mailman/listinfo/sqliteusers___________________________________________
Gunter Hick  Software Engineer  Scientific Games International GmbH  Klitschgasse 24, A1130 Vienna  FN 157284 a, HG Wien, DVR: 0430013  (O) +43 1 80100  0
May be privileged. May be confidential. Please delete if not the addressee.
_______________________________________________
sqliteusers mailing list
[hidden email]
http://mailinglists.sqlite.org/cgibin/mailman/listinfo/sqliteusers


On Sun, Dec 16, 2018 at 9:26 PM Thomas Kurz < [hidden email]> wrote:
> > Good way to overflow your integers.
> > With floating point, that's not a problem.
>
> With int64, it shouldn't be a problem either.
>
>
Well.. are you absolutely sure about that?
Considering these Zimbabwe Dollars amounts mentioned earlier, let's compare
max unsigned int64 with sample amount:
2^64
18446744073709551616
10000000000000000000000001
It seems int64 is easy overflown.
PostgreSQL's NUMERIC data type would be a solution here.
_______________________________________________
sqliteusers mailing list
[hidden email]
http://mailinglists.sqlite.org/cgibin/mailman/listinfo/sqliteusers


On 17 Dec 2018, at 04:32, D Burgess < [hidden email]> wrote:
>> Banks still use, as they have for a very long time, Binary Coded
>> Decimal, or some equivalent that does not suffer from a loss of
>> accuracy, so all this foofaraw to do with floating point representation
>> of various amounts of currency does not apply to the real world.
> As do insurance companies and many in the manufacturing world (inventory).
> There is a lot to like about BCD.
And do any processors/computers have hardware for that these days?

Cheers  Tim
_______________________________________________
sqliteusers mailing list
[hidden email]
http://mailinglists.sqlite.org/cgibin/mailman/listinfo/sqliteusers


On Mon, Dec 17, 2018 at 2:43 AM Tim Streater < [hidden email]> wrote:
> On 17 Dec 2018, at 04:32, D Burgess < [hidden email]> wrote:
>
> >> Banks still use, as they have for a very long time, Binary Coded
> >> Decimal, or some equivalent that does not suffer from a loss of
> >> accuracy, so all this foofaraw to do with floating point representation
> >> of various amounts of currency does not apply to the real world.
>
> > As do insurance companies and many in the manufacturing world
> (inventory).
> > There is a lot to like about BCD.
>
> And do any processors/computers have hardware for that these days?
>
>
>
I don't think anyone has pointed out that the "evil" is not floating point,
it is the binary exponent.
Just have the exponent be a decimal number, and accept that the mantissa
may have some highorder zeroes.
Gerry Snyder
_______________________________________________
sqliteusers mailing list
[hidden email]
http://mailinglists.sqlite.org/cgibin/mailman/listinfo/sqliteusers


Gerry Snyder wrote:
< I don't think anyone has pointed out that the "evil" is not floating point, it is the binary exponent.
Disregarding the “evil” appellation, the fundamental fact is that, with modern floating point hardware (implementing the IEEE754 standard), only that subset of rational numbers having a denominator which is a power of 2 can be represented. If that is what you were trying to say, I would point out that it is not the representation of the exponent (binary or otherwise) that creates the mismatch with (many) rational numbers having a denominator which is a power of 10; it is that many such denominators cannot be represented at all when the interpretation of the exponent Ne is as 2 ^ Ne.
_______________________________________________
sqliteusers mailing list
[hidden email]
http://mailinglists.sqlite.org/cgibin/mailman/listinfo/sqliteusers


Tim Streater wrote:
[Responding to “There is a lot to like about BCD.”]
> And do any processors/computers have hardware for that these days?
The modern X86 descendent processors from AMD and Intel implement AA{A,D,M,S} instructions, which do the BCD adjustments associated with addition, subtraction, multiplication and division. They do not work in 64bit mode, however. It would be interesting to know why not.
_______________________________________________
sqliteusers mailing list
[hidden email]
http://mailinglists.sqlite.org/cgibin/mailman/listinfo/sqliteusers


On Sat, 15 Dec 2018 01:24:18 0800
Darren Duncan < [hidden email]> wrote:
> If yours is a financial application then you should be using exact
> numeric types only
Color me skeptical. That very much depends on the application. IEEE
doubleprecision floating point is accurate to within 15 decimal
digits. The example given,
> 211496.26
gives, safely, a margin of 6 order of magnitude. If the application is
taking sums of 100's of thousands of dollars, it will stay accurate to
within a penny using floating point until there are millions of
entries:
10^15 ÷ 10^8 = 10^7
I doubt the financial application exists that sums milliions of entries
AND cares about the last penny.
I've seen advice about using integer arithmetic and implied decimal
points in textbooks. It's convenient in languages like Cobol, that
support it. In languages like C, floating point is too convenient 
and accurate  to ignore.
I'm sure banks have regulations and approved rounding algorithms.
In decades of programming on Wall Street, though, we used floating
point for everything. The only problems I remember involved matching
results between systems when porting: the differences were
insignificant, but because they were visible they had to be explained.
That always took some work.
jkl
_______________________________________________
sqliteusers mailing list
[hidden email]
http://mailinglists.sqlite.org/cgibin/mailman/listinfo/sqliteusers


On 17 Dec 2018, at 5:16pm, James K. Lowden < [hidden email]> wrote:
> IEEE
> doubleprecision floating point is accurate to within 15 decimal
> digits.
First, the problem is not storage it's calculation.
Second, the thread was started because a floating point calculation in SQLite, exactly as it is run today, led to the following value:
211496.25999999992
which is typical of such problems. Please don't consider that there's no problem until you have solved that problem.
Simon.
_______________________________________________
sqliteusers mailing list
[hidden email]
http://mailinglists.sqlite.org/cgibin/mailman/listinfo/sqliteusers


The "nearest" representation of 211496.26 is 211496.26000000000931323. The two representable IEEE754 double precision floating point numbers bounding 211496.26 are:
211496.26000000000931323
211496.2599999999802094
The difference between 211496.25999999992 (which is itself a truncated representation  the actual stored value should presumably be 211496.25999999992200173) and 211496.26000000000931323 is 8.73114913702011e11, or 3 ULP.
Applying halfeven rounding at the second "decimal place" to 211496.25999999992 (which when multiplied by 100 is 21149625.9999999925494194 which halfeven rounds to 21149626.0 which when divided by 100 is 211496.26000000000931323 or exactly the "nearest representable IEEE754 double precision floating point number" to 211496.26

The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume.
>Original Message
>From: sqliteusers [mailto:sqliteusers
> [hidden email]] On Behalf Of Simon Slavin
>Sent: Monday, 17 December, 2018 10:36
>To: SQLite mailing list
>Subject: Re: [sqlite] Question about floating point
>
>On 17 Dec 2018, at 5:16pm, James K. Lowden < [hidden email]>
>wrote:
>
>> IEEE
>> doubleprecision floating point is accurate to within 15 decimal
>> digits.
>
>First, the problem is not storage it's calculation.
>
>Second, the thread was started because a floating point calculation
>in SQLite, exactly as it is run today, led to the following value:
>
>211496.25999999992
>
>which is typical of such problems. Please don't consider that
>there's no problem until you have solved that problem.
>
>Simon.
>_______________________________________________
>sqliteusers mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgibin/mailman/listinfo/sqliteusers_______________________________________________
sqliteusers mailing list
[hidden email]
http://mailinglists.sqlite.org/cgibin/mailman/listinfo/sqliteusers


> On Dec 17, 2018, at 1:12 PM, Keith Medcalf < [hidden email]> wrote:
>
>
> The "nearest" representation of 211496.26 is 211496.26000000000931323. The two representable IEEE754 double precision floating point numbers bounding 211496.26 are:
>
> 211496.26000000000931323
> 211496.2599999999802094
>
> The difference between 211496.25999999992 (which is itself a truncated representation  the actual stored value should presumably be 211496.25999999992200173) and 211496.26000000000931323 is 8.73114913702011e11, or 3 ULP.
>
> Applying halfeven rounding at the second "decimal place" to 211496.25999999992 (which when multiplied by 100 is 21149625.9999999925494194 which halfeven rounds to 21149626.0 which when divided by 100 is 211496.26000000000931323 or exactly the "nearest representable IEEE754 double precision floating point number" to 211496.26
Which brings up an important point…. When saying "doubleprecision floating point is accurate to within 15 decimal digits” it does NOT mean floating point can perfectly represent any number up to 15 digits long, it means that the delta between intent and representation is 15 digits smaller than the largest represented digit. In this case, the delta is 10 digits under the radix point, plus another 5 or 6 digits from the other side of the radix point.
In short, the problem _is_ storage… or at least representation. And that’s before calculations muck everything up further.
j
>> On 17 Dec 2018, at 5:16pm, James K. Lowden < [hidden email]>
>> wrote:
>>
>>> IEEE
>>> doubleprecision floating point is accurate to within 15 decimal
>>> digits.
>>
>> First, the problem is not storage it's calculation.
>>
_______________________________________________
sqliteusers mailing list
[hidden email]
http://mailinglists.sqlite.org/cgibin/mailman/listinfo/sqliteusers


On Monday, 17 December, 2018 12:27, Jay Kreibich < [hidden email]> wrote:
>> On Dec 17, 2018, at 1:12 PM, Keith Medcalf < [hidden email]> wrote:
>> The "nearest" representation of 211496.26 is
>> 211496.26000000000931323. The two representable IEEE754 double
>> precision floating point numbers bounding 211496.26 are:
>> 211496.26000000000931323
>> 211496.2599999999802094
>> The difference between 211496.25999999992 (which is itself a
>> truncated representation  the actual stored value should presumably
>> be 211496.25999999992200173) and 211496.26000000000931323 is
>> 8.73114913702011e11, or 3 ULP.
>> Applying halfeven rounding at the second "decimal place" to
>> 211496.25999999992 (which when multiplied by 100 is
>> 21149625.9999999925494194 which halfeven rounds to 21149626.0 which
>> when divided by 100 is 211496.26000000000931323 or exactly the
>> "nearest representable IEEE754 double precision floating point
>> number" to 211496.26
> Which brings up an important point…. When saying "doubleprecision
> floating point is accurate to within 15 decimal digits” it does NOT
> mean floating point can perfectly represent any number up to 15
> digits long, it means that the delta between intent and
> representation is 15 digits smaller than the largest represented
> digit. In this case, the delta is 10 digits under the radix point,
> plus another 5 or 6 digits from the other side of the radix point.
> In short, the problem _is_ storage… or at least representation. And
> that’s before calculations muck everything up further.
Which is why:
sqlite> create table x(x);
sqlite> insert into x values(1.0);
sqlite> insert into x values(1.0e100);
sqlite> insert into x values(1.0);
sqlite> insert into x values(1.0e100);
sqlite> select sum(x) from x;
0.0
but
sqlite> select sum(x) from (select x from x order by abs(x) desc);
2.0

The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume.
_______________________________________________
sqliteusers mailing list
[hidden email]
http://mailinglists.sqlite.org/cgibin/mailman/listinfo/sqliteusers


On 20181217 9:16 AM, James K. Lowden wrote:
> On Sat, 15 Dec 2018 01:24:18 0800
> Darren Duncan wrote:
>> If yours is a financial application then you should be using exact
>> numeric types only
>
> Color me skeptical. That very much depends on the application. IEEE
> doubleprecision floating point is accurate to within 15 decimal
> digits. The example given,
>
>> 211496.26
>
> gives, safely, a margin of 6 order of magnitude. If the application is
> taking sums of 100's of thousands of dollars, it will stay accurate to
> within a penny using floating point until there are millions of
> entries:
>
> 10^15 ÷ 10^8 = 10^7
>
> I doubt the financial application exists that sums milliions of entries
> AND cares about the last penny.
In modern days, getting to millions of financial transaction entries in a short
time is common, and we still care about the last penny, I know from experience.
 Darren Duncan
_______________________________________________
sqliteusers mailing list
[hidden email]
http://mailinglists.sqlite.org/cgibin/mailman/listinfo/sqliteusers


Ok, as there seem to be some experts about floatingpoint numbers here, there is one aspect that I never understood:
floats are stored as a fractional part, which is binary encoded, and an integertype exponent. The first leads to the famous rounding errors as there is no exact representation of most fractions.
Can someone explain to me why it has been defined this way? Having 1 bit sign, 11 bit exponent, and 52 bit mantissa, I would have stored the (in the meantime well known) number 211496.26 as 21149626E2, i.e. I would have stored a 52 bit integer number and appropriate exponent. This way there should be no rounding errors and one would always have a guaranteed precision of ~15 significant digits.
 Original Message 
From: Keith Medcalf < [hidden email]>
To: SQLite mailing list < [hidden email]>
Sent: Monday, December 17, 2018, 20:12:29
Subject: [sqlite] Question about floating point
The "nearest" representation of 211496.26 is 211496.26000000000931323. The two representable IEEE754 double precision floating point numbers bounding 211496.26 are:
211496.26000000000931323
211496.2599999999802094
The difference between 211496.25999999992 (which is itself a truncated representation  the actual stored value should presumably be 211496.25999999992200173) and 211496.26000000000931323 is 8.73114913702011e11, or 3 ULP.
Applying halfeven rounding at the second "decimal place" to 211496.25999999992 (which when multiplied by 100 is 21149625.9999999925494194 which halfeven rounds to 21149626.0 which when divided by 100 is 211496.26000000000931323 or exactly the "nearest representable IEEE754 double precision floating point number" to 211496.26

The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume.
>Original Message
>From: sqliteusers [mailto:sqliteusers
> [hidden email]] On Behalf Of Simon Slavin
>Sent: Monday, 17 December, 2018 10:36
>To: SQLite mailing list
>Subject: Re: [sqlite] Question about floating point
>On 17 Dec 2018, at 5:16pm, James K. Lowden < [hidden email]>
>wrote:
>> IEEE
>> doubleprecision floating point is accurate to within 15 decimal
>> digits.
>First, the problem is not storage it's calculation.
>Second, the thread was started because a floating point calculation
>in SQLite, exactly as it is run today, led to the following value:
>211496.25999999992
>which is typical of such problems. Please don't consider that
>there's no problem until you have solved that problem.
>Simon.
>_______________________________________________
>sqliteusers mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgibin/mailman/listinfo/sqliteusers_______________________________________________
sqliteusers mailing list
[hidden email]
http://mailinglists.sqlite.org/cgibin/mailman/listinfo/sqliteusers_______________________________________________
sqliteusers mailing list
[hidden email]
http://mailinglists.sqlite.org/cgibin/mailman/listinfo/sqliteusers


On 12/17/18 3:19 PM, Darren Duncan wrote:
> On 20181217 9:16 AM, James K. Lowden wrote:
>> On Sat, 15 Dec 2018 01:24:18 0800
>> Darren Duncan wrote:
>>> If yours is a financial application then you should be using exact
>>> numeric types only
>>
>> Color me skeptical. That very much depends on the application. IEEE
>> doubleprecision floating point is accurate to within 15 decimal
>> digits. The example given,
This thread is getting out of hand. Firstly there is no such binary
representation ( in this universe ) for a trivial decimal number such as
one tenth ( 0.10 ) and really folks should refer to the text book
recently published ( 2nd Edition actually ) where all this is covered :
Handbook of FloatingPoint Arithmetic
Authors: Muller, J.M., Brunie, N., de Dinechin, F.,
Jeannerod, C.P., Joldes, M., Lefèvre, V.,
Melquiond, G., Revol, N., Torres, S.
This handbook is a definitive guide to the effective use of
modern floatingpoint arithmetic, which has considerably evolved,
from the frequently inconsistent floatingpoint number systems of
early computing to the recent IEEE 7542008 standard.
I reviewed this chapter by chapter and have looked over the code and if
people were to study the actual mathematics then this whole discussion
would be moot. Okay ... so enough is enough here.
Dennis Clarke
_______________________________________________
sqliteusers mailing list
[hidden email]
http://mailinglists.sqlite.org/cgibin/mailman/listinfo/sqliteusers


> On Dec 17, 2018, at 1:50 PM, Thomas Kurz < [hidden email]> wrote:
>
> Can someone explain to me why it has been defined this way? Having 1 bit sign, 11 bit exponent, and 52 bit mantissa, I would have stored the (in the meantime well known) number 211496.26 as 21149626E2, i.e. I would have stored a 52 bit integer number and appropriate exponent. This way there should be no rounding errors and one would always have a guaranteed precision of ~15 significant digits.
You’re asking why we don’t use base10 exponents instead of base2? It’s because calculations on such numbers are much slower and more complex than binary. (You might as well ask why integers are stored as binary instead of BCD!)
—Jens
_______________________________________________
sqliteusers mailing list
[hidden email]
http://mailinglists.sqlite.org/cgibin/mailman/listinfo/sqliteusers


On Mon, 17 Dec 2018 17:35:54 +0000
Simon Slavin < [hidden email]> wrote:
> On 17 Dec 2018, at 5:16pm, James K. Lowden < [hidden email]>
> wrote:
>
> > IEEE
> > doubleprecision floating point is accurate to within 15 decimal
> > digits.
>
> First, the problem is not storage it's calculation.
>
> Second, the thread was started because a floating point calculation
> in SQLite, exactly as it is run today, led to the following value:
>
> 211496.25999999992
>
> which is typical of such problems.
What problem? Rounded to the number of significant digits  2 decimal
places in the input  the number is correct.
jkl
_______________________________________________
sqliteusers mailing list
[hidden email]
http://mailinglists.sqlite.org/cgibin/mailman/listinfo/sqliteusers


>This thread is getting out of hand. Firstly there is no such binary
>representation ( in this universe ) for a trivial decimal number such
>as one tenth ( 0.10 ) and really folks should refer to the text book
>recently published ( 2nd Edition actually ) where all this is covered
>:
> Handbook of FloatingPoint Arithmetic
> Authors: Muller, J.M., Brunie, N., de Dinechin, F.,
> Jeannerod, C.P., Joldes, M., Lefèvre, V.,
> Melquiond, G., Revol, N., Torres, S.
>
> This handbook is a definitive guide to the effective use of
> modern floatingpoint arithmetic, which has considerably
> evolved, from the frequently inconsistent floatingpoint number
> systems of early computing to the recent IEEE 7542008 standard.
https://doc.lagout.org/science/0_Computer%20Science/3_Theory/Handbook%20of%20Floating%20Point%20Arithmetic.pdfWhile it is true there is no exact representation of 1/10th in binary floating point, at double precision the epsilon is 1.3877787807814457e17 which means that for all intents and purposes 1/10th is exact to 16.9 decimal places. Which is pretty damn good for a format that is only purported to be accurate to 15 decimal digits.

The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume.
_______________________________________________
sqliteusers mailing list
[hidden email]
http://mailinglists.sqlite.org/cgibin/mailman/listinfo/sqliteusers


On 2018/12/17 11:53 PM, Dennis Clarke wrote:
>
> This thread is getting out of hand. Firstly there is no such binary
> representation ( in this universe ) for a trivial decimal number such as
> one tenth ( 0.10 ) and really folks should refer to the text book
> recently published ( 2nd Edition actually ) where all this is covered
> : //....
My good man, did the discussion really irritate you that much? I'm truly
sorry to hear that, but I'd like to offer as consolation that it's
probably enlightening to most others.
If we follow the proposed rationale above, we could replace this entire
forum with a single web page sporting some links to the very great SQL,
DB, and SQLite books out there, then nobody need discuss anything.
Of course, we know that believing any one book blindly also doesn't lead
to complete knowledge, so perhaps discussion is not as evil (especially
the kind containing varied views) and we should keep at it?
I hope in that light you will join us in enjoying the rest of this
thread, wherever it may lead.
_______________________________________________
sqliteusers mailing list
[hidden email]
http://mailinglists.sqlite.org/cgibin/mailman/listinfo/sqliteusers

123
