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 |
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 |
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 |
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 |
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 |
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 |
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 -- Cheers -- Tim _______________________________________________ sqlite-users mailing list [hidden email] http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users |
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 |
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 |
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 |
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 |
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 |
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 |
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 |
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 |
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 |
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 |
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 |
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> 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 |
Free forum by Nabble | Edit this page |