SQL is a storage and retrieval engine with limited calculation abilities in support of storage, retrieval and reporting.
You can store numbers as integers, text or floating point and the calling language can use whatever subroutines to translate and manipulate the numbers. If the calling language has subroutine library that supports binary coded decimal (BCD) https://en.wikipedia.org/wiki/Binary-coded_decimal you can store the inputs and outputs in SQL while performing calculations in the calling language. The Intel chip family (and I assume ARM RISC chips) has only rudimentary support for BCD, so the calculations have to be implemented in a higher level language. https://en.wikipedia.org/wiki/Intel_BCD_opcode Besides SQLite3 is implemented in C; not assembler. TI has an assembly language library and this enthusiast has found a library for building an onscreen calculator (but he is really excited by ferrite core magnetic memory! -- hasn't been used in 30 years!). http://www.eetimes.com/author.asp?section_id=14&doc_id=1282755 On Fri, Oct 23, 2015 at 11:53 AM, Scott Hess <shess at google.com> wrote: > In one case, you asked "When I add these imprecise values together, do they > equal this other precise value?" In the other case you asked "When I add > these imprecise values together, what is the decimal expansion?" and then > you noticed that the decimal expansion did not equal that precise value. > > My point is that what is going on internally is all there is. It's not > reporting something different from the result it sees, it is very literally > reporting what it has. In the language metaphor, you're asking the > questions in English (base-10 in this case), and the computer only knows > how to think in Japanese (base-2 in this case), so you can't avoid the > translation back and forth, and when you give it little bits and pieces > then ask it to put them together, it can't understand your intention from > the bits and pieces. > > In your example, the computer didn't at some point think "I had a 23, here, > but I'm going to report 22.99999 just for the heck of it". What probably > happened on the other case is that it had a near-25 value which was closer > to 25 than to 24.9999999999, so it printed 25, whereas on the near-23 case > it was closer to 22.99999 than 23, so it went with that. When you have a > bunch of base-2 representations of base-10 fractional numbers, sometimes > they're slightly too small, sometimes slightly too large. When you add > them together, sometimes you're lucky and the errors cancel out and you > happen to get what you hoped for, but sometimes the errors go against you > and you end up slightly too small or slightly too large. > > -scott > > > On Fri, Oct 23, 2015 at 8:34 AM, Rousselot, Richard A < > Richard.A.Rousselot at centurylink.com> wrote: > > > Scott, > > > > I agree with everything you said but... To me if a program/CPU evaluates > > something internally, then when it reports the result it should be the > > result as it sees it. It shouldn't report something different. > > > > So using your analogy, I ask a English speaking person a two interrelated > > questions, they translate the questions to Japanese in their head, then > > answers one question in Japanese and another in English. I say pick a > > language and stick with it. Either answer my question all in English or > > all in Japanese don't mix it. > > > > I think we are getting to hung up on the details of what is going on > > internally. The real question is why don't the two results, which are > > coming from the same program, agree? (i.e. return 22.99999999999999 not > > 23.0) > > > > Richard > > > > -----Original Message----- > > From: sqlite-users-bounces at mailinglists.sqlite.org [mailto: > > sqlite-users-bounces at mailinglists.sqlite.org] On Behalf Of Scott Hess > > Sent: Friday, October 23, 2015 10:05 AM > > To: General Discussion of SQLite Database > > Subject: Re: [sqlite] Simple Math Question > > > > On Fri, Oct 23, 2015 at 7:39 AM, Dominique Devienne <ddevienne at gmail.com > > > > wrote: > > > > > On Fri, Oct 23, 2015 at 4:16 PM, Rousselot, Richard A < > > > Richard.A.Rousselot at centurylink.com> wrote: > > > > So I decided to output 1000 digits, because why not? So now I am > > > > more perplexed with all these digits showing it is working the > > > > opposite of > > > how I > > > > expected it. Why is the second set of equations evaluating to a > "yes" > > > when > > > > it is the only one that is obviously NOT equal to the expression??? > > > > > > Indeed, that's puzzling :) > > > > > > Just to be clear, though, how floating-point numbers work is breaking > your > > expectations because your expectations are wrong when applied to > > floating-point numbers. Internally, they are base-2 scientific notation, > > so asking for more significant digits in the base-10 representation won't > > help - base-10 fractional numbers cannot always be represented precisely > in > > base-2, ALSO base-2 fractional numbers cannot always be represented > > precisely in base-10, so it's like a game of telephone where you can end > up > > slightly removed from where you started out, even though it seems like > it's > > a simple round trip. Since each individual digit cannot be represented > > perfectly, it doesn't matter how many digits of precision you ask for, > > you'll always be able to find cases where it doesn't line up like you > > expect. > > > > Think of it this way: Find an English sentence, and find an English to > > Japanese translator. Translate each individual word of the sentence from > > English to Japanese, then concatenate the results together. Then > translate > > the entire original sentence to Japanese. The results will almost never > be > > the same. Then do the same process translating the Japanese back to > > English. Again, the two routes will provide different results, _and_ > both > > of those results will almost certainly not match the original English > > sentence. This isn't a reflection of the translator's abilities at all. > > > > I'm not saying the computer is always right, just that the computer is > > following a very strict recipe with reproducible results. I don't mean > > reproducible like your three examples make logical sense to you, the > user, > > I mean reproducible like my Intel box gives the same results as my AMD > box > > as my ARM box. If you want to be able to deal with fractional decimal > > values with high fidelity, you either need to arrange for base-10 > > representation (slow, because computers have to simulate it), or you have > > to do your math in shifted fashion (fast, but can be error prone). > > > > -scott > > _______________________________________________ > > sqlite-users mailing list > > sqlite-users at mailinglists.sqlite.org > > 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 > > sqlite-users at mailinglists.sqlite.org > > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > > > _______________________________________________ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >
