
1234

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 email and destroy all copies of the communication and any attachments.
_______________________________________________
sqliteusers mailing list
[hidden email]
http://mailinglists.sqlite.org/cgibin/mailman/listinfo/sqliteusers


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.
Floatingpoint math is fraught with problems when it comes to expecting
exact results at a specific precision.
See: http://floatingpointgui.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
_______________________________________________
sqliteusers mailing list
[hidden email]
http://mailinglists.sqlite.org/cgibin/mailman/listinfo/sqliteusers


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.
Floatingpoint math is fraught with problems when it comes to expecting exact results at a specific precision.
See: http://floatingpointgui.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 _______________________________________________
sqliteusers mailing list
[hidden email]
http://mailinglists.sqlite.org/cgibin/mailman/listinfo/sqliteusersThis 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 email and destroy all copies of the communication and any attachments.
_______________________________________________
sqliteusers mailing list
[hidden email]
http://mailinglists.sqlite.org/cgibin/mailman/listinfo/sqliteusers


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.
_______________________________________________
sqliteusers mailing list
[hidden email]
http://mailinglists.sqlite.org/cgibin/mailman/listinfo/sqliteusers


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/problemswithfloat.html
Igor Tandetnik
_______________________________________________
sqliteusers mailing list
[hidden email]
http://mailinglists.sqlite.org/cgibin/mailman/listinfo/sqliteusers


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
_______________________________________________
sqliteusers mailing list
[hidden email]
http://mailinglists.sqlite.org/cgibin/mailman/listinfo/sqliteusers


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.5527136788005e150.03.5527136788005e15
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) < 1e14 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) < 1e14 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) < 1e14 then "yes" else "no" end;
25.0yes23.0yes21.0yes
The number "1e14" is the "fuzz factor" used to determine the "closeness" of the numbers. See
http://en.wikipedia.org/wiki/Floating_pointfor some information on floating point numbers used by computers. For comparing them see
http://floatingpointgui.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:sqliteusers
> [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 email and destroy all copies of the communication and any
> attachments.
> _______________________________________________
> sqliteusers mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgibin/mailman/listinfo/sqliteusers_______________________________________________
sqliteusers mailing list
[hidden email]
http://mailinglists.sqlite.org/cgibin/mailman/listinfo/sqliteusers


> 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 roundhalfeven (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.
_______________________________________________
sqliteusers mailing list
[hidden email]
http://mailinglists.sqlite.org/cgibin/mailman/listinfo/sqliteusers


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 subunit 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 roundhalfeven (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.
_______________________________________________
sqliteusers mailing list
[hidden email]
http://mailinglists.sqlite.org/cgibin/mailman/listinfo/sqliteusers


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
> email and destroy all copies of the communication and any attachments.
> _______________________________________________ sqliteusers mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgibin/mailman/listinfo/sqliteusersjust for the fun: roundoff 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 sciencefiction 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!
_______________________________________________
sqliteusers mailing list
[hidden email]
http://mailinglists.sqlite.org/cgibin/mailman/listinfo/sqliteusers


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 superprecise 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
>> email and destroy all copies of the communication and any attachments.
>> _______________________________________________ sqliteusers mailing list
>> [hidden email]
>> http://mailinglists.sqlite.org/cgibin/mailman/listinfo/sqliteusers>
> just for the fun: roundoff 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 sciencefiction 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!
> _______________________________________________
> sqliteusers mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgibin/mailman/listinfo/sqliteusersThis 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 email or its attachments is strictly prohibited. If you have received this email in error, please notify the sender immediately and delete the original.
_______________________________________________
sqliteusers mailing list
[hidden email]
http://mailinglists.sqlite.org/cgibin/mailman/listinfo/sqliteusers


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
base100, i.e. each byte represents 2 base10 digits.
http://www.orafaq.com/wiki/NumberSQLite 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
_______________________________________________
sqliteusers mailing list
[hidden email]
http://mailinglists.sqlite.org/cgibin/mailman/listinfo/sqliteusers


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
> base100, i.e. each byte represents 2 base10 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
> _______________________________________________
> sqliteusers mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgibin/mailman/listinfo/sqliteusers>
>
_______________________________________________
sqliteusers mailing list
[hidden email]
http://mailinglists.sqlite.org/cgibin/mailman/listinfo/sqliteusers


You are thinking (and typing) in base 10. Computers use this newfangled 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 8541987) or General Decimal Arithmetic
http://speleotrove.com/decimal/decarith.htmlas specified by IBM and implemented in many nonlite SQL Engines.
> Original Message
> From: [hidden email] [mailto:sqliteusers
> [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
> > base100, i.e. each byte represents 2 base10 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
> > _______________________________________________
> > sqliteusers mailing list
> > [hidden email]
> > http://mailinglists.sqlite.org/cgibin/mailman/listinfo/sqliteusers> >
> >
>
> _______________________________________________
> sqliteusers mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgibin/mailman/listinfo/sqliteusers_______________________________________________
sqliteusers mailing list
[hidden email]
http://mailinglists.sqlite.org/cgibin/mailman/listinfo/sqliteusers


On Fri, Oct 23, 2015 at 9:46 AM, Keith Medcalf < [hidden email]> wrote:
> You are thinking (and typing) in base 10. Computers use this newfangled
> thing called binary  base 2.
> The "floating point" type is IEEE 754 double precision binary (base 2)
> floating point.
>
https://en.wikipedia.org/wiki/Doubleprecision_floatingpoint_formatBasically, each bit in the mantissa represents a powerof2 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 powerof2
fractions (1/(2^i)), and you get the closest number it can represent,
typically with an error around 1/(2^52) for an 8byte double.
Another good good way to think of IEEE I was presented once with, and which
kind of gave me a ahah 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
_______________________________________________
sqliteusers mailing list
[hidden email]
http://mailinglists.sqlite.org/cgibin/mailman/listinfo/sqliteusers


Computers don't store values in base10 fractions when they use doubles or floats; they use base2 fractions. This causes that your simple base10 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
> base100, i.e. each byte represents 2 base10 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
> _______________________________________________
> sqliteusers mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgibin/mailman/listinfo/sqliteusers>
>
_______________________________________________
sqliteusers mailing list
[hidden email]
http://mailinglists.sqlite.org/cgibin/mailman/listinfo/sqliteusers_______________________________________________
sqliteusers mailing list
[hidden email]
http://mailinglists.sqlite.org/cgibin/mailman/listinfo/sqliteusers


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 newfangled 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 8541987) or General Decimal Arithmetic
> http://speleotrove.com/decimal/decarith.html> as specified by IBM and implemented in many nonlite SQL Engines.
>
>
>> Original Message
>> From: [hidden email] [mailto:sqliteusers
>> [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
>>> base100, i.e. each byte represents 2 base10 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
>>> _______________________________________________
>>> sqliteusers mailing list
>>> [hidden email]
>>> http://mailinglists.sqlite.org/cgibin/mailman/listinfo/sqliteusers>>>
>>>
>> _______________________________________________
>> sqliteusers mailing list
>> [hidden email]
>> http://mailinglists.sqlite.org/cgibin/mailman/listinfo/sqliteusers>
>
> _______________________________________________
> sqliteusers mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgibin/mailman/listinfo/sqliteusers>
>
_______________________________________________
sqliteusers mailing list
[hidden email]
http://mailinglists.sqlite.org/cgibin/mailman/listinfo/sqliteusers


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:sqliteusers
> [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
> _______________________________________________
> sqliteusers mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgibin/mailman/listinfo/sqliteusers_______________________________________________
sqliteusers mailing list
[hidden email]
http://mailinglists.sqlite.org/cgibin/mailman/listinfo/sqliteusers

1234
