Re: [sqlite] Question about floating point

[Peter da Silva]

That wasn't "endian" argument, this is an arithmetic operation question,
avoiding extra operations in the common case of small (mag < 2^56)
operands. Since I posted that I've figured out some other optimizations
that work better the way they laid it out, and it makes more sense now.



On Thu, Jan 3, 2019 at 5:54 AM Gary R. Schmidt  wrote:

> On 03/01/2019 22:22, Peter da Silva wrote:
> > Why is the exponent in the low bits, since it forces unnecessary shifts
> for
> > integer operations?
> >
> That's easy, because the high bits are closer to the barrel shifter, so
> it takes less time for the electron to get there![1][2]
>
> Cheers,
> GaryB-)
>
> 1 - Oh ghod, bit-order arguments, worse than the shell wars and the
> editor wars combined!!!
>
> 2 - Sarcasm intended, for those who may not be certain.
> ___
> 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

Re: [sqlite] Question about floating point

[Gary R. Schmidt]

On 03/01/2019 22:22, Peter da Silva wrote:

Why is the exponent in the low bits, since it forces unnecessary shifts for
integer operations?

That's easy, because the high bits are closer to the barrel shifter, so 
it takes less time for the electron to get there![1][2]


Cheers,
GaryB-)

1 - Oh ghod, bit-order arguments, worse than the shell wars and the 
editor wars combined!!!


2 - Sarcasm intended, for those who may not be certain.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Question about floating point

[Peter da Silva]

Why is the exponent in the low bits, since it forces unnecessary shifts for
integer operations?

On Thu., 3 Jan. 2019, 03:34 Pavlos Christoforou  Happy new year all,
>
> Have not followed the full thread, in case it has not been mentioned
> already:
>
> http://dec64.com/
>
>
> Cheers
>
>
> On Tue, 18 Dec 2018 at 02:42, Keith Medcalf  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
> > >:
> > > Handbook of Floating-Point 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 floating-point arithmetic, which has considerably
> > > evolved, from the frequently inconsistent floating-point number
> > > systems of early computing to the recent IEEE 754-2008 standard.
> >
> >
> >
> https://doc.lagout.org/science/0_Computer%20Science/3_Theory/Handbook%20of%20Floating%20Point%20Arithmetic.pdf
> >
> > While it is true there is no exact representation of 1/10th in binary
> > floating point, at double precision the epsilon is 1.3877787807814457e-17
> > 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.
> >
> >
> >
> >
> > ___
> > sqlite-users mailing list
> > sqlite-users@mailinglists.sqlite.org
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >
>
>
> --
> Pavlos Christoforou
>
> Point Nine Limited
> Mobile: +357 99 160960
>
> pavlos.christofo...@p9ft.com
> ___
> 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

Re: [sqlite] Question about floating point

[Pavlos Christoforou]

Happy new year all,

Have not followed the full thread, in case it has not been mentioned
already:

http://dec64.com/


Cheers


On Tue, 18 Dec 2018 at 02:42, Keith Medcalf  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
> >:
> > Handbook of Floating-Point 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 floating-point arithmetic, which has considerably
> > evolved, from the frequently inconsistent floating-point number
> > systems of early computing to the recent IEEE 754-2008 standard.
>
>
> https://doc.lagout.org/science/0_Computer%20Science/3_Theory/Handbook%20of%20Floating%20Point%20Arithmetic.pdf
>
> While it is true there is no exact representation of 1/10th in binary
> floating point, at double precision the epsilon is 1.3877787807814457e-17
> 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.
>
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>


-- 
Pavlos Christoforou

Point Nine Limited
Mobile: +357 99 160960

pavlos.christofo...@p9ft.com
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Question about floating point

[Rowan Worth]

On Sat, 15 Dec 2018 at 15:10, Frank Millman  wrote:

> On Dec 15, 2018, at 08.58, Jay Kreibich wrote:
>
> > > On Dec 15, 2018, at 12:49 AM, Frank Millman 
> wrote:
> > >
> > > I know that floating point is not precise and not suitable for
> financial uses. Even so, I am curious about the following -
> > >
> [...]
> > >
> > > With the same version of sqlite3 and the same select statement, why
> does python return a different result from sqlite3.exe?
> >
> > Because the shell is altering the output to make it easier to read.
> Consider:
> >
> > sqlite> select 211496.252;
> > 211496.26
>

I just wanted to point out that python does the same thing (as does
basically every floating point display routine):

$ python
Python 2.7.5 (default, Aug  4 2017, 00:39:18)
[GCC 4.8.5 20150623 (Red Hat 4.8.5-16)] on linux2
>>> a=211496.252
>>> a
211496.252
>>> print a
211496.26
>>> repr(a)
'211496.252'
>>> str(a)
'211496.26'

It's just that the python interpreter outputs the "representation" by
default.

(Interesting thread; wow at the uₙ = 111 - 1130/uₙ₋₁ + 3000/(uₙ₋₁·uₙ₋₂)
sequence!)
-Rowan
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Question about floating point

[Warren Young]

On Dec 20, 2018, at 3:38 PM, Igor Tandetnik  wrote:
> 
> On 12/20/2018 1:34 PM, Dennis Clarke wrote:
>> A more interesting topic of discussion would be the speed and complexity
>> of circuitry designed for another number base such as 5 or even decimal.
> 
> https://en.wikipedia.org/wiki/Ternary_computer

Several of the early electronic stored program computers used decimal 
representation, including the ENIAC:

https://en.wikipedia.org/wiki/Decimal_computer#Early_computers
https://en.wikipedia.org/wiki/ENIAC#Comparison_with_other_early_computers
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Question about floating point

[Igor Tandetnik]

On 12/20/2018 1:34 PM, Dennis Clarke wrote:

A more interesting topic of discussion would be the speed and complexity
of circuitry designed for another number base such as 5 or even decimal.


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

--
Igor Tandetnik

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Question about floating point

[Dennis Clarke]

On 12/19/18 7:51 PM, James K. Lowden wrote:

On Tue, 18 Dec 2018 17:34:29 -0500
Dennis Clarke  wrote:


some serious reading and experiments are needed to get a good
handle on why numerical computation is as much art as it is science.
If we wander into the problem without sufficient study and VERY
careful consideration then we are doomed to repeat the errors of the
past.


I think perhaps you left out "Numerical Methods for Scientists and
Engineers", by Richard Hamming.  :-)


I figure that if you are in the industry and have any experience at all
then you know that old gem by heart.

A more interesting topic of discussion would be the speed and complexity
of circuitry designed for another number base such as 5 or even decimal.
All of our collective experience over the past fifty years has been
marching towards ever more effective digital logic for our de facto
standard binary using the usual circuit assumptions and Moore's law has
been profitable to so many corporations. Anyone can manufacture a 15nm
product with $15M thus here we are in 2018 looking at verilog designs
and 9nm multi-layer manufacturing for the next decade.  We are caught
collectively in the economics and thinking of binary. So we should look
away from that for a moment. I do NOT mean the usual undergrad 4 bit
adder logic which we all know entirely too well. Let's go lower down
into the switch design that shall render sensor logic with at least ten
voltage levels and perhaps, in my own ideas, twelve for guard logic at
either end of the range.  The real issue on the table initially would be
transducer slew rates and noise control. Anyway, this is all off topic
at this point.

Dennis Clarke

ps: feel free to see https://www.youtube.com/watch?v=AOC7KmHvx9w
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Question about floating point

[James K. Lowden]

On Tue, 18 Dec 2018 17:34:29 -0500
Dennis Clarke  wrote:

> some serious reading and experiments are needed to get a good
> handle on why numerical computation is as much art as it is science.
> If we wander into the problem without sufficient study and VERY
> careful consideration then we are doomed to repeat the errors of the
> past. 

I think perhaps you left out "Numerical Methods for Scientists and
Engineers", by Richard Hamming.  :-)  

But when you boil it down, the answer is there is no answer, is there?
The best advice is to understand where things can go wrong, and stay
away from them.  

The truth is that any system for representing numbers is forced to
represent some numbers approximately.  

We think "pen and paper" and the good old decimal system is the gold
standard, but what of ? ?  Even bankers, ever counting pennies,
approximate to compute interest and averages.  Little known fact:
sometimes they compute interest on the basis of a 360-day year.  

--jkl


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Question about floating point

[Gerry Snyder]

On Wed, Dec 19, 2018 at 4:57 PM Keith Medcalf  wrote:

>
>
> >All I meant was that with a decimal exponent, the units could be
> >dollars,
> >and additions and subtractions of cents would be exact (assuming the
> >mantissa has enough bits), with no worries about rounding. Which is
> >the
> >basis for this whole discussion.
>
> This is called fixed point.  All that is required is that you keep track
> of the decimal point yourself...sort of like using a slide rule.
>

Er, no. Not at all.

It's called floating point with a decimal exponent. Calculations in cents
come out exact*, calculations in mils come out exact*, and the machine
keeps track of the decimal point.

* Assuming that the input numbers are integer cents (exponent -2) or mils
(exponent -3)
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Question about floating point

[Keith Medcalf]

>All I meant was that with a decimal exponent, the units could be
>dollars,
>and additions and subtractions of cents would be exact (assuming the
>mantissa has enough bits), with no worries about rounding. Which is
>the
>basis for this whole discussion.

This is called fixed point.  All that is required is that you keep track of the 
decimal point yourself...sort of like using a slide rule.

---
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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Question about floating point

[Gerry Snyder]

On Mon, Dec 17, 2018 at 10:04 AM Larry Brasfield 
wrote:

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


All I meant was that with a decimal exponent, the units could be dollars,
and additions and subtractions of cents would be exact (assuming the
mantissa has enough bits), with no worries about rounding. Which is the
basis for this whole discussion.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Question about floating point

[Dennis Clarke]

Apologies ... I should have included a link to Jean-Michel Muller's work
on "Elementary Functions" and on preserving monotonicity and always
getting correctly rounded results when implementing the elementary
functions in floating-point arithmetic.

https://link.springer.com/book/10.1007/978-1-4899-7983-4


Also an interesting read in IEEE Transactions on Computers, Vol 66, 
Issue 12 : Exponential Sums and Correctly-Rounded Functions.


https://ieeexplore.ieee.org/document/7891945




___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Question about floating point

[Dennis Clarke]

On 12/18/18 6:01 AM, R Smith wrote:> 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 ...
[WARNING : written with a smile ]

Well, I guess the real issue is that I see fairly baseline stuff going
in circles over and over and over and over and very little clarity. I
merely posted that textbook because it really was written by the experts
and I have done a fair amount of emails in life back and forth to a few
of the authors on various topics who always cleared the air. They really
are the experts and the only name missing from that list is the great
and dreaded William Kahan himself.  I say that with a smile as Professor
Emeritus of Mathematics Kahan is well known to write very bluntly about
people who have not a clue about trivial things. Trivial to him. The
rest of us merely try to catch up and get a solid understanding of the
basics which, as I was saying, have been covered over and over and over
and over in circles over and over and it gets ... annoying to walk into
a store and hear Beethoven's Ninth Symphony played over dirty cheap
speakers.  Certainly when I have heard live performances a few times in
my life. Very irritating is the word.

I see this sort of thing happen from time to time and I have to take the
approach of 'care' or 'do not care'.  In the case of the store with bad
speakers the option is 'do not care' and simply accept the noise. In the
case were good people can be led down a wrong path and then fall into a
pitfall or trap I feel 'care' happens.  I am not a sociopath nor some
old greybeard UNIX geek that merely enjoys retirement too much to 'care'
anymore.

So let's play a little game based on an early lesson from William Kahan
which will demonstrate how poorly floating point works when used with
nothing but blind trust in bit games.  Also we will assume that we are
going to play by the rules of his IEEE 754 and I may make a passing
reference to these three documents :

1 ) IEEE 754-2008 - IEEE Standard for Floating-Point Arithmetic
https://standards.ieee.org/standard/754-2008.html

2 ) Formal Verification of Floating-Point Hardware Design
https://www.springer.com/us/book/9783319955124

3 ) Handbook of Floating-Point Arithmetic
https://www.springer.com/us/book/9783319765259

4 ) The Art of Computer Programming
https://cs.stanford.edu/~knuth/taocp.html

5 ) Oral history interview with Donald E. Knuth
Charles Babbage Institute, 2001
https://conservancy.umn.edu/handle/11299/107413

6 ) How Futile are Mindless Assessments of Roundoff
in Floating-Point Computation ?
Prof William Kahan
https://people.eecs.berkeley.edu/~wkahan/Mindless.pdf

Also perhaps ISO/IEC/IEEE 60559:2011 and working group ISO/IEC 
JTC1/SC22/WG11 publications.


Let's begin with a quick and incomplete definition of "floating point"
data representation thus :

Given a radix B with precision p we express a 'floting point'
number in the format

[  (+/-)m_0 . m_1 m_2 m_3 ... m_(p-1)  ]  *  B^e

where we call e the exponent which is always an integer and the
expression m_0 . m_1  m_2 m_3 ... m_(p-1) will be called the not
so friendy word "significand" which is expressed in radix B.

A complete and formal definition will be found in chapter 3 of reference
(3) above. This is not a new idea and in fact is quite old. One may find
a fairly nice history of "floating point" in computing machines such as
the Babbage difference engine ( see Charles Babbage et. al. ) and other
machines that performed numerical computation in (4) and (5) above.
Suffice it to say that radix 60 mathematics was common in the Babylonian
history and the Yale Babylonian Collection provides a tablet with an
approximation of the positive square root of two with four sexagesimal
digits 1, 24, 51, 10.  Numerical data representation is not new at all
however I personally fell into the problem in while working on long term
trajectory computations with early Apollo systems. We had not yet been
able to establish a formal method to detect and handle numerical error
conditions and the much loved William Kahan provides us this rather
trivial example to illustrate:

Given   u_0 = 2  and   u_1 = -4   we then compute

u_[n] = 111 - 1130/(u[n-1]) + 3000/(u[n-1]*u[n-2])

where n > 1 clearly.


Trivial :


#include 
#include 
#include 

#define LOOPCNT 30

int main (int argc, char *argv[])
{
long double u[LOOPCNT];
int j;

u[0] = (long double)2.0L;
u[1] = (long double)-4.0L;

printf("u[00] = %+20.18Lf\n", u[0]);

Re: [sqlite] Question about floating point

[ajm]

Although the problem is well known by the readers, may be someone would like 
remember the basics (somethin written by me some years ago -in spanish-).
http://www.zator.com/Cpp/E2_2_4a.htm
If you want "play" whiths the numbers in IEE754 this page bay be the fun 
(unfortuately, the original is not longer available)
http://www.zator.com/Cpp/E2_2_4a1.htm

A.J. Millan

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Question about floating point

[Scott Robison]

On Mon, Dec 17, 2018 at 2:50 PM Thomas Kurz  wrote:

> Ok, as there seem to be some experts about floating-point numbers here,
> there is one aspect that I never understood:
>
> floats are stored as a fractional part, which is binary encoded, and an
> integer-type 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 21149626E-2, 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.
>

To get the maximum precision possible from a binary floating point number,
the designers of the format took advantage of the fact that all numbers
other than zero would have a 1 bit set somewhere in their representation.
To that end, "normal" floating point numbers actually have a 53 bit
mantissa. "But that equals 65 bits! You can't cram 65 bits into a 64 bit
word." But you can if the most significant set bit of the mantissa is
implied just to the left of the explicitly given 52 bits of the mantissa.
The most significant digit of a decimal number can be any value from 1
through 9, so you can't use this same trick to extend the precision of a
decimal floating point number.

In addition to normal numbers, there are subnormal numbers, where the left
most digit is implicitly a 0 bit. The value zero happens to be a subnormal
number with all bits set to zero.

Even without the implicit bit, many / most schemes for encoding decimal
digits in binary lose some portion of the range that is possible with
binary representations, and the IEEE designers wanted the best of both
worlds, range and precision, so they gave up exact decimal representation
in favor of binary.

Your approach of coding is what the decimal type does in the .net platform,
among other examples, but the available range is smaller than IEEE binary
floating point numbers of the same size.

As far as it goes, you can still have rounding errors that propagate with a
decimal scheme such as you suggest. Simply add 1/3 + 1/3 + 1/3 in a decimal
representation.

333E-15 + 333E-15 + 333E-15 =
999E-15. But it should be 1000E-15 (or 1E0). It
doesn't matter how many bits of precision you add, you can never do this
type of math exactly with decimal floating point numbers. Any time the
decimal expansion extends beyond the bit length of the available precision,
rounding choices are going to have to be made at some point, and some
calculation will be inexact.

Note: I am spouting from memory, so my apologies if I've gotten any
terminology wrong (such as subnormal vs denormal, so similar other ideas).

SDR
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Question about floating point

[Richard Damon]

On 12/18/18 6:21 AM, R Smith wrote:
>
> I'm not even going to touch on silly/stupid programming and
> calculations that round along the intermediate steps, those have been
> mentioned already, they are evil and it isn't the fault of the storage
> medium.

Actually, periodically rounding IS a valid method IF you know the
'precision' of the native numbers. For example, if you know that all the
numbers in a list supposed to be exact values to two decimals, as you
add them up the rounding error grows, and if the list is long enough,
can cause an error i those two decimal digits, but by periodically
rounding to that two decimals resets the rounding error.

The error is in doing this sort of rounding when the input numbers are
NOT known to be 'exact' to those two digits, but you round to that
precision. THAT rounding will increase the error in the calculation.

-- 
Richard Damon

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Question about floating point

[R Smith]

On 2018/12/18 1:21 AM, James K. Lowden wrote:



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

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.



Exactly, and I would go further to suggest that the problem is much more 
to do with the human brain and visual concepts than the numbers.


Sure enough, as a mathematician the numbers
211496.252 and
211496.26

look exactly the same to me (when I consider scales in the sub 15 digit 
range), as it does to an IEEE float, but you can see how, to the average 
onlooker's brain, those look vastly different - see them as pictures 
rather than numbers, which obviously look completely different - and 
this is what scares people and why this keeps being a problem.


I'm not even going to touch on silly/stupid programming and calculations 
that round along the intermediate steps, those have been mentioned 
already, they are evil and it isn't the fault of the storage medium.


The fact that the SQLite programmers and the Python programmers (or 
perhaps Python-SQLite-wrapper programmers) did not choose the same 
presentation is just one more case of "not the storage method's fault 
but indeed the interpretation's fault".



PS, a good video to cure you of "visual number deficit" sickness would 
be one of those explaining why 0.999... (recurring) is exactly equal to 
1. (And that's not even touching IEEE)

This be as good as any: https://www.youtube.com/watch?v=G_gUE74YVos


Cheers!
Ryan


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Question about floating point

[R Smith]

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.



___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Question about floating point

[Keith Medcalf]

>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 Floating-Point 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 floating-point arithmetic, which has considerably
> evolved, from the frequently inconsistent floating-point number 
> systems of early computing to the recent IEEE 754-2008 standard.

https://doc.lagout.org/science/0_Computer%20Science/3_Theory/Handbook%20of%20Floating%20Point%20Arithmetic.pdf

While it is true there is no exact representation of 1/10th in binary floating 
point, at double precision the epsilon is 1.3877787807814457e-17 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.




___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Question about floating point

[James K. Lowden]

On Mon, 17 Dec 2018 17:35:54 +
Simon Slavin  wrote:

> On 17 Dec 2018, at 5:16pm, James K. Lowden 
> wrote:
> 
> > IEEE
> > double-precision 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.252
> 
> 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
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Question about floating point

[Jens Alfke]

> On Dec 17, 2018, at 1:50 PM, Thomas Kurz  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 21149626E-2, 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 base-10 exponents instead of base-2? 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
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Question about floating point

[Tim Streater]

On 17 Dec 2018, at 21:50, Thomas Kurz  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 21149626E-2, 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.

Possibly:





-- 
Cheers  --  Tim
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Question about floating point

[Dennis Clarke]

On 12/17/18 3:19 PM, Darren Duncan wrote:

On 2018-12-17 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
double-precision 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 Floating-Point 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 floating-point arithmetic, which has considerably evolved,
from the frequently inconsistent floating-point number systems of
early computing to the recent IEEE 754-2008 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
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Question about floating point

[Thomas Kurz]

Ok, as there seem to be some experts about floating-point numbers here, there 
is one aspect that I never understood:

floats are stored as a fractional part, which is binary encoded, and an 
integer-type 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 21149626E-2, 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 
To: SQLite mailing list 
Sent: Monday, December 17, 2018, 20:12:29
Subject: [sqlite] Question about floating point


The "nearest" representation of 211496.26 is 211496.260931323.  The two 
representable IEEE-754 double precision floating point numbers bounding 
211496.26 are:

211496.260931323
211496.25802094

The difference between 211496.252 (which is itself a truncated 
representation -- the actual stored value should presumably be 
211496.252200173) and 211496.260931323 is 8.73114913702011e-11, 
or 3 ULP.

Applying half-even rounding at the second "decimal place" to 211496.252 
(which when multiplied by 100 is 21149625.25494194 which half-even 
rounds to 21149626.0 which when divided by 100 is 211496.260931323 or 
exactly the "nearest representable IEEE-754 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: sqlite-users [mailto:sqlite-users-
>boun...@mailinglists.sqlite.org] 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 
>wrote:

>> IEEE
>> double-precision 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.252

>which is typical of such problems.  Please don't consider that
>there's no problem until you have solved that problem.

>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

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Question about floating point

[Darren Duncan]

On 2018-12-17 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
double-precision 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

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Question about floating point

[Keith Medcalf]

On Monday, 17 December, 2018 12:27, Jay Kreibich  wrote:

>> On Dec 17, 2018, at 1:12 PM, Keith Medcalf  wrote:

>> The "nearest" representation of 211496.26 is
>> 211496.260931323.  The two representable IEEE-754 double
>> precision floating point numbers bounding 211496.26 are:

>> 211496.260931323
>> 211496.25802094

>> The difference between 211496.252 (which is itself a
>> truncated representation -- the actual stored value should presumably
>> be 211496.252200173) and 211496.260931323 is
>> 8.73114913702011e-11, or 3 ULP.

>> Applying half-even rounding at the second "decimal place" to
>> 211496.252 (which when multiplied by 100 is
>> 21149625.25494194 which half-even rounds to 21149626.0 which
>> when divided by 100 is 211496.260931323 or exactly the
>> "nearest representable IEEE-754 double precision floating point
>> number" to 211496.26

> Which brings up an important point…. When saying "double-precision
> 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.





___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Question about floating point

[Jay Kreibich]

> On Dec 17, 2018, at 1:12 PM, Keith Medcalf  wrote:
> 
> 
> The "nearest" representation of 211496.26 is 211496.260931323.  The 
> two representable IEEE-754 double precision floating point numbers bounding 
> 211496.26 are:
> 
> 211496.260931323
> 211496.25802094
> 
> The difference between 211496.252 (which is itself a truncated 
> representation -- the actual stored value should presumably be 
> 211496.252200173) and 211496.260931323 is 
> 8.73114913702011e-11, or 3 ULP.
> 
> Applying half-even rounding at the second "decimal place" to 
> 211496.252 (which when multiplied by 100 is 21149625.25494194 
> which half-even rounds to 21149626.0 which when divided by 100 is 
> 211496.260931323 or exactly the "nearest representable IEEE-754 
> double precision floating point number" to 211496.26


Which brings up an important point…. When saying "double-precision 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 
>> wrote:
>> 
>>> IEEE
>>> double-precision floating point is accurate to within 15 decimal
>>> digits.
>> 
>> First, the problem is not storage it's calculation.
>> 

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Question about floating point

[Keith Medcalf]

The "nearest" representation of 211496.26 is 211496.260931323.  The two 
representable IEEE-754 double precision floating point numbers bounding 
211496.26 are:

211496.260931323
211496.25802094

The difference between 211496.252 (which is itself a truncated 
representation -- the actual stored value should presumably be 
211496.252200173) and 211496.260931323 is 8.73114913702011e-11, 
or 3 ULP.

Applying half-even rounding at the second "decimal place" to 211496.252 
(which when multiplied by 100 is 21149625.25494194 which half-even 
rounds to 21149626.0 which when divided by 100 is 211496.260931323 or 
exactly the "nearest representable IEEE-754 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: sqlite-users [mailto:sqlite-users-
>boun...@mailinglists.sqlite.org] 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 
>wrote:
>
>> IEEE
>> double-precision 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.252
>
>which is typical of such problems.  Please don't consider that
>there's no problem until you have solved that problem.
>
>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

Re: [sqlite] Question about floating point

[Simon Slavin]

On 17 Dec 2018, at 5:16pm, James K. Lowden  wrote:

> IEEE
> double-precision 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.252

which is typical of such problems.  Please don't consider that there's no 
problem until you have solved that problem.

Simon.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Question about floating point

[James K. Lowden]

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
double-precision 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
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Question about floating point

[James K. Lowden]

On Sat, 15 Dec 2018 10:35:01 -0700
"Keith Medcalf"  wrote:

> And the propensity to apply intermediate rounding still exists.

Yes.  The only significant errors I've ever seen using floating point
were not due to the computer, but to the programmer.  

--jkl

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Question about floating point

[Larry Brasfield]

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 64-bit mode, 
however.  It would be interesting to know why not.

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Question about floating point

[Larry Brasfield]

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

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Question about floating point

[Gerry Snyder]

On Mon, Dec 17, 2018 at 2:43 AM Tim Streater  wrote:

> On 17 Dec 2018, at 04:32, D Burgess  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 high-order zeroes.

Gerry Snyder
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Question about floating point

[Tim Streater]

On 17 Dec 2018, at 04:32, D Burgess  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
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Question about floating point

[Digital Dog]

On Sun, Dec 16, 2018 at 9:26 PM Thomas Kurz  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
11

It seems int64 is easy overflown.

PostgreSQL's NUMERIC data type would be a solution here.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Question about floating point

[Darren Duncan]

On 2018-12-16 6:54 AM, Wout Mertens wrote:

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.


What you're talking about is NOT a problem with integer money.  Integers have 
unlimited precision, they are as large as you need them to be, there is no such 
thing as overflowing them.  When you're using a computer to represent the 
integers, you just use a data type capable of representing the largest integers 
you could ever possibly need to use for storage or intermediate calculations, if 
necessary a variable size representation such as BigInt or binary-coded-decimal. 
-- Darren Duncan

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Question about floating point

[D Burgess]

> 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.
>
>  Cheers,
>  GaryB-)
>
As do insurance companies and many in the manufacturing world (inventory).
There is a lot to like about BCD.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Question about floating point

[Darren Duncan]

On 2018-12-15 2:15 AM, Frank Millman wrote:

On 2018-12-14 11:24 AM, Darren Duncan wrote:

If yours is a financial application then you should be using exact numeric types

only, such as integers that represent multiples of whatever quantum you are
using, such as cents; fractional numbers are a display or user input format
only, and in those cases they are character strings.




Thanks, Darren. In principle I agree with you, but I am experimenting with a 
different approach.
My application supports SQL Server and PostgreSQL as well as sqlite3, and those 
databases do have exact numeric types for monetary use, and I am trying to 
stick to one code base as much as possible.
The python sqlite3 module allows you to convert sqlite3 data to a python 
object, so my approach is to store decimal data as text in sqlite3, and convert 
it to a python Decimal object when reading it in. I find that this works ok. I 
do have a bit of trouble when using sqlite3 to ‘sum’ a column, as it then 
switches to floating point and can lose precision, but provided I convert the 
result back to a Decimal object with appropriate rounding it also works.

Having said that, I am still testing, and I may decide that I have to bite the 
bullet and store everything as integers, in which case I will use the same 
approach for the other databases as well.

Simon Slavin says ‘Currency amounts should be stored as integers’. Does this 
apply to sqlite3 specifically, or is that your recommendation for all databases?


I think that can reasonably apply to all DBMSs, even ones supporting exact 
fractional numeric types.  Keeping the money as integers everywhere for storage 
or where you do math, and character strings only for display to users, is a 
reasonable consistent strategy, that also scales more easily to handling 
multiple currencies.  Its not just about the DBMSs.  Some programming languages 
don't support exact fractional numbers either, and Javascript doesn't on the web 
client side if you do that. -- Darren Duncan

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Question about floating point

[Gary R. Schmidt]

On 2018-12-17 02:41, Simon Slavin wrote:

On 16 Dec 2018, at 2:54pm, Wout Mertens  wrote:

imagine having to handle the Zimbabwean Dollar, which ended up having 
100 trillion dollar notes. Good way to overflow your integers.


Indeed.  But when the crisis started in the early 2000s, the currency
was devalued by 1000.  Then ten zeros were wiped out at a stroke.  And
then another twelve zeros were slashed to make the "fourth Zimbabwe
dollar".

So if you were going to keep track of an account with Z$1 old and Z$1
new you'd need a precision capable of keeping track through 3+10+12 =
25 zeros, or amounts like

10,000,000,000,000,000,000,000,001

The precision of double-precision floats, 52-bit significand, is 2^52
= 4.5e15.  Just to store one new Z$.  If someone has ten new dollars
in their account you could no longer keep their account even in
double-precision floating point.  You would have needed quad-precision
(2^112 = 5.2e33), and banks weren't using that at the time even to do
calculations.  (I don't know what they use now, I'm not in the
industry.)



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.


Cheers,
GaryB-)
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Question about floating point

[Wout Mertens]

Ah, the luxuries of not programming in JavaScript ;)

Anyway, using int64 would not have been sufficient to represent, say, tax
numbers for the country, especially if you worked with cents.



Whereas 53 bits of precision gets you a very long way and can even handle
deflation

The

Wout.

On Sun, Dec 16, 2018, 9:26 PM Thomas Kurz  > Good way to overflow your integers.
> > With floating point, that's not a problem.
>
> With int64, it shouldn't be a problem either.
>
> ___
> 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

Re: [sqlite] Question about floating point

[Thomas Kurz]

> Good way to overflow your integers.
> With floating point, that's not a problem.

With int64, it shouldn't be a problem either.

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Question about floating point

[Keith Medcalf]

Just remember however that you only have 53-bits of precision, so in floating 
point adding a "small" number (eg, 0.01) to a big number (1e16) the result will 
be 1e16 not 1.1e16 whereas with scaled decimal the result will 
be precise and that unless special precautions are taken that the floating 
point sum of 1.0, 1e100, 1.0, -1e100 is 0, not 2.0, because the result is in 
the scale of the largest input, not the smallest.

---
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: sqlite-users [mailto:sqlite-users-
>boun...@mailinglists.sqlite.org] On Behalf Of Wout Mertens
>Sent: Sunday, 16 December, 2018 07:55
>To: SQLite mailing list
>Subject: Re: [sqlite] Question about floating point
>
>On Sat, Dec 15, 2018 at 7:13 PM Keith Medcalf 
>wrote:
>
>>
>> >And yet ... here we are.  The post which started this thread
>summed
>> >currency amounts and reached a total of 211496.252 .
>>
>> >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 half-even 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 half-even
>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.
>___
>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

Re: [sqlite] Question about floating point

[Simon Slavin]

On 16 Dec 2018, at 2:54pm, Wout Mertens  wrote:

> imagine having to handle the Zimbabwean Dollar, which ended up having 100 
> trillion dollar notes. Good way to overflow your integers.

Indeed.  But when the crisis started in the early 2000s, the currency was 
devalued by 1000.  Then ten zeros were wiped out at a stroke.  And then another 
twelve zeros were slashed to make the "fourth Zimbabwe dollar".

So if you were going to keep track of an account with Z$1 old and Z$1 new you'd 
need a precision capable of keeping track through 3+10+12 = 25 zeros, or 
amounts like

10,000,000,000,000,000,000,000,001

The precision of double-precision floats, 52-bit significand, is 2^52 = 4.5e15. 
 Just to store one new Z$.  If someone has ten new dollars in their account you 
could no longer keep their account even in double-precision floating point.  
You would have needed quad-precision (2^112 = 5.2e33), and banks weren't using 
that at the time even to do calculations.  (I don't know what they use now, I'm 
not in the industry.)

Didn't matter, anyway, since Zim banks just agreed to truncate amounts less 
than newZ$0.01 .

Simon.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Question about floating point

[Wout Mertens]

On Sat, Dec 15, 2018 at 7:13 PM Keith Medcalf  wrote:

>
> >And yet ... here we are.  The post which started this thread summed
> >currency amounts and reached a total of 211496.252 .
>
> >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 half-even 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 half-even 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.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Question about floating point

[Keith Medcalf]

On Saturday, 15 December, 2018 10:54, Simon Slavin  wrote:
>On 15 Dec 2018, at 5:35pm, Keith Medcalf  wrote:

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

>And yet ... here we are.  The post which started this thread summed
>currency amounts and reached a total of 211496.252 .

>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 half-even 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 half-even rounding the 
accumulated errors will amount to quite a sum.

---
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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Question about floating point

[Simon Slavin]

On 15 Dec 2018, at 5:35pm, Keith Medcalf  wrote:

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

And yet ... here we are.  The post which started this thread summed currency 
amounts and reached a total of 211496.252 .

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

Simon.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Question about floating point

[Keith Medcalf]

On Friday, 14 December, 2018 23:49, Frank Millman  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. 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'")
>
 cur.fetchone()
>(211496.252,)

>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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Question about floating point

[Simon Slavin]

On 15 Dec 2018, at 10:15am, Frank Millman  wrote:

> Simon Slavin says ‘Currency amounts should be stored as integers’. Does this 
> apply to sqlite3 specifically, or is that your recommendation for all 
> databases?

For anything that goes near a computer.  I used to work with international 
banking software.  Any time we put out a tender for an interface and the bidder 
treated currencies as REAL we knew we'd be able to find a calculation which 
invisibly lost or gained a fraction. Even with division we could trip up their 
software.  It's an inherent problem with using binary operations on a base-10 
fraction.

The only time you treat currency units as real is when you're doing 
calculations which are inherently real.  As when you're given a yearly interest 
rate of 4% and want to know how much interest you'd get for 820 days.  And even 
with that you do the calculation and convert the answer to an integer, using 
whatever rounding/truncating algorithm your banking authority has approved.

It doesn't involve much less work once you've decided to do it.  The big 
problem occurs only when taking a system which was designed (possibly by 
someone else years ago) for fractions and converting all the data.

Simon.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Question about floating point

[Niall O'Reilly]

On 15 Dec 2018, at 10:15, Frank Millman wrote:

Simon Slavin says ‘Currency amounts should be stored as integers’. 
Does this apply to sqlite3 specifically, or is that your 
recommendation for all databases?


It doesn't matter whether a database is involved.

Using integers for currency amounts is long established as
the only way to do the arithmetic needed for accounting.
I'm not sure when I first learned this; it was surely more
than 40 years ago. I wish I could cite a reference.

You need to count farthings, pennies, centimes, millièmes,
or whatever the smallest denomination of the currency is,
and arrange appropriate display filtering.

In 1972 or so, I learned PL/I, which then had a "STERLING"
data type for representing sums as pounds, shillings, and
pence. I gather from what a quick web search shows of
current IBM documentation that this data type is no longer
(advertised as) supported.

Best regards,

Niall O'Reilly
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Question about floating point

[Frank Millman]

On 2018-12-14 11:24 AM, Darren Duncan wrote:

> 
> If yours is a financial application then you should be using exact numeric 
> types 
only, such as integers that represent multiples of whatever quantum you are 
using, such as cents; fractional numbers are a display or user input format 
only, and in those cases they are character strings.
> 

Thanks, Darren. In principle I agree with you, but I am experimenting with a 
different approach.
My application supports SQL Server and PostgreSQL as well as sqlite3, and those 
databases do have exact numeric types for monetary use, and I am trying to 
stick to one code base as much as possible.
The python sqlite3 module allows you to convert sqlite3 data to a python 
object, so my approach is to store decimal data as text in sqlite3, and convert 
it to a python Decimal object when reading it in. I find that this works ok. I 
do have a bit of trouble when using sqlite3 to ‘sum’ a column, as it then 
switches to floating point and can lose precision, but provided I convert the 
result back to a Decimal object with appropriate rounding it also works.

Having said that, I am still testing, and I may decide that I have to bite the 
bullet and store everything as integers, in which case I will use the same 
approach for the other databases as well.

Simon Slavin says ‘Currency amounts should be stored as integers’. Does this 
apply to sqlite3 specifically, or is that your recommendation for all databases?

Thanks

Frank
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Question about floating point

[Simon Slavin]

On 15 Dec 2018, at 9:24am, Darren Duncan  wrote:

> If yours is a financial application then you should be using exact numeric 
> types only, such as integers that represent multiples of whatever quantum you 
> are using, such as cents; fractional numbers are a display or user input 
> format only, and in those cases they are character strings.

This.  Currency amounts should be stored as integers.  The problem you spotted 
is just one of many bad consequences of using floating arithmetic on 
currencies.  Please please consider changing the way your database works.

Simon.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Question about floating point

[Darren Duncan]

If yours is a financial application then you should be using exact numeric types 
only, such as integers that represent multiples of whatever quantum you are 
using, such as cents; fractional numbers are a display or user input format 
only, and in those cases they are character strings. -- Darren Duncan


On 2018-12-14 10:49 PM, Frank Millman wrote:

Hi all

I know that floating point is not precise and not suitable for financial uses. 
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'")



cur.fetchone()

(211496.252,)

With the same version of sqlite3 and the same select statement, why does python 
return a different result from sqlite3.exe?

Thanks

Frank Millman

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Question about floating point

[Frank Millman]

On Dec 15, 2018, at 08.58, Jay Kreibich wrote:

> 
> > On Dec 15, 2018, at 12:49 AM, Frank Millman  wrote:
> > 
> > I know that floating point is not precise and not suitable for financial 
> > uses. Even so, I am curious about the following -
> > 
[...]
> > 
> > With the same version of sqlite3 and the same select statement, why does 
> > python return a different result from sqlite3.exe?
> 
> Because the shell is altering the output to make it easier to read.  Consider:
> 
> $ sqlite3
> 
> SQLite version 3.16.0 2016-11-04 19:09:39
> 
> Enter ".help" for usage hints.
> 
> Connected to a transient in-memory database.
> 
> Use ".open FILENAME" to reopen on a persistent database.
> sqlite> select 211496.252;
> 211496.26
> 

 That makes sense.

Thanks, Jay
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Question about floating point

[Jay Kreibich]

> On Dec 15, 2018, at 12:49 AM, Frank Millman  wrote:
> 
> Hi all
> 
> I know that floating point is not precise and not suitable for financial 
> uses. 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'")
> 
 cur.fetchone()
> (211496.252,)
> 
> With the same version of sqlite3 and the same select statement, why does 
> python return a different result from sqlite3.exe?


Because the shell is altering the output to make it easier to read.  Consider:

$ sqlite3
SQLite version 3.16.0 2016-11-04 19:09:39
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> select 211496.252;
211496.26


-j



___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Question about floating point

[Frank Millman]

Hi all

I know that floating point is not precise and not suitable for financial uses. 
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'")

>>> cur.fetchone()
(211496.252,)

With the same version of sqlite3 and the same select statement, why does python 
return a different result from sqlite3.exe?

Thanks

Frank Millman
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users