[sqlite] Simple Math Question
Read that as "sorry i replied to wrong Scott... mine was meant to be a reply to Scott Hess". Long working days and small and keyboardless mobile devices are not helping me. Sorry again Il 23/ott/2015 19:40, "Alessandro Marzocchi" ha scritto: > Sorry, i replied to wrong Scott Hess... mine was meant to be a reply to > his message... > " 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". > Sorry for my error. > Il 23/ott/2015 19:18, "Scott Robison" ha > scritto: > > On Fri, Oct 23, 2015 at 10:45 AM, Alessandro Marzocchi < > alessandro.marzocchi at gmail.com> wrote: > > > Scott actually all base2 fractions are rapresentable as base10 > > fractions... 10 is divisable by 2. > > As for Richard try to think about this... computer does 2 base > > calculations as we usually do calculation in base 10. But the same would > > happend to us when talking about different bases.. > > Let's say we want to add 3 times 20 minutes (expressed in decimal of > hours > > up to 6th digit). 0h20' is 0.33 hours . If you multiply that for 3 > you > > get 0.99.. not 1 as you expect. The > > > > Yes, they are. What did I write that leads you to believe I don't > understand that? Or are you addressing a different Scott (as there are > three in this conversation)? I'm not upset or angry or demanding > satisfaction, just trying to understand where my commentary went wrong and > if I need to correct anything for the record. > > When I say floating point calculations are "inexact" what I mean is > "potentially inexact" because conversion from decimal in source code to > binary floating point in memory is potentially inexact, the arithmetic > performed will potentially involve rounding of some form, and the final > conversion back from binary floating point to decimal representation for > humans can only work with what is left over after those previous potential > approximations. > > > > Il 23/ott/2015 18:31, "Scott Robison" ha > > scritto: > > > > > On Fri, Oct 23, 2015 at 9: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. > > > > > > > > > > This is true to an extent, and there are ways to display something > "more > > > exact". But the library programmers wrote code to format floating point > > > numbers in a way that is appropriate for display to humans. Knowing > that > > > floating point calculations are inexact, they round values after a > > certain > > > number of decimal places, as most applications expect to see something > > like > > > "25" not "24.9995" (numbers made up). > > > > > > > > > > > > > > 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.99 > > 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.
[sqlite] Simple Math Question
Sorry, i replied to wrong Scott Hess... mine was meant to be a reply to his message... " 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". Sorry for my error. Il 23/ott/2015 19:18, "Scott Robison" ha scritto: On Fri, Oct 23, 2015 at 10:45 AM, Alessandro Marzocchi < alessandro.marzocchi at gmail.com> wrote: > Scott actually all base2 fractions are rapresentable as base10 > fractions... 10 is divisable by 2. > As for Richard try to think about this... computer does 2 base > calculations as we usually do calculation in base 10. But the same would > happend to us when talking about different bases.. > Let's say we want to add 3 times 20 minutes (expressed in decimal of hours > up to 6th digit). 0h20' is 0.33 hours . If you multiply that for 3 you > get 0.99.. not 1 as you expect. The > Yes, they are. What did I write that leads you to believe I don't understand that? Or are you addressing a different Scott (as there are three in this conversation)? I'm not upset or angry or demanding satisfaction, just trying to understand where my commentary went wrong and if I need to correct anything for the record. When I say floating point calculations are "inexact" what I mean is "potentially inexact" because conversion from decimal in source code to binary floating point in memory is potentially inexact, the arithmetic performed will potentially involve rounding of some form, and the final conversion back from binary floating point to decimal representation for humans can only work with what is left over after those previous potential approximations. > Il 23/ott/2015 18:31, "Scott Robison" ha > scritto: > > > On Fri, Oct 23, 2015 at 9: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. > > > > > > > This is true to an extent, and there are ways to display something "more > > exact". But the library programmers wrote code to format floating point > > numbers in a way that is appropriate for display to humans. Knowing that > > floating point calculations are inexact, they round values after a > certain > > number of decimal places, as most applications expect to see something > like > > "25" not "24.9995" (numbers made up). > > > > > > > > > > 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.99 > 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
[sqlite] Simple Math Question
Scott actually all base2 fractions are rapresentable as base10 fractions... 10 is divisable by 2. As for Richard try to think about this... computer does 2 base calculations as we usually do calculation in base 10. But the same would happend to us when talking about different bases.. Let's say we want to add 3 times 20 minutes (expressed in decimal of hours up to 6th digit). 0h20' is 0.33 hours . If you multiply that for 3 you get 0.99.. not 1 as you expect. The Il 23/ott/2015 18:31, "Scott Robison" ha scritto: > On Fri, Oct 23, 2015 at 9: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. > > > > This is true to an extent, and there are ways to display something "more > exact". But the library programmers wrote code to format floating point > numbers in a way that is appropriate for display to humans. Knowing that > floating point calculations are inexact, they round values after a certain > number of decimal places, as most applications expect to see something like > "25" not "24.9995" (numbers made up). > > > > > > 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.99 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 > > > 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] crash
12 okt 2015, om 20:22, R.Smith:
>
> On 2015-10-12 07:40 PM, Richard Hipp wrote:
>> On 10/12/15, R.Smith wrote:
>>> More explicitly - would these be valid queries:
>>>
>>> SELECT props.*
>>>FROM (
>>> SELECT 1 as id, JSON_OBJECT('i',5,'n','James') AS obj UNION
>>> ALL
>>> SELECT 2, JSON_OBJECT('i',6,'n','Jill') UNION ALL
>>> SELECT 3, JSON_OBJECT('i',7,'n','John')
>>> ) AS T1, JSON_EACH(T1.obj) AS props
>>> WHERE 1
>>>
>> Yes.
>>
>>> and in future (without JSON):
>>>
>>> SELECT B.* FROM
>>> (SELECT (C1 || C2 || C3) AS CX FROM T) AS A,
>>> (SELECT CX FROM A) AS B
>>> WHERE 1
>>>
>> This is not valid, and has never been valid. I'm sorry, but I don't
>> understand the connection to the previous query. They are completely
>> different, as far as I can see.
>
> Yes, I guess what I am after is quantifying the difference. In The
> first query, an alias to a completely made-up table is passed to a
> table-valued function (the T1 alias) and it knows to reference/read
> from that made up table designated as T1.
>
> In the second query, a completely made up table A is used in a
> second table definition (but this time not a table-valued function -
> which is the only real difference) to produce B.
>
> Of course they are not the same, and the first works but the second
> doesn't - so is it ONLY because the first is a Table-valued function
> (and thus will this be common workings for Table-valued functions
> henceforth), or perhaps it has to be an eponymous virtual table, or
> is it specifically because it's a JSON function which allows this
> uniquely?
>
> I think/hope this will be common workings for all table-valued
> functions - this is essentially what I'm asking.
>
> (The other question about whether this would work with normal table
> functions is already answered and understood to be: Never).
>
> Thank you kindly,
> Ryan
Hello, I still wish to add something and would like to hear if I'm
wrong.
The question if table aliasses are visible within json functions
becomes easier when using an alternative notation. It looks as if
table-like functions do have "column-like" parameters that can be
passed in regular WHERE clauses.
The common notation:
json_each(t1.obj) as props
is then:
json_each as props where props.json=t1.obj
Note that I'm uninvolved and only found this while experimenting with
SQLite 3.9.0.
Ryan Smith's example query:
SELECT t1.*, props.*
FROM (
SELECT 1 as id, JSON_OBJECT('i',5,'n','James') AS obj UNION ALL
SELECT 2, JSON_OBJECT('i',6,'n','Jill') UNION ALL
SELECT 3, JSON_OBJECT('i',7,'n','John')
) AS t1
INNER JOIN JSON_EACH(t1.obj) AS props
can be written as
SELECT t1.id, props.*
FROM (
SELECT 1 as id, JSON_OBJECT('i',5,'n','James') AS json UNION ALL
SELECT 2, JSON_OBJECT('i',6,'n','Jill') UNION ALL
SELECT 3, JSON_OBJECT('i',7,'n','John')
) AS t1
JOIN (SELECT '$.n' AS root) t2
JOIN JSON_EACH AS props
WHERE props.json=t1.json
and that should clear the initial question marks.
I assume it remains so in future versions but actually don't care.
Regards, Edzard.
[sqlite] Simple Math Question
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 :) To know for sure, you'd need Keith's custom ieee754() function probably. Until you can have a look at the actual bits of the computed double, to see the sign/exponent/mantissa at the binary level, hard to say. Could be your use of 1000f too! 15 decimal digits is typical for double. --DD
[sqlite] Simple Math Question
On 23 October 2015 at 16:08, Dominique Devienne wrote: > 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 > Very true, and a corollary is that using a fixed tolerance for floating point comparisons (like 1e-14 suggested elsewhere in this thread) doesn't work in general. It's fine if you know the magnitude of the numbers you're comparing, but once you get to numbers above 100, abs(f1 - f2) < 1e-14 becomes the same as saying f1 == f2, because even a single bit of error at this magnitude will be larger than 1e-14. Some languages provide a way to determine the distance between a given floating point value and the next largest representable number (eg the Math.ulp function in java), but I'm not sure if SQL provides such? Or if its even in scope... -Rowan
[sqlite] Simple Math Question
On Fri, Oct 23, 2015 at 3:45 PM, Rousselot, Richard A < Richard.A.Rousselot at centurylink.com> wrote: > Anyhow, one last question. If the case statements are evaluating > something that is not a whole number (or better yet not exactly matching on > both sides of the equation) . Why is it that when displaying the results > of the math sqlite does not produce something like 25.03 or > 22.999? Shouldn't the result have all significant digits showing? > I guess that is why these results seem misleading. > The output is done by sqlite3.exe, the command line shell, and you'd have to look at its code to know for sure. But instead, take control of the formatting, by using printf() from https://www.sqlite.org/lang_corefunc.html and use a format with more precision, and you should see the differences I think. --DD
[sqlite] Simple Math Question
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.99 not 23.0) Richard -Original Message- From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users-boun...@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 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] Simple Math Question
On 23 Oct 2015, at 2:45pm, Rousselot, Richard A wrote: > Sorry, I seemed to kick off a nerd fight. :) Please don't apologise. It's the most fun we've had in ages. Simon.
[sqlite] Simple Math Question
AFAICT system 360 machines had BCD in microcode, just like any other basic datatype. Z is only the renamed survivor of 360/xx and 370/xxx "boxes". At 14:33 23/10/2015, you wrote: >--- >On Fri, Oct 23, 2015 at 3:08 AM, wrote: > > > 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. > > >???Unless the architecture implements the newest IEEE-754-2008 "decimal >floating point"??? >ref: https://en.wikipedia.org/wiki/Decimal_floating_point > >???I, personally, only know of one such machine. And it is not really >"popular", except with really _huge_ customers: The IBM z series machine. >It can run 5 different OSes: 4 are IBM proprietary the 5th is Linux. This >machine is not Intel compatible, in any way, shape, or form. And it is >super expensive. Which is why it's not "popular". Running Linux, the >largest has 114 cores and SMT for 228 simultaneous "threads", all in a >single "box". OK, it is a _big_ box . >https://pbs.twimg.com/media/BP8HYj9CQAA17g3.jpg:medium That's about the >size on an entire 19 inch rack (more or less). > >Also, as Mr. Long indicated, this entire series of machines have a decimal >data type in the hardware (normally called "packed decimal", which "packs" >2 decimal digits per bytes, except for the last byte which reserves the >last nybble for a sign indicator). There are instructions which translate >from binary to/from packed decimal and packed decimal to/from "printable" >decimal (called zoned decimal for historical reasons due to old style >punched cards). > > > > > > > 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 > >-- > >Schrodinger's backup: The condition of any backup is unknown until a >restore is attempted. > >Yoda of Borg, we are. Futile, resistance is, yes. Assimilated, you >will be. > >He's about as useful as a wax frying pan. > >10 to the 12th power microphones = 1 Megaphone > >Maranatha! <>< >John McKown >___ >sqlite-users mailing list >sqlite-users at mailinglists.sqlite.org >http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >---
[sqlite] Simple Math Question
Dominique,
Thanks for that idea but now I have more questions?
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???
SELECT
printf('%4.1000f',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,
printf('%4.1000f',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,
printf('%4.1000f',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
Result
25.0
000|no|22.99
00|yes|21.00
00|no
-Original Message-
From: sqlite-users-bounces at mailinglists.sqlite.org
[mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Dominique
Devienne
Sent: Friday, October 23, 2015 8:54 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Simple Math Question
On Fri, Oct 23, 2015 at 3:45 PM, Rousselot, Richard A < Richard.A.Rousselot at
centurylink.com> wrote:
> Anyhow, one last question. If the case statements are evaluating
> something that is not a whole number (or better yet not exactly
> matching on both sides of the equation) . Why is it that when
> displaying the results of the math sqlite does not produce something
> like 25.03 or 22.999? Shouldn't the result have all
> significant digits showing?
> I guess that is why these results seem misleading.
>
The output is done by sqlite3.exe, the command line shell, and you'd have to
look at its code to know for sure.
But instead, take control of the formatting, by using printf() from
https://www.sqlite.org/lang_corefunc.html and use a format with more precision,
and you should see the differences I think. --DD
[sqlite] Simple Math Question
Sorry, I seemed to kick off a nerd fight. :) Anyhow, one last question. If the case statements are evaluating something that is not a whole number (or better yet not exactly matching on both sides of the equation) . Why is it that when displaying the results of the math sqlite does not produce something like 25.03 or 22.999? Shouldn't the result have all significant digits showing? I guess that is why these results seem misleading. Richard From: Rousselot, Richard A Sent: Thursday, October 22, 2015 2:45 PM To: 'sqlite-users at mailinglists.sqlite.org' Cc: Rousselot, Richard A Subject: 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] Simple Math Question
On Thu, Oct 22, 2015 at 09:27:43PM +0100, Simon Slavin wrote: > Financial software frequently handles all currency amounts as pence or > cents for the reasons you've just found out. Most financial processing is done on mainframes which have had fixed point decimal types (in hardware) since the early 1960s. If you use the right types this problem doesn't happen and about the only thing you have to think of is rounding properly (for which there is some amount of software support). But yeah, if you don't have a fixed decimal type and you try to use floats for money you're gonna get sued eventually. Don't do that... /jl -- ASCII ribbon campaign ( ) Powered by Lemote Fuloong against HTML e-mail X Loongson MIPS and OpenBSD and proprietary/ \http://www.mutt.org attachments / \ Code Blue or Go Home! Encrypted email preferred PGP Key 2048R/DA65BC04
[sqlite] Simple Math Question
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_id=1282755 On Fri, Oct 23, 2015 at 11:53 AM, Scott Hess 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.9 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.99, so it printed 25, whereas on the near-23 case > it was closer to 22.9 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.99 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 > > > 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
[sqlite] Simple Math Question
I didn't see this go through, so I'm sending it again. I got an Alexa spam, so maybe that's part of it. I ran the following code on my ARM processor: double c25 = 0.0; c25 += 9.2; c25 += 7.9; c25 += 0.0; c25 += 4.0; c25 += 2.6; c25 += 1.3; double n25 = 25.0; double c23 = 0.0; c23 += 9.2; c23 += 7.8; c23 += 0.0; c23 += 3.0; c23 += 1.3; c23 += 1.7; double n23 = 23.0; double c21 = 0.0; c21 += 9.2; c21 += 7.9; c21 += 0.0; c21 += 1.0; c21 += 1.3; c21 += 1.6; double n21 = 21.0; My debugger shows the following: C25: 2.5003E+1 (0x40390001) N25: 25.0 (0x4039) C23: 23.0 N23: 23.0 C21: 2.1003E+1 (0x40350001) N21: 21 (0x4035) The error is down in the least significant bit. -Original Message- From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Jim Callahan Sent: Friday, October 23, 2015 11:19 AM To: General Discussion of SQLite Database Subject: Re: [sqlite] Simple Math Question Pocket calculators and COBOL used binary coded decimal (bcd) numbers to avoid the representation/round off issues. But this meant another entire number type (supported with addition, subtraction and having to be type checked in functions) in addition to integer and floating point; most found it easier to use integers to keep track on pennies... > On Fri, Oct 23, 2015 at 11:05 AM, Scott Hess wrote: > > On Fri, Oct 23, 2015 at 7:39 AM, Dominique Devienne > > 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 ___ sqlite-users mailing list sqlite-users at mailinglists.sqlite.org 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] Simple Math Question
Pocket calculators and COBOL used binary coded decimal (bcd) numbers to avoid the representation/round off issues. But this meant another entire number type (supported with addition, subtraction and having to be type checked in functions) in addition to integer and floating point; most found it easier to use integers to keep track on pennies... On Fri, Oct 23, 2015 at 11:05 AM, Scott Hess wrote: > On Fri, Oct 23, 2015 at 7:39 AM, Dominique Devienne > 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 >
[sqlite] Simple Math Question
On Fri, Oct 23, 2015 at 10:45 AM, Alessandro Marzocchi < alessandro.marzocchi at gmail.com> wrote: > Scott actually all base2 fractions are rapresentable as base10 > fractions... 10 is divisable by 2. > As for Richard try to think about this... computer does 2 base > calculations as we usually do calculation in base 10. But the same would > happend to us when talking about different bases.. > Let's say we want to add 3 times 20 minutes (expressed in decimal of hours > up to 6th digit). 0h20' is 0.33 hours . If you multiply that for 3 you > get 0.99.. not 1 as you expect. The > Yes, they are. What did I write that leads you to believe I don't understand that? Or are you addressing a different Scott (as there are three in this conversation)? I'm not upset or angry or demanding satisfaction, just trying to understand where my commentary went wrong and if I need to correct anything for the record. When I say floating point calculations are "inexact" what I mean is "potentially inexact" because conversion from decimal in source code to binary floating point in memory is potentially inexact, the arithmetic performed will potentially involve rounding of some form, and the final conversion back from binary floating point to decimal representation for humans can only work with what is left over after those previous potential approximations. > Il 23/ott/2015 18:31, "Scott Robison" ha > scritto: > > > On Fri, Oct 23, 2015 at 9: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. > > > > > > > This is true to an extent, and there are ways to display something "more > > exact". But the library programmers wrote code to format floating point > > numbers in a way that is appropriate for display to humans. Knowing that > > floating point calculations are inexact, they round values after a > certain > > number of decimal places, as most applications expect to see something > like > > "25" not "24.9995" (numbers made up). > > > > > > > > > > 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.99 > 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
[sqlite] Simple Math Question
On Fri, Oct 23, 2015 at 9:33 AM, Marc L. Allen wrote: > I ram the following code on my ARM processor: > > double c25 = 0.0; > c25 += 9.2; > c25 += 7.9; > c25 += 0.0; > c25 += 4.0; > c25 += 2.6; > c25 += 1.3; > double n25 = 25.0; > It might be illustrative to use the web app at http://babbage.cs.qc.edu/courses/cs341/IEEE-754.html to look at the internal representation of the numbers used in the computation. (Note that below ^ is exponentiation, not xor, and the b suffixed numbers are in binary, so 10 is actually 2 decimal, 11 is 3, etc. Also I'm not rounding numbers just for simplicity.) 9.2 = 1.0010011001100110011001100110011001100110011001100110b * 10b ^ 11b 7.9 = 1.100110011001100110011001100110011001100110011010b * 10b ^ 10b 0.0 = 0.b * 10b ^ 00b 4.0 = 1.b * 10b ^ 10b 2.6 = 1.0100110011001100110011001100110011001100110011001101b * 10b ^ 01b 1.3 = 1.0100110011001100110011001100110011001100110011001101b * 10b ^ 00b To add those numbers together, you have to get all the exponents aligned: 9.2 = 1.001001100110011001100110011001100110011001100110011b * 10b ^ 11b 7.9 = 0.110011001100110011001100110011001100110011001101000b * 10b ^ 11b 0.0 = 0.000b * 10b ^ 11b 4.0 = 0.100b * 10b ^ 11b 2.6 = 0.0101001100110011001100110011001100110011001100110011010b * 10b ^ 11b 1.3 = 0.0010100110011001100110011001100110011001100110011001101b * 10b ^ 11b When you add those together you get: 11.000b * 10b ^ 11b IEEE-754 double precision format requires normalization, so first we need to move the binary point left so that there is a single 1 digit to its left: 1.1000b * 10b ^ 100b This format also requires exactly / only 52 digits to the right to the binary point, so we'll drop some digits. There are rounding modes available, but I'm just doing to truncate. 1.1000b * 10b ^ 100b When converted back to decimal, you get 24.996 as the result. Because of the detail and complexity of the IEEE-754 standard, there are a lot of details, such as rounding modes and how they are used when converting the decimal values to binary and when converting the binary sum back to decimal. If going to the other extreme, you could get 25.004 as the result if the above values were handled differently. Regardless, binary floating point can only approximately express our normal concept of numbers (as many others have pointed out in this thread). I just thought it might be illustrative to not just show the final results, but to show how those final results (could) come about. > double c23 = 0.0; > c23 += 9.2; > c23 += 7.8; > c23 += 0.0; > c23 += 3.0; > c23 += 1.3; > c23 += 1.7; > double n23 = 23.0; > > double c21 = 0.0; > c21 += 9.2; > c21 += 7.9; > c21 += 0.0; > c21 += 1.0; > c21 += 1.3; > c21 += 1.6; > double n21 = 21.0; > > My debugger shows the following: > > C25: 2.5003E+1 (0x40390001) > N25: 25.0 (0x4039) > C23: 23.0 > N23: 23.0 > C21: 2.1003E+1 (0x40350001) > N21: 21 (0x4035) > > The error is down in the least significant bit. > > -Original Message- > From: sqlite-users-bounces at mailinglists.sqlite.org [mailto: > sqlite-users-bounces at mailinglists.sqlite.org] On Behalf Of Jim Callahan > Sent: Friday, October 23, 2015 11:19 AM > To: General Discussion of SQLite Database > Subject: Re: [sqlite] Simple Math Question > > Pocket calculators and COBOL used binary coded decimal (bcd) numbers to > avoid the representation/round off issues. But this meant another entire > number type (supported with addition, subtraction and having to be type > checked in functions) in addition to integer and floating point; most found > it easier to use integers to keep track on pennies... > > On Fri, Oct 23, 2015 at 11:05 AM, Scott Hess wrote: > > > On Fri, Oct 23, 2015 at 7:39 AM, Dominique Devienne > > > > 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
[sqlite] ***suspected spam*** Re: Simple Math Question
On Fri, Oct 23, 2015 at 10:47 AM, Hick Gunter wrote: > Once the binary representation of an integer value becomes longer than the > mantissa, even integers will start to fall into the space in between > adjacent floating point values... > > asql> select (9007199254740992.0 + 1.0) - (9007199254740992.0 + 2.0); > (9007199254740992.0 + 1.0) - (9007199254740992.0 + 2.0) > --- > -2.0 > asql> select (9007199254740992.0 + 1.0) - (9007199254740992.0); > (9007199254740992.0 + 1.0) - (9007199254740992.0) > - > 0.0 See also http://stackoverflow.com/questions/1848700/biggest-integer-that-can-be-stored-in-a-double for more details. --DD
[sqlite] Simple Math Question
You're right, any base-2 representation right of the decimal should be precise to represent in base-10. But it's the kind of thing where if you find yourself counting on it, you probably made a grave error earlier in your design :-). -scott On Fri, Oct 23, 2015 at 10:40 AM, Alessandro Marzocchi < alessandro.marzocchi at gmail.com> wrote: > Sorry, i replied to wrong Scott Hess... mine was meant to be a reply to his > message... > " 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". > Sorry for my error. > Il 23/ott/2015 19:18, "Scott Robison" ha > scritto: > > On Fri, Oct 23, 2015 at 10:45 AM, Alessandro Marzocchi < > alessandro.marzocchi at gmail.com> wrote: > > > Scott actually all base2 fractions are rapresentable as base10 > > fractions... 10 is divisable by 2. > > As for Richard try to think about this... computer does 2 base > > calculations as we usually do calculation in base 10. But the same would > > happend to us when talking about different bases.. > > Let's say we want to add 3 times 20 minutes (expressed in decimal of > hours > > up to 6th digit). 0h20' is 0.33 hours . If you multiply that for 3 > you > > get 0.99.. not 1 as you expect. The > > > > Yes, they are. What did I write that leads you to believe I don't > understand that? Or are you addressing a different Scott (as there are > three in this conversation)? I'm not upset or angry or demanding > satisfaction, just trying to understand where my commentary went wrong and > if I need to correct anything for the record. > > When I say floating point calculations are "inexact" what I mean is > "potentially inexact" because conversion from decimal in source code to > binary floating point in memory is potentially inexact, the arithmetic > performed will potentially involve rounding of some form, and the final > conversion back from binary floating point to decimal representation for > humans can only work with what is left over after those previous potential > approximations. > > > > Il 23/ott/2015 18:31, "Scott Robison" ha > > scritto: > > > > > On Fri, Oct 23, 2015 at 9: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. > > > > > > > > > > This is true to an extent, and there are ways to display something > "more > > > exact". But the library programmers wrote code to format floating point > > > numbers in a way that is appropriate for display to humans. Knowing > that > > > floating point calculations are inexact, they round values after a > > certain > > > number of decimal places, as most applications expect to see something > > like > > > "25" not "24.9995" (numbers made up). > > > > > > > > > > > > > > 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.99 > > 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 > > > >
[sqlite] Simple Math Question
On Oct 23, 2015, at 10:36 AM, Scott Hess wrote: > > Indeed, and the cost was the need to have two completely independent math > systems, one precise and one fast. For obvious reasons over time people > who did a lot of math just figured out how to make the fast one precise > enough for their needs, so mostly nobody wanted the precise one. The > obvious choice at that point was to reclaim that silicon space to make > everything else faster, and we ended up here. Also the fact that most older CISC chips did BCD instructions in microcode, in a time when a non-trivial amount of software was written in assembly or with very crude compilers. With modern, RISC-ish systems, it is often almost as fast to do such things in application code, especially with a good compiler that can optimize the lower-level instructions for a specific core design. -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Intelligence is like underwear: it is important that you have it, but showing it to the wrong people has the tendency to make them feel uncomfortable." -- Angela Johnson
[sqlite] Simple Math Question
On Oct 23, 2015, at 7:41 AM, Jean-Christophe Deschamps wrote: > AFAICT system 360 machines had BCD in microcode, just like any other basic > datatype. Z is only the renamed survivor of 360/xx and 370/xxx "boxes". > The Motorola 680x0 chips (used in the original Macintosh systems) also had BCD instructions. -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Intelligence is like underwear: it is important that you have it, but showing it to the wrong people has the tendency to make them feel uncomfortable." -- Angela Johnson
[sqlite] Simple Math Question
I ram the following code on my ARM processor: double c25 = 0.0; c25 += 9.2; c25 += 7.9; c25 += 0.0; c25 += 4.0; c25 += 2.6; c25 += 1.3; double n25 = 25.0; double c23 = 0.0; c23 += 9.2; c23 += 7.8; c23 += 0.0; c23 += 3.0; c23 += 1.3; c23 += 1.7; double n23 = 23.0; double c21 = 0.0; c21 += 9.2; c21 += 7.9; c21 += 0.0; c21 += 1.0; c21 += 1.3; c21 += 1.6; double n21 = 21.0; My debugger shows the following: C25: 2.5003E+1 (0x40390001) N25: 25.0 (0x4039) C23: 23.0 N23: 23.0 C21: 2.1003E+1 (0x40350001) N21: 21 (0x4035) The error is down in the least significant bit. -Original Message- From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Jim Callahan Sent: Friday, October 23, 2015 11:19 AM To: General Discussion of SQLite Database Subject: Re: [sqlite] Simple Math Question Pocket calculators and COBOL used binary coded decimal (bcd) numbers to avoid the representation/round off issues. But this meant another entire number type (supported with addition, subtraction and having to be type checked in functions) in addition to integer and floating point; most found it easier to use integers to keep track on pennies... On Fri, Oct 23, 2015 at 11:05 AM, Scott Hess wrote: > On Fri, Oct 23, 2015 at 7:39 AM, Dominique Devienne > > 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 > ___ sqlite-users mailing list sqlite-users at mailinglists.sqlite.org 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] Simple Math Question
On Fri, Oct 23, 2015 at 9: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. > This is true to an extent, and there are ways to display something "more exact". But the library programmers wrote code to format floating point numbers in a way that is appropriate for display to humans. Knowing that floating point calculations are inexact, they round values after a certain number of decimal places, as most applications expect to see something like "25" not "24.9995" (numbers made up). > > 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.99 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 > 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 > -- Scott Robison
[sqlite] Simple Math Question
On Fri, Oct 23, 2015 at 9:33 AM, Jay Kreibich wrote: > > On Oct 23, 2015, at 7:41 AM, Jean-Christophe Deschamps > wrote: > > > AFAICT system 360 machines had BCD in microcode, just like any other > basic datatype. Z is only the renamed survivor of 360/xx and 370/xxx > "boxes". > > > > > The Motorola 680x0 chips (used in the original Macintosh systems) also had > BCD instructions. > As did the Intel 80x86 & MOS 65xx chips (practically every 8-bit era computer from Apple, Atari, Commodore). > > -j > > -- > Jay A. Kreibich < J A Y @ K R E I B I.C H > > > "Intelligence is like underwear: it is important that you have it, but > showing it to the wrong people has the tendency to make them feel > uncomfortable." -- Angela Johnson > > > > > > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > -- Scott Robison
[sqlite] Simple Math Question
IEEE754 is an extension that is located in the ext/misc of the standard
distribution. Of course, before equality testing the numbers must be
"normalized" so they have the same exponent ...
SQLite version 3.9.1 2015-10-22 18:06:40
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> SELECT
...>(9.2+7.9+0+4.0+2.6+1.3),
...>ieee754(9.2+7.9+0+4.0+2.6+1.3),
...>ieee754(25.0),
...>case when (9.2+7.9+0+4.0+2.6+1.3)=25.0 then "yes" else
"no" end;
25.0|ieee754(7036874417766401,-48)|ieee754(25,0)|no
>>> Decimal(7036874417766401) * Decimal(2**-48)
Decimal('25.00355271367880')
sqlite> SELECT
...>(9.2+7.8+0+3.0+1.3+1.7),
...>ieee754(9.2+7.8+0+3.0+1.3+1.7),
...>ieee754(23.0),
...>case when (9.2+7.8+0+3.0+1.3+1.7)=23.0 then "yes" else
"no" end;
23.0|ieee754(23,0)|ieee754(23,0)|yes
sqlite> SELECT
...>(9.2+7.9+0+1.0+1.3+1.6),
...>ieee754(9.2+7.9+0+1.0+1.3+1.6),
...>ieee754(21.0),
...>case when (9.2+7.9+0+1.0+1.3+1.6)=21.0 then "yes" else
"no" end;
21.0|ieee754(5910974510923777,-48)|ieee754(21,0)|no
>>> Decimal(5910974510923777) * Decimal(2**-48)
Decimal('21.00355271367880')
> -Original Message-
> From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users-
> bounces at mailinglists.sqlite.org] On Behalf Of Dominique Devienne
> Sent: Friday, 23 October, 2015 08:39
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] Simple Math Question
>
> 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 :)
>
> To know for sure, you'd need Keith's custom ieee754() function probably.
> Until you can have a look at the actual bits of the computed double, to
> see
> the sign/exponent/mantissa at the binary level, hard to say.
> Could be your use of 1000f too! 15 decimal digits is typical for double.
> --DD
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Simple Math Question
On Fri, Oct 23, 2015 at 10:09 AM, Scott Doctor 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] Simple Math Question
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 scott at scottdoctor.com On 10/23/2015 12:14 AM, Dominique Devienne wrote: > On Thu, Oct 22, 2015 at 11:55 PM, Igor Tandetnik > 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 > 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
[sqlite] Simple Math Question
On Fri, Oct 23, 2015 at 9: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. > 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] Simple Math Question
On Thu, Oct 22, 2015 at 11:55 PM, Igor Tandetnik 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] Simple Math Question
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.9 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.99, so it printed 25, whereas on the near-23 case it was closer to 22.9 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.99 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 > 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
[sqlite] ***suspected spam*** Re: Simple Math Question
>> 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 >> > >Very true, and a corollary is that using a fixed tolerance for floating point >comparisons (like 1e-14 suggested elsewhere in this thread) doesn't work in >general. > It's fine if you know the magnitude of the numbers you're comparing, but once > you get to numbers above 100, abs(f1 - f2) < 1e-14 becomes the same as saying > f1 == f2, because even a single bit of error at this magnitude will be larger > than 1e-14. >Some languages provide a way to determine the distance between a given >floating point value and the next largest representable number (eg the Math.ulp > function in java), but I'm not sure if SQL provides such? Or if its even in > scope... Once the binary representation of an integer value becomes longer than the mantissa, even integers will start to fall into the space in between adjacent floating point values... asql> select (9007199254740992.0 + 1.0) - (9007199254740992.0 + 2.0); (9007199254740992.0 + 1.0) - (9007199254740992.0 + 2.0) --- -2.0 asql> select (9007199254740992.0 + 1.0) - (9007199254740992.0); (9007199254740992.0 + 1.0) - (9007199254740992.0) - 0.0 ___ Gunter Hick Software Engineer Scientific Games International GmbH FN 157284 a, HG Wien Klitschgasse 2-4, A-1130 Vienna, Austria Tel: +43 1 80100 0 E-Mail: hick at scigames.at This communication (including any attachments) is intended for the use of the intended recipient(s) only and may contain information that is confidential, privileged or legally protected. Any unauthorized use or dissemination of this communication is strictly prohibited. If you have received this communication in error, please immediately notify the sender by return e-mail message and delete all copies of the original communication. Thank you for your cooperation.
[sqlite] Simple Math Question
On Fri, Oct 23, 2015 at 8:19 AM, Jim Callahan < jim.callahan.orlando at gmail.com> wrote: > Pocket calculators and COBOL used binary coded decimal (bcd) numbers to > avoid the representation/round off issues. But this meant another entire > number type (supported with addition, subtraction and having to be type > checked in functions) in addition to integer and floating point; most found > it easier to use integers to keep track on pennies... Indeed, and the cost was the need to have two completely independent math systems, one precise and one fast. For obvious reasons over time people who did a lot of math just figured out how to make the fast one precise enough for their needs, so mostly nobody wanted the precise one. The obvious choice at that point was to reclaim that silicon space to make everything else faster, and we ended up here. -scott
[sqlite] ***suspected spam*** Re: Simple Math Question
>17 -> 0x10001 mantissa >-1 -> 0xf exponent ( or however many bits the exponent is represented by >exact Still thinking in decimal... Value = mantissa * 2 ^ exponent And not Vale = mantissa * 10 ^ exponent ___ Gunter Hick Software Engineer Scientific Games International GmbH FN 157284 a, HG Wien Klitschgasse 2-4, A-1130 Vienna, Austria Tel: +43 1 80100 0 E-Mail: hick at scigames.at This communication (including any attachments) is intended for the use of the intended recipient(s) only and may contain information that is confidential, privileged or legally protected. Any unauthorized use or dissemination of this communication is strictly prohibited. If you have received this communication in error, please immediately notify the sender by return e-mail message and delete all copies of the original communication. Thank you for your cooperation.
[sqlite] Simple Math Question
On Fri, Oct 23, 2015 at 7:39 AM, Dominique Devienne 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] Simple Math Question
On Fri, Oct 23, 2015 at 7:41 AM, Jean-Christophe Deschamps wrote: > AFAICT system 360 machines had BCD in microcode, just like any other basic > datatype. Z is only the renamed survivor of 360/xx and 370/xxx "boxes". > I believe that's true. But only the latest z machines have DFP (Decimal _Floating_ Point) in them. And the OP to which I was replying was talking about decimal values & floating point ?(which has been binary in the past). -- Schrodinger's backup: The condition of any backup is unknown until a restore is attempted. Yoda of Borg, we are. Futile, resistance is, yes. Assimilated, you will be. He's about as useful as a wax frying pan. 10 to the 12th power microphones = 1 Megaphone Maranatha! <>< John McKown
[sqlite] Simple Math Question
On Fri, Oct 23, 2015 at 3:08 AM, wrote: > 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. > ?Unless the architecture implements the newest IEEE-754-2008 "decimal floating point"? ref: https://en.wikipedia.org/wiki/Decimal_floating_point ?I, personally, only know of one such machine. And it is not really "popular", except with really _huge_ customers: The IBM z series machine. It can run 5 different OSes: 4 are IBM proprietary the 5th is Linux. This machine is not Intel compatible, in any way, shape, or form. And it is super expensive. Which is why it's not "popular". Running Linux, the largest has 114 cores and SMT for 228 simultaneous "threads", all in a single "box". OK, it is a _big_ box . https://pbs.twimg.com/media/BP8HYj9CQAA17g3.jpg:medium That's about the size on an entire 19 inch rack (more or less). Also, as Mr. Long indicated, this entire series of machines have a decimal data type in the hardware (normally called "packed decimal", which "packs" 2 decimal digits per bytes, except for the last byte which reserves the last nybble for a sign indicator). There are instructions which translate from binary to/from packed decimal and packed decimal to/from "printable" decimal (called zoned decimal for historical reasons due to old style punched cards). > > 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 > -- Schrodinger's backup: The condition of any backup is unknown until a restore is attempted. Yoda of Borg, we are. Futile, resistance is, yes. Assimilated, you will be. He's about as useful as a wax frying pan. 10 to the 12th power microphones = 1 Megaphone Maranatha! <>< John McKown
[sqlite] Simple Math Question
On 23 Oct 2015, at 2:01am, Keith Medcalf 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 1, 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] Simple Math Question
sqlite> select ieee754(1.7); ieee754(7656119366529843,-52) or ?00011011001100110011001100110011001100110011001100110011 for the mantissa. Note that it is an infinitely repeating fraction. > -Original Message- > From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users- > bounces at mailinglists.sqlite.org] 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 > 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 > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Simple Math Question
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: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users- > bounces at mailinglists.sqlite.org] 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 > scott at scottdoctor.com > > On 10/23/2015 12:14 AM, Dominique Devienne wrote: > > On Thu, Oct 22, 2015 at 11:55 PM, Igor Tandetnik > 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 > > 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
[sqlite] Simple Math Question
17 -> 0x10001 mantissa -1 -> 0xf exponent ( or however many bits the exponent is represented by exact Scott Doctor scott at scottdoctor.com 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: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users- >> bounces at mailinglists.sqlite.org] 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 >> scott at scottdoctor.com >> >> On 10/23/2015 12:14 AM, Dominique Devienne wrote: >>> On Thu, Oct 22, 2015 at 11:55 PM, Igor Tandetnik >> 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 >>> 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 > > > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > >
[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 scott at scottdoctor.com On 10/23/2015 12:14 AM, Dominique Devienne wrote: > On Thu, Oct 22, 2015 at 11:55 PM, Igor Tandetnik > 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 > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > >
[sqlite] Alexa Phishing
Le Mon, 19 Oct 2015 08:41:28 +0200, "Gert Venter" a ?crit : > I also received an eMail from alexa within 15 minutes after registering. > > GAV > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users I didn't read so far this discussion... now I don't need to do it! I got the pictures... sent for her iPhone... I blame steve jobs for that!
[sqlite] Simple Math Question
On 22 Oct 2015 at 21:25, Richard A Rousselot 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 < > Richard.A.Rousselot at centurylink.com> 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
