Simple Math Question

classic Classic list List threaded Threaded
75 messages Options
1234
Reply | Threaded
Open this post in threaded view
|

Simple Math Question

RichardR
Doing the following math, why is it that the results are not all returning "yes"?

SELECT
                (9.2+7.9+0+4.0+2.6+1.3),
                case when (9.2+7.9+0+4.0+2.6+1.3)=25.0 then "yes" else "no" end,
                (9.2+7.8+0+3.0+1.3+1.7),
                case when (9.2+7.8+0+3.0+1.3+1.7)=23.0 then "yes" else "no" end,
                (9.2+7.9+0+1.0+1.3+1.6),
                case when (9.2+7.9+0+1.0+1.3+1.6)=21.0 then "yes" else "no" end
FROM
                sometable;

Result
25.0 no 23.0 yes 21.0 no

I'm sure this has an obvious answer but it isn't jumping out at me.

Richard

This communication is the property of CenturyLink and may contain confidential or privileged information. Unauthorized use of this communication is strictly prohibited and may be unlawful. If you have received this communication in error, please immediately notify the sender by reply e-mail and destroy all copies of the communication and any attachments.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Simple Math Question

Stephan Beal-3
On Thu, Oct 22, 2015 at 9:45 PM, Rousselot, Richard A <
[hidden email]> wrote:

> Doing the following math, why is it that the results are not all returning
> "yes"?
>
> SELECT
>                 (9.2+7.9+0+4.0+2.6+1.3),
>                 case when (9.2+7.9+0+4.0+2.6+1.3)=25.0 then "yes" else
> "no" end,
>                 (9.2+7.8+0+3.0+1.3+1.7),
>                 case when (9.2+7.8+0+3.0+1.3+1.7)=23.0 then "yes" else
> "no" end,
>                 (9.2+7.9+0+1.0+1.3+1.6),
>                 case when (9.2+7.9+0+1.0+1.3+1.6)=21.0 then "yes" else
> "no" end
> FROM
>                 sometable;
>

Try the same with integers as you'll likely see different results.
Floating-point math is fraught with problems when it comes to expecting
exact results at a specific precision.

See: http://floating-point-gui.de/

the first example of which demonstrates the problem you are seeing.


--
----- stephan beal
http://wanderinghorse.net/home/stephan/
http://gplus.to/sgbeal
"Freedom is sloppy. But since tyranny's the only guaranteed byproduct of
those who insist on a perfect world, freedom will have to do." -- Bigby Wolf
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Simple Math Question

Igor Tandetnik-2
In reply to this post by RichardR
On 10/22/2015 3:45 PM, Rousselot, Richard A wrote:
> Doing the following math, why is it that the results are not all returning "yes"?

For the same reason that 0.3333333333 + 0.3333333333 + 0.3333333333 !=
1.   0.3333333333 is only approximately equal to 1/3, and the error
tends to accumulate.

See also: https://docs.oracle.com/cd/E19957-01/806-3568/ncg_goldberg.html
--
Igor Tandetnik

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

Re: Simple Math Question

RichardR
In reply to this post by Stephan Beal-3
I fully expected something like this from a fractional number like 1/3 but didn't realize that this would be the case if when the digits were explicitly stated and they summed to a whole number. I will use a Cast statement to resolve.

FWIW, MySQL and Oracle both return all yes for that query.

Thanks to everyone that replied.

Richard

-----Original Message-----
From: [hidden email] [mailto:[hidden email]] On Behalf Of Stephan Beal
Sent: Thursday, October 22, 2015 2:57 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Simple Math Question

On Thu, Oct 22, 2015 at 9:45 PM, Rousselot, Richard A < [hidden email]> wrote:

> Doing the following math, why is it that the results are not all
> returning "yes"?
>
> SELECT
>                 (9.2+7.9+0+4.0+2.6+1.3),
>                 case when (9.2+7.9+0+4.0+2.6+1.3)=25.0 then "yes" else
> "no" end,
>                 (9.2+7.8+0+3.0+1.3+1.7),
>                 case when (9.2+7.8+0+3.0+1.3+1.7)=23.0 then "yes" else
> "no" end,
>                 (9.2+7.9+0+1.0+1.3+1.6),
>                 case when (9.2+7.9+0+1.0+1.3+1.6)=21.0 then "yes" else
> "no" end FROM
>                 sometable;
>

Try the same with integers as you'll likely see different results.
Floating-point math is fraught with problems when it comes to expecting exact results at a specific precision.

See: http://floating-point-gui.de/

the first example of which demonstrates the problem you are seeing.


--
----- stephan beal
http://wanderinghorse.net/home/stephan/
http://gplus.to/sgbeal
"Freedom is sloppy. But since tyranny's the only guaranteed byproduct of those who insist on a perfect world, freedom will have to do." -- Bigby Wolf _______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
This communication is the property of CenturyLink and may contain confidential or privileged information. Unauthorized use of this communication is strictly prohibited and may be unlawful. If you have received this communication in error, please immediately notify the sender by reply e-mail and destroy all copies of the communication and any attachments.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Simple Math Question

Simon Slavin-3
In reply to this post by RichardR

On 22 Oct 2015, at 8:45pm, Rousselot, Richard A <[hidden email]> wrote:

>                (9.2+7.9+0+1.0+1.3+1.6),

If you really are handling a lot of numbers with exactly one decimal place, the answer is to multiply them all by 10 are part of your input routine, and store them all as integers.  On the other hand, you may have just come up with good examples to demonstrate your problem and this may not reflect your real data at all.

Financial software frequently handles all currency amounts as pence or cents for the reasons you've just found out.  Annoys the heck out of bankers until you have them work problems by hand and seen just how stupid computers really are.

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

Re: Simple Math Question

Igor Tandetnik-2
In reply to this post by RichardR
On 10/22/2015 4:25 PM, Rousselot, Richard A wrote:
> I fully expected something like this from a fractional number like 1/3 but didn't realize that this would be the case if when the digits were explicitly stated and they summed to a whole number. I will use a Cast statement to resolve.

Just like 1/3 cannot be precisely represented as a finite decimal
fraction, 1/10 aka 0.1 cannot be precisely represented as a finite
binary fraction - which is what a floating point value really is.

> FWIW, MySQL and Oracle both return all yes for that query.

In MySQL, 9.2 is a literal of DECIMAL type, which is in fact represented
as a finite decimal fraction. SQLite doesn't have such a type. You would
likely observe similar results in MySQL if you write your constants like
this: 9.2e0 (scientific notation makes them have FLOAT or DOUBLE type).

See also: https://dev.mysql.com/doc/refman/5.0/en/problems-with-float.html
--
Igor Tandetnik

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

Re: Simple Math Question

Tim Streater-3
In reply to this post by RichardR
On 22 Oct 2015 at 21:25, Richard A Rousselot <[hidden email]> wrote:

> I fully expected something like this from a fractional number like 1/3 but
> didn't realize that this would be the case if when the digits were explicitly
> stated and they summed to a whole number. I will use a Cast statement to
> resolve.

> On Thu, Oct 22, 2015 at 9:45 PM, Rousselot, Richard A <
> [hidden email]> wrote:
>
>> Doing the following math, why is it that the results are not all
>> returning "yes"?
>>
>> SELECT
>>                 (9.2+7.9+0+4.0+2.6+1.3),
>>                 case when (9.2+7.9+0+4.0+2.6+1.3)=25.0 then "yes" else
>> "no" end,
>>                 (9.2+7.8+0+3.0+1.3+1.7),
>>                 case when (9.2+7.8+0+3.0+1.3+1.7)=23.0 then "yes" else
>> "no" end,
>>                 (9.2+7.9+0+1.0+1.3+1.6),
>>                 case when (9.2+7.9+0+1.0+1.3+1.6)=21.0 then "yes" else
These numbers may be specifiable exactly in decimal to one place of decimals, but can they be expressed exactly in binary floating point? If not, then it's not gonna work. I first came across this issue 50 years ago when I wrote my first program - to calculate square roots using Newton's Method.

--
Cheers  --  Tim

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

Re: Simple Math Question

Keith Medcalf
In reply to this post by RichardR

Because there are differences.  Double precision floating point is only accurate to 14 digits and there is a difference in the 15th decimal place.

sqlite> SELECT
   ...>                 (9.2+7.9+0+4.0+2.6+1.3) - 25.0,
   ...>                 (9.2+7.8+0+3.0+1.3+1.7) - 23.0,
   ...>                 (9.2+7.9+0+1.0+1.3+1.6) - 21.0;
3.5527136788005e-15|0.0|3.5527136788005e-15

When comparing floating point numbers you should be doing something like this:

sqlite> SELECT
   ...>                 (9.2+7.9+0+4.0+2.6+1.3),
   ...>                 case when abs((9.2+7.9+0+4.0+2.6+1.3) - 25.0) < 1e-14 then "yes" else "no" end,
   ...>                 (9.2+7.8+0+3.0+1.3+1.7),
   ...>                 case when abs((9.2+7.8+0+3.0+1.3+1.7) - 23.0) < 1e-14 then "yes" else "no" end,
   ...>                 (9.2+7.9+0+1.0+1.3+1.6),
   ...>                 case when abs((9.2+7.9+0+1.0+1.3+1.6) - 21.0) < 1e-14 then "yes" else "no" end;
25.0|yes|23.0|yes|21.0|yes

The number "1e-14" is the "fuzz factor" used to determine the "closeness" of the numbers.  See
http://en.wikipedia.org/wiki/Floating_point
for some information on floating point numbers used by computers.  For comparing them see
http://floating-point-gui.de/errors/comparison/
and in particular the paper referenced at the end of the page -- which links to a more correct article.




> -----Original Message-----
> From: [hidden email] [mailto:sqlite-users-
> [hidden email]] On Behalf Of Rousselot, Richard A
> Sent: Thursday, 22 October, 2015 13:45
> To: '[hidden email]'
> Subject: [sqlite] Simple Math Question
>
> Doing the following math, why is it that the results are not all returning
> "yes"?
>
> SELECT
>                 (9.2+7.9+0+4.0+2.6+1.3),
>                 case when (9.2+7.9+0+4.0+2.6+1.3)=25.0 then "yes" else
> "no" end,
>                 (9.2+7.8+0+3.0+1.3+1.7),
>                 case when (9.2+7.8+0+3.0+1.3+1.7)=23.0 then "yes" else
> "no" end,
>                 (9.2+7.9+0+1.0+1.3+1.6),
>                 case when (9.2+7.9+0+1.0+1.3+1.6)=21.0 then "yes" else
> "no" end
> FROM
>                 sometable;
>
> Result
> 25.0 no 23.0 yes 21.0 no
>
> I'm sure this has an obvious answer but it isn't jumping out at me.
>
> Richard
>
> This communication is the property of CenturyLink and may contain
> confidential or privileged information. Unauthorized use of this
> communication is strictly prohibited and may be unlawful. If you have
> received this communication in error, please immediately notify the sender
> by reply e-mail and destroy all copies of the communication and any
> attachments.
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



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

Re: Simple Math Question

Keith Medcalf
In reply to this post by Simon Slavin-3

> Financial software frequently handles all currency amounts as pence or
> cents for the reasons you've just found out.  Annoys the heck out of
> bankers until you have them work problems by hand and seen just how stupid
> computers really are.

Usually as hundredths (1/100) of a cent -- that is, the amount multiplied by 1000, stored as an integer.  "Bankers Rounding" is then done on any results.  Usually Bankers Rounding is done as round-half-even (so 3.5 becomes 4 and 2.5 becomes 2).  While they tend to be more accurate, stochastic rounding of halfs or alternating rounding are often not reproducible and therefore are not usually used where reproducibility is required.




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

Re: Simple Math Question

Simon Slavin-3

On 23 Oct 2015, at 2:01am, Keith Medcalf <[hidden email]> wrote:

>> Financial software frequently handles all currency amounts as pence or
>> cents for the reasons you've just found out.  Annoys the heck out of
>> bankers until you have them work problems by hand and seen just how stupid
>> computers really are.
>
> Usually as hundredths (1/100) of a cent -- that is, the amount multiplied by 1000, stored as an integer.

You misstyped 10000, of course.  And that originated back in the days when the Italian Lira's sub-unit was ten thousandths of a Lira (really).  Which itself is from when one Italian Lira was the cost of a pound of silver.  Oh, for the power of the Venetian Lira.  Unthinkable now.

> "Bankers Rounding" is then done on any results.  Usually Bankers Rounding is done as round-half-even (so 3.5 becomes 4 and 2.5 becomes 2).  While they tend to be more accurate, stochastic rounding of halfs or alternating rounding are often not reproducible and therefore are not usually used where reproducibility is required.

Getting rare these days.  Modern banking rules tend to state how fractions should be rounded for each formula.  So the rules for working out current account interest rates will have an explicit statement that the result of the percentage rate calculation must be rounded this way, whereas the rules for the bank's percentage of an exchange rate deal must be rounded that way.

I do like the UK Inland Revenue rules, though.  First, they never worry about any unit less than a pound.  Second, you may round your calculations any way you like as long as you're consistent.  Third, they apply punitive fines only when you intentionally tried to cheat them, never when you merely made a mistake.

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

Re: Simple Math Question

Nicolas Jäger
In reply to this post by RichardR
Le Thu, 22 Oct 2015 19:45:09 +0000,
"Rousselot, Richard A" <[hidden email]> a écrit :

> Doing the following math, why is it that the results are not all returning "yes"?
>
> SELECT
>                 (9.2+7.9+0+4.0+2.6+1.3),
>                 case when (9.2+7.9+0+4.0+2.6+1.3)=25.0 then "yes" else "no" end,
>                 (9.2+7.8+0+3.0+1.3+1.7),
>                 case when (9.2+7.8+0+3.0+1.3+1.7)=23.0 then "yes" else "no" end,
>                 (9.2+7.9+0+1.0+1.3+1.6),
>                 case when (9.2+7.9+0+1.0+1.3+1.6)=21.0 then "yes" else "no" end
> FROM
>                 sometable;
>
> Result
> 25.0 no 23.0 yes 21.0 no
>
> I'm sure this has an obvious answer but it isn't jumping out at me.
>
> Richard
>
> This communication is the property of CenturyLink and may contain confidential or privileged
> information. Unauthorized use of this communication is strictly prohibited and may be unlawful.
> If you have received this communication in error, please immediately notify the sender by reply
> e-mail and destroy all copies of the communication and any attachments.
> _______________________________________________ sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

just for the fun: round-off error...

1/3 has an infinite number of decimal number. meanwhile a computer can only store a finite
number of decimal...

this is the story who leads Lorenz to talk about his butterfly and the chaos... I do not remember
the whole story but iirc that was when he worked on some numerical weather prediction. Data
stored in some magnetic device (sound like science-fiction for someone young like me...) and the
data in the RAM haven't the same number of decimal, so the calculus after several iterations
gave totaly different results...

regards,
Nicolas J.

n.b. : "meanwhile a computer can only store a finite number of decimal..." same in groceries when
you see 98c and you have to pay 1$... thiefs!
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Simple Math Question

Marc L. Allen
If I recall, he had a printout of the state at each time unit. The state was represented as a set of numbers out to 6 decimal points.

He wanted to rerun part of the simulation, so he entered in those super-precise numbers and let them run, but the model quick diverged because those numbers just weren't precise enough.

> On Oct 22, 2015, at 10:56 PM, Nicolas Jäger <[hidden email]> wrote:
>
> Le Thu, 22 Oct 2015 19:45:09 +0000,
> "Rousselot, Richard A" <[hidden email]> a écrit :
>
>> Doing the following math, why is it that the results are not all returning "yes"?
>>
>> SELECT
>>                (9.2+7.9+0+4.0+2.6+1.3),
>>                case when (9.2+7.9+0+4.0+2.6+1.3)=25.0 then "yes" else "no" end,
>>                (9.2+7.8+0+3.0+1.3+1.7),
>>                case when (9.2+7.8+0+3.0+1.3+1.7)=23.0 then "yes" else "no" end,
>>                (9.2+7.9+0+1.0+1.3+1.6),
>>                case when (9.2+7.9+0+1.0+1.3+1.6)=21.0 then "yes" else "no" end
>> FROM
>>                sometable;
>>
>> Result
>> 25.0 no 23.0 yes 21.0 no
>>
>> I'm sure this has an obvious answer but it isn't jumping out at me.
>>
>> Richard
>>
>> This communication is the property of CenturyLink and may contain confidential or privileged
>> information. Unauthorized use of this communication is strictly prohibited and may be unlawful.
>> If you have received this communication in error, please immediately notify the sender by reply
>> e-mail and destroy all copies of the communication and any attachments.
>> _______________________________________________ sqlite-users mailing list
>> [hidden email]
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
> just for the fun: round-off error...
>
> 1/3 has an infinite number of decimal number. meanwhile a computer can only store a finite
> number of decimal...
>
> this is the story who leads Lorenz to talk about his butterfly and the chaos... I do not remember
> the whole story but iirc that was when he worked on some numerical weather prediction. Data
> stored in some magnetic device (sound like science-fiction for someone young like me...) and the
> data in the RAM haven't the same number of decimal, so the calculus after several iterations
> gave totaly different results...
>
> regards,
> Nicolas J.
>
> n.b. : "meanwhile a computer can only store a finite number of decimal..." same in groceries when
> you see 98c and you have to pay 1$... thiefs!
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



This email and any attachments are only for use by the intended recipient(s) and may contain legally privileged, confidential, proprietary or otherwise private information. Any unauthorized use, reproduction, dissemination, distribution or other disclosure of the contents of this e-mail or its attachments is strictly prohibited. If you have received this email in error, please notify the sender immediately and delete the original.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Simple Math Question

Dominique Devienne
In reply to this post by Igor Tandetnik-2
On Thu, Oct 22, 2015 at 11:55 PM, Igor Tandetnik <[hidden email]> wrote:

> On 10/22/2015 4:25 PM, Rousselot, Richard A wrote:
>
>> FWIW, MySQL and Oracle both return all yes for that query.
>>
>
> In MySQL, 9.2 is a literal of DECIMAL type, which is in fact represented
> as a finite decimal fraction. SQLite doesn't have such a type. You would
> likely observe similar results in MySQL if you write your constants like
> this: 9.2e0 (scientific notation makes them have FLOAT or DOUBLE type).
>

And oracle's NUMBER can be up to 20 bytes (21 unsigned), stores number is
base-100, i.e. each byte represents 2 base-10 digits.
http://www.orafaq.com/wiki/Number

SQLite only uses IEEE double, which often cannot represent accurately even
small (as in text) numbers with a decimal point. You can use Oracle's
BINARY_DOUBLE to force it using an IEEE double. FWIW. --DD
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Simple Math Question

Scott Doctor
Something sounds wrong with this floating point issue. His example
consists of two digit numbers with simple addition. The number 1.7 would
be represented by the value 17 and an exponent of -1, which is an exact
number, same with his other numbers. His math operations should give
exact results since the operations are simple addition. Since he is not
doing any multiplication or division, there should not be any resolution
issues. His results using his simple equations should give exact
answers. So something is not handling the numbers properly if he is not
getting exact results. If the claims about floating point results in
sqlite are as stated in this thread of messages then floating point
should be entirely avoided and eliminated from sqlite as this implies
that floating point operations are improperly implemented rendering
useless and incorrect results.

------------
Scott Doctor
[hidden email]

On 10/23/2015 12:14 AM, Dominique Devienne wrote:

> On Thu, Oct 22, 2015 at 11:55 PM, Igor Tandetnik <[hidden email]> wrote:
>
>> On 10/22/2015 4:25 PM, Rousselot, Richard A wrote:
>>
>>> FWIW, MySQL and Oracle both return all yes for that query.
>>>
>> In MySQL, 9.2 is a literal of DECIMAL type, which is in fact represented
>> as a finite decimal fraction. SQLite doesn't have such a type. You would
>> likely observe similar results in MySQL if you write your constants like
>> this: 9.2e0 (scientific notation makes them have FLOAT or DOUBLE type).
>>
> And oracle's NUMBER can be up to 20 bytes (21 unsigned), stores number is
> base-100, i.e. each byte represents 2 base-10 digits.
> http://www.orafaq.com/wiki/Number
>
> SQLite only uses IEEE double, which often cannot represent accurately even
> small (as in text) numbers with a decimal point. You can use Oracle's
> BINARY_DOUBLE to force it using an IEEE double. FWIW. --DD
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>

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

Re: Simple Math Question

Keith Medcalf

You are thinking (and typing) in base 10.  Computers use this new-fangled thing called binary -- base 2.
The "floating point" type is IEEE 754 double precision binary (base 2) floating point.  

SQLite does not use decimal floating point (IEEE 854-1987) or General Decimal Arithmetic
http://speleotrove.com/decimal/decarith.html
as specified by IBM and implemented in many non-lite SQL Engines.


> -----Original Message-----
> From: [hidden email] [mailto:sqlite-users-
> [hidden email]] On Behalf Of Scott Doctor
> Sent: Friday, 23 October, 2015 01:31
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] Simple Math Question
>
> Something sounds wrong with this floating point issue. His example
> consists of two digit numbers with simple addition. The number 1.7 would
> be represented by the value 17 and an exponent of -1, which is an exact
> number, same with his other numbers. His math operations should give
> exact results since the operations are simple addition. Since he is not
> doing any multiplication or division, there should not be any resolution
> issues. His results using his simple equations should give exact
> answers. So something is not handling the numbers properly if he is not
> getting exact results. If the claims about floating point results in
> sqlite are as stated in this thread of messages then floating point
> should be entirely avoided and eliminated from sqlite as this implies
> that floating point operations are improperly implemented rendering
> useless and incorrect results.
>
> ------------
> Scott Doctor
> [hidden email]
>
> On 10/23/2015 12:14 AM, Dominique Devienne wrote:
> > On Thu, Oct 22, 2015 at 11:55 PM, Igor Tandetnik <[hidden email]>
> wrote:
> >
> >> On 10/22/2015 4:25 PM, Rousselot, Richard A wrote:
> >>
> >>> FWIW, MySQL and Oracle both return all yes for that query.
> >>>
> >> In MySQL, 9.2 is a literal of DECIMAL type, which is in fact
> represented
> >> as a finite decimal fraction. SQLite doesn't have such a type. You
> would
> >> likely observe similar results in MySQL if you write your constants
> like
> >> this: 9.2e0 (scientific notation makes them have FLOAT or DOUBLE type).
> >>
> > And oracle's NUMBER can be up to 20 bytes (21 unsigned), stores number
> is
> > base-100, i.e. each byte represents 2 base-10 digits.
> > http://www.orafaq.com/wiki/Number
> >
> > SQLite only uses IEEE double, which often cannot represent accurately
> even
> > small (as in text) numbers with a decimal point. You can use Oracle's
> > BINARY_DOUBLE to force it using an IEEE double. FWIW. --DD
> > _______________________________________________
> > sqlite-users mailing list
> > [hidden email]
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >
> >
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



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

Re: Simple Math Question

Dominique Devienne
On Fri, Oct 23, 2015 at 9:46 AM, Keith Medcalf <[hidden email]> wrote:

> You are thinking (and typing) in base 10.  Computers use this new-fangled
> thing called binary -- base 2.
> The "floating point" type is IEEE 754 double precision binary (base 2)
> floating point.
>

https://en.wikipedia.org/wiki/Double-precision_floating-point_format

Basically, each bit in the mantissa represents a power-of-2 fraction. And
the sum of these fractions gives you what comes after the decimal point
(adjusted for the exponent).

So 1.75 is exact in IEEE since 1/2 + 1/4 = 0.75, but other simple numbers'
decimal part can't be represented exactly using such a sum of power-of-2
fractions (1/(2^i)), and you get the closest number it can represent,
typically with an error around 1/(2^52) for an 8-byte double.

Another good good way to think of IEEE I was presented once with, and which
kind of gave me a ah-ah moment, is the fact that numbers with exact
representation fall on the nodes of grid, and there's plenty of "space" in
between the nodes for values which cannot be exactly represented. The
"spacing" of the grid varies depending on the magnitude of your number
(i.e. the exponent) and the size of the mantissa (i.e. how small your 1/2^i
fractions get). IEEE is complex... I'm not an expert, but hopefully the
above helps. --DD
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Simple Math Question

Bert Huijben-5
In reply to this post by Scott Doctor
Computers don't store values in base-10 fractions when they use doubles or floats; they use base-2 fractions. This causes that your simple base-10 fractions can't be stored exactly.

This is also why those other -not lite- engines have a decimal type, processed using much slower integer logic coded in their engines... but this doesn't make the current floating point arithmetic broken in any way.

Bert



From: Scott Doctor
Sent: vrijdag 23 oktober 2015 09:30
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Simple Math Question


Something sounds wrong with this floating point issue. His example
consists of two digit numbers with simple addition. The number 1.7 would
be represented by the value 17 and an exponent of -1, which is an exact
number, same with his other numbers. His math operations should give
exact results since the operations are simple addition. Since he is not
doing any multiplication or division, there should not be any resolution
issues. His results using his simple equations should give exact
answers. So something is not handling the numbers properly if he is not
getting exact results. If the claims about floating point results in
sqlite are as stated in this thread of messages then floating point
should be entirely avoided and eliminated from sqlite as this implies
that floating point operations are improperly implemented rendering
useless and incorrect results.

------------
Scott Doctor
[hidden email]

On 10/23/2015 12:14 AM, Dominique Devienne wrote:

> On Thu, Oct 22, 2015 at 11:55 PM, Igor Tandetnik <[hidden email]> wrote:
>
>> On 10/22/2015 4:25 PM, Rousselot, Richard A wrote:
>>
>>> FWIW, MySQL and Oracle both return all yes for that query.
>>>
>> In MySQL, 9.2 is a literal of DECIMAL type, which is in fact represented
>> as a finite decimal fraction. SQLite doesn't have such a type. You would
>> likely observe similar results in MySQL if you write your constants like
>> this: 9.2e0 (scientific notation makes them have FLOAT or DOUBLE type).
>>
> And oracle's NUMBER can be up to 20 bytes (21 unsigned), stores number is
> base-100, i.e. each byte represents 2 base-10 digits.
> http://www.orafaq.com/wiki/Number
>
> SQLite only uses IEEE double, which often cannot represent accurately even
> small (as in text) numbers with a decimal point. You can use Oracle's
> BINARY_DOUBLE to force it using an IEEE double. FWIW. --DD
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


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

Re: Simple Math Question

Scott Doctor
In reply to this post by Keith Medcalf

17 -> 0x10001 mantissa
-1 -> 0xfffff  exponent ( or however many bits the exponent is
represented by
exact

------------
Scott Doctor
[hidden email]

On 10/23/2015 12:46 AM, Keith Medcalf wrote:

> You are thinking (and typing) in base 10.  Computers use this new-fangled thing called binary -- base 2.
> The "floating point" type is IEEE 754 double precision binary (base 2) floating point.
>
> SQLite does not use decimal floating point (IEEE 854-1987) or General Decimal Arithmetic
> http://speleotrove.com/decimal/decarith.html
> as specified by IBM and implemented in many non-lite SQL Engines.
>
>
>> -----Original Message-----
>> From: [hidden email] [mailto:sqlite-users-
>> [hidden email]] On Behalf Of Scott Doctor
>> Sent: Friday, 23 October, 2015 01:31
>> To: General Discussion of SQLite Database
>> Subject: Re: [sqlite] Simple Math Question
>>
>> Something sounds wrong with this floating point issue. His example
>> consists of two digit numbers with simple addition. The number 1.7 would
>> be represented by the value 17 and an exponent of -1, which is an exact
>> number, same with his other numbers. His math operations should give
>> exact results since the operations are simple addition. Since he is not
>> doing any multiplication or division, there should not be any resolution
>> issues. His results using his simple equations should give exact
>> answers. So something is not handling the numbers properly if he is not
>> getting exact results. If the claims about floating point results in
>> sqlite are as stated in this thread of messages then floating point
>> should be entirely avoided and eliminated from sqlite as this implies
>> that floating point operations are improperly implemented rendering
>> useless and incorrect results.
>>
>> ------------
>> Scott Doctor
>> [hidden email]
>>
>> On 10/23/2015 12:14 AM, Dominique Devienne wrote:
>>> On Thu, Oct 22, 2015 at 11:55 PM, Igor Tandetnik <[hidden email]>
>> wrote:
>>>> On 10/22/2015 4:25 PM, Rousselot, Richard A wrote:
>>>>
>>>>> FWIW, MySQL and Oracle both return all yes for that query.
>>>>>
>>>> In MySQL, 9.2 is a literal of DECIMAL type, which is in fact
>> represented
>>>> as a finite decimal fraction. SQLite doesn't have such a type. You
>> would
>>>> likely observe similar results in MySQL if you write your constants
>> like
>>>> this: 9.2e0 (scientific notation makes them have FLOAT or DOUBLE type).
>>>>
>>> And oracle's NUMBER can be up to 20 bytes (21 unsigned), stores number
>> is
>>> base-100, i.e. each byte represents 2 base-10 digits.
>>> http://www.orafaq.com/wiki/Number
>>>
>>> SQLite only uses IEEE double, which often cannot represent accurately
>> even
>>> small (as in text) numbers with a decimal point. You can use Oracle's
>>> BINARY_DOUBLE to force it using an IEEE double. FWIW. --DD
>>> _______________________________________________
>>> sqlite-users mailing list
>>> [hidden email]
>>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>>
>>>
>> _______________________________________________
>> sqlite-users mailing list
>> [hidden email]
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>

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

Re: Simple Math Question

Dominique Devienne
On Fri, Oct 23, 2015 at 10:09 AM, Scott Doctor <[hidden email]>
wrote:
>
> 17 -> 0x10001 mantissa
>

Not in IEEE binary rep based on the 1/2^i fraction I describe above.
0.17 would be 0/2 + 0/4 + 1/8 (0.125) + ... (whatever 1/2^1 fractions
approximate best the remainder of 0.17 - 0.125). --DD
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Simple Math Question

Keith Medcalf

sqlite> select ieee754(1.7);
ieee754(7656119366529843,-52)

or ‭00011011001100110011001100110011001100110011001100110011 for the mantissa.  Note that it is an infinitely repeating fraction.

> -----Original Message-----
> From: [hidden email] [mailto:sqlite-users-
> [hidden email]] On Behalf Of Dominique Devienne
> Sent: Friday, 23 October, 2015 02:12
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] Simple Math Question
>
> On Fri, Oct 23, 2015 at 10:09 AM, Scott Doctor <[hidden email]>
> wrote:
> >
> > 17 -> 0x10001 mantissa
> >
>
> Not in IEEE binary rep based on the 1/2^i fraction I describe above.
> 0.17 would be 0/2 + 0/4 + 1/8 (0.125) + ... (whatever 1/2^1 fractions
> approximate best the remainder of 0.17 - 0.125). --DD
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
1234