[sqlite] Simple Math Question
On Saturday, 7 November, 2015 09:08, James K. Lowden wrote:
> On Fri, 06 Nov 2015 22:16:57 -0700
> "Keith Medcalf" wrote:
> > I wrote a function called "ulps" which can be used as an extension to
> > SQLite3
> Bravo, Keith!
> One suggestion, if I may. If you expect "ulps" to be used to test for
> equality, perhaps it would be more convenient to ignore the sign.
> Something like
> fequal(x, y, delta) === abs(ulps(x -y)) < delta
> might express the idea more directly?
> --jkl
Simple wrappers can be created that emulate all the equality tests.
feq(x, y, delta) --> abs(ulps(x, y)) <= delta : x == y
fne(x, y, delta) --> abs(ulps(x, y)) > delta : x != y
fgt(x, y, delta) --> ulps(x, y) > delta: x > y
fge(x, y, delta) --> ulps(x, y) => -delta : x >= y
flt(x, y, delta) --> ulps(x, y) < -delta : x < y
fle(x, y, delta) --> ulps(x, y) <= delta : x <= y
ala:
static double epsilon(double value)
{
int exponent;
double mantissa = frexp(value, );
double epsilon = ldexp(1.0, exponent - 53);
return epsilon;
}
static double distance(double x, double y)
{
return (x - y) / epsilon(x);
}
SQLITE_PRIVATE void _ulp(sqlite3_context *context, int argc, sqlite3_value
**argv)
{
sqlite3_result_double(context, epsilon(sqlite3_value_double(argv[0])));
}
SQLITE_PRIVATE void _ulps(sqlite3_context *context, int argc, sqlite3_value
**argv)
{
sqlite3_result_double(context, distance(sqlite3_value_double(argv[0]),
sqlite3_value_double(argv[1])));
}
SQLITE_PRIVATE void _feq(sqlite3_context *context, int argc, sqlite3_value
**argv)
{
double ulps = distance(sqlite3_value_double(argv[0]),
sqlite3_value_double(argv[1]));
if (fabs(ulps) <= fabs(sqlite3_value_double(argv[2])))
sqlite3_result_int(context, 1);
else
sqlite3_result_int(context, 0);
}
SQLITE_PRIVATE void _fne(sqlite3_context *context, int argc, sqlite3_value
**argv)
{
double ulps = distance(sqlite3_value_double(argv[0]),
sqlite3_value_double(argv[1]));
if (fabs(ulps) > fabs(sqlite3_value_double(argv[2])))
sqlite3_result_int(context, 1);
else
sqlite3_result_int(context, 0);
}
SQLITE_PRIVATE void _fgt(sqlite3_context *context, int argc, sqlite3_value
**argv)
{
double ulps = distance(sqlite3_value_double(argv[0]),
sqlite3_value_double(argv[1]));
if (ulps > fabs(sqlite3_value_double(argv[2])))
sqlite3_result_int(context, 1);
else
sqlite3_result_int(context, 0);
}
SQLITE_PRIVATE void _fge(sqlite3_context *context, int argc, sqlite3_value
**argv)
{
double ulps = distance(sqlite3_value_double(argv[0]),
sqlite3_value_double(argv[1]));
if (ulps >= -fabs(sqlite3_value_double(argv[2])))
sqlite3_result_int(context, 1);
else
sqlite3_result_int(context, 0);
}
SQLITE_PRIVATE void _flt(sqlite3_context *context, int argc, sqlite3_value
**argv)
{
double ulps = distance(sqlite3_value_double(argv[0]),
sqlite3_value_double(argv[1]));
if (ulps < -fabs(sqlite3_value_double(argv[2])))
sqlite3_result_int(context, 1);
else
sqlite3_result_int(context, 0);
}
SQLITE_PRIVATE void _fle(sqlite3_context *context, int argc, sqlite3_value
**argv)
{
double ulps = distance(sqlite3_value_double(argv[0]),
sqlite3_value_double(argv[1]));
if (ulps <= fabs(sqlite3_value_int(argv[2])))
sqlite3_result_int(context, 1);
else
sqlite3_result_int(context, 0);
}
SQLITE_PRIVATE void _nxtaft(sqlite3_context *context, int argc, sqlite3_value
**argv)
{
double x = sqlite3_value_double(argv[0]);
double y = sqlite3_value_double(argv[1]);
sqlite3_result_double(context, _nextafter(x, y));
}
nErr += sqlite3_create_function(db, "nextafter",2,
SQLITE_ANY|SQLITE_DETERMINISTIC, 0, _nxtaft,0, 0);
nErr += sqlite3_create_function(db, "ulp", 1,
SQLITE_ANY|SQLITE_DETERMINISTIC, 0, _ulp, 0, 0);
nErr += sqlite3_create_function(db, "ulps", 2,
SQLITE_ANY|SQLITE_DETERMINISTIC, 0, _ulps, 0, 0);
nErr += sqlite3_create_function(db, "feq", 3,
SQLITE_ANY|SQLITE_DETERMINISTIC, 0, _feq, 0, 0);
nErr += sqlite3_create_function(db, "fne", 3,
SQLITE_ANY|SQLITE_DETERMINISTIC, 0, _fne, 0, 0);
nErr += sqlite3_create_function(db, "fgt", 3,
SQLITE_ANY|SQLITE_DETERMINISTIC, 0, _fgt, 0, 0);
nErr += sqlite3_create_function(db, "fge", 3,
SQLITE_ANY|SQLITE_DETERMINISTIC, 0, _fge, 0, 0);
nErr += sqlite3_create_function(db, "flt", 3,
SQLITE_ANY|SQLITE_DETERMINISTIC, 0, _flt, 0, 0);
nErr += sqlite3_create_function(db, "fle", 3,
SQLITE_ANY|SQLITE_DETERMINISTIC, 0, _fle, 0, 0);
Giving:
>sqlite < ulps.sql
create table test (x real, y real);
insert into test values (9.2 + 7.9
[sqlite] Simple Math Question
On Fri, 06 Nov 2015 22:16:57 -0700 "Keith Medcalf" wrote: > I wrote a function called "ulps" which can be used as an extension to > SQLite3 Bravo, Keith! One suggestion, if I may. If you expect "ulps" to be used to test for equality, perhaps it would be more convenient to ignore the sign. Something like fequal(x, y, delta) === abs(ulps(x -y)) < delta might express the idea more directly? --jkl
[sqlite] Simple Math Question
On 23-10-15 16:39, 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 :) > > I was reading this thread just now. sqlite> select 9.2+7.9+0+4.0+2.6+1.3-25.0, (9.2+7.9)+(0+4.0+2.6+1.3-25.0); 3.5527136788005e-15|0.0 ;) (the reasons are probably explained in the parts of the thread that i did not read ;)
[sqlite] Simple Math Question
On 11/7/2015 3:47 AM, Luuk wrote: > I was reading this thread just now. > > sqlite> select 9.2+7.9+0+4.0+2.6+1.3-25.0, (9.2+7.9)+(0+4.0+2.6+1.3-25.0); > 3.5527136788005e-15|0.0 > > ;) > > > (the reasons are probably explained in the parts of the thread that i > did not read ;) Among other quirks, floating point operations are not commutative. Let L be a large number, S a small number - sufficiently small that L + S == L (S is lost to rounding error). Then clearly (L + S) - L == 0, (L - L) + S == S. This matters a lot for many computations, where one can choose just the right order of operations to reduce the error, and choosing wrong means errors accumulate and invalidate the result. See also: https://en.wikipedia.org/wiki/Numerical_stability -- Igor Tandetnik
[sqlite] Simple Math Question
> On Thursday, 22 October, 2015, at 13:45, Rousselot, Richard A
> said:
> 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 know this has been discussed to death. I wrote a function called "ulps"
which can be used as an extension to SQLite3 and is defined as follows
(compiles in MSVC and GCC , should work on all compilers I think):
SQLITE_PRIVATE void _ulps(sqlite3_context *context, int argc, sqlite3_value
**argv)
{
register double x = sqlite3_value_double(argv[0]);
register double y = sqlite3_value_double(argv[1]);
register double u1 = fabs(_nextafter(x, 1.7976931348623157e+308) - x);
register double u2 = fabs(_nextafter(x, -1.7976931348623157e+308) - x);
register double au = (u1 + u2) / 2.0;
sqlite3_result_double(context, (x - y) / au);
}
It computes the absolute distance between x and the next representable double
towards +Inf, and the absolute distance between x and the next representable
double towards -Inf. It then averages these distances and returns the number
of times this interval occurs in the difference between x and y.
Running the original problem:
SQLite version 3.10.0 2015-11-07 01:19:00
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> .mode column
sqlite> .header on
sqlite> create table test (x real, y real);
sqlite> insert into test values (9.2 + 7.9 + 0 + 4.0 + 2.6 + 1.3, 25.0);
sqlite> insert into test values (9.2 + 7.8 + 0 + 3.0 + 1.3 + 1.7, 23.0);
sqlite> insert into test values (9.2 + 7.9 + 0 + 1.0 + 1.3 + 1.6, 21.0);
sqlite> select x, y, case when x == y then 'yes' else 'no' end as equal,
ulps(x, y) as ulps from test;
x y equal ulps
-- -- -- --
25.025.0no 1.0
23.023.0yes 0.0
21.021.0no 1.0
sqlite>
This shows that although the x and y may not be exactly equal, they are merely
1 ULP different from each other using the representation precision of x.
This works for comparing numbers of any scale ... of course if the numbers are
not "relatively equal" the number of ULPS between them may huge.
So, instead of "x == y" using "abs(ulps(x - y)) < 5" is true if x and y are
within 5 ULPS of each other.
[sqlite] Simple Math Question
On 29 October 2015 at 09:46, SQLite mailing list < sqlite-users at mailinglists.sqlite.org> wrote: > > which I understood to mean, "if you can represent it in decimal, you > can represent it in binary". I didn't think that was true, but there > seemed to be concensus that it was. > The consensus was the other way: "If you can represent it in binary, you can represent it in decimal." -Rowan
[sqlite] Simple Math Question
On 29 Oct 2015, at 2:09am, SQLite mailing list wrote: > The consensus was the other way: "If you can represent it in binary, you > can represent it in decimal." Well that one is actually true. If you can represent any non-recurring fraction in binary, in decimal it's a non-recurring fraction ending in a 5. Simon.
[sqlite] Simple Math Question
At 23:34 28/10/2015, you wrote: >--- > > Those binary representations can be converted back into precise decimal > > representations, but those decimal representations will not be the > original > > decimal values, because they were translated from decimal strings into > > binary floating-point values and back into decimal strings. > > > -scott > >This explains the deficiency in the SQLite print function, but it doesn't >have to be that way. > >See: Steele, Jr., Guy L., and White, Jon L. How to print floating-point >numbers accurately. In Proc. ACM SIGPLAN ???90 Conf. Prog. Lang. >Design and >Implementation. ACM (White Plains, NY, June 1990), 112?126. ACM SIGPLAN >Noticess 25, 6 (June 1990). > >A retrospective by Steele & White is here: > >http://grouper.ieee.org/groups/754/email/pdfq3pavhBfih.pdf > >I'm not advocating that SQLite add Steele & White's Dragon algorithm, just >pointing out that there are ways to fix the deficiency. > >-- >Doug Currie While it's possible to (somehow) minimize the issues involved with printing a floating-point value (albeit at high cost), the issue of comparing them as is done in the OP is a pretty different beast. There you have to convert a decimal FP target constant to a binary value stored in FP register or memory storage then perform a comparison. And contrary to Simon, I don't think that: >sqlite> CREATE TABLE t(r REAL PRIMARY KEY,t TEXT); >sqlite> INSERT INTO t VALUES (21.0,'twenty one point zero'); >sqlite> INSERT INTO t VALUES (9.2+7.9+0+1.0+1.3+1.6, 'calculation'); should bark for duplicate PK, since the values are hardly equal in practice. (Else SQLite would indeed raise a dup PK error!) BTW and following an entirely distinct thread: I'd rather filter Alexa out myself using my mail client features. jcd at antichoc.net
[sqlite] Simple Math Question
On 28 Oct 2015, at 11:23pm, SQLite mailing list wrote: > This can't possibly work. "Fuzzy equality" is not transitive (x is close > enough to y, y is close enough to z, but x is just far enough from z to be > non-equal), which would break any indexing scheme. Oh crumbs. You're right. I didn't think of it like that. Is there a way to do it correctly ? Simon Slavin.
[sqlite] Simple Math Question
On Thu, 29 Oct 2015 10:09:28 +0800 SQLite mailing list wrote: > The consensus was the other way: "If you can represent it in binary, > you can represent it in decimal." Gah, I see now. Thank you for the clarification. --jkl
[sqlite] Simple Math Question
Sorry, I missed out my point: SQLite version 3.8.10.2 2015-05-20 18:17:19 Enter ".help" for usage hints. sqlite> CREATE TABLE t(r REAL PRIMARY KEY,t TEXT); sqlite> INSERT INTO t VALUES (21.0,'twenty one point zero'); sqlite> INSERT INTO t VALUES (9.2+7.9+0+1.0+1.3+1.6, 'calculation'); sqlite> SELECT * FROM t WHERE r>10; 21.0|twenty one point zero 21.0|calculation It is this that worries me. No that both rows are printed as '21.0', but that SQLite did not use slop when checking to see whether the two values were duplicates. I feel that it should have rejected the second INSERT command because of the duplicate key. Obviously an extremely tiny minority of SQLite databases have a UNIQUE KEY on a REAL column. But fixing this may fix other bugs. Simon Slavin.
[sqlite] Simple Math Question
On 28 Oct 2015, at 10:34pm, SQLite mailing list wrote: > This explains the deficiency in the SQLite print function, but it doesn't > have to be that way. I'm with a previous poster. SQLite is primarily a database system. Its primary jobs are storage and retrieval. It shouldn't really be used to print at all and putting unusual effort into its print functions may not be wise. However, I would support improvement in its floating point calculations, including implementing 'slop' in testing for equality. This is not only for use when expressions include the equal sign, but also for cases where comparing two numbers is done in important internal operations, like checking that primary keys do not include duplicate entries. However, I just tried to create some relevant problems: SQLite version 3.8.10.2 2015-05-20 18:17:19 Enter ".help" for usage hints. sqlite> CREATE TABLE t(r REAL PRIMARY KEY,t TEXT); sqlite> INSERT INTO t VALUES (1,'one'),(2,'two'); sqlite> INSERT INTO t VALUES (1.4,'one point four'),(1.5,'one point five'),(1.6,'one point six');; sqlite> SELECT * FROM t; 1.0|one 2.0|two 1.4|one point four 1.5|one point five 1.6|one point six sqlite> INSERT INTO t VALUES (1.6,'attempt duplication'); Error: UNIQUE constraint failed: t.r sqlite> INSERT INTO t VALUES (1.3+0.3,'attempt duplication'); Error: UNIQUE constraint failed: t.r sqlite> INSERT INTO t VALUES (1.2+0.2,'attempt duplication'); Error: UNIQUE constraint failed: t.r sqlite> INSERT INTO t VALUES (0.2+0.8,'attempt duplication'); Error: UNIQUE constraint failed: t.r sqlite> INSERT INTO t VALUES (0.3+0.7,'attempt duplication'); Error: UNIQUE constraint failed: t.r sqlite> SELECT 0.3+0.7, (0.3+0.7) = (0.2+0.8); 1.0|1 sqlite> SELECT 0.3+0.7, (0.3+0.7) - 1, (0.3+0.7) = 1; 1.0|0.0|1 sqlite> SELECT (9.2+7.9+0+1.0+1.3+1.6),(9.2+7.9+0+1.0+1.3+1.6)-21,(9.2+7.9+0+1.0+1.3+1.6)=21.0; 21.0|3.5527136788005e-15|0 As you can see (remembering that 1 = TRUE), SQLite is correctly noticing the problem when it does pure comparisons, whether in internal collations or for other tests for equality. What it's not doing is checking for near equality when printing. And I'm happy with that. Simon Slavin.
[sqlite] Simple Math Question
On Wed, 28 Oct 2015 17:52:25 + Simon wrote: > On 28 Oct 2015, at 5:08pm, James K. Lowden > wrote: > > > If we accept what you say, above, then why should > > > >> (9.2+7.8+0+3.0+1.3+1.7) > > > > in particular present any problem? There's no division. Each value > > has an exact decimal representation. > > You didn't work it out yourself, did you ? > > 0.2 in binary is 0.0011001100110011... > 0.3 in binary is 0.0100110011001100... > > They both recur at the 1/16th level. 0.7 and 0.8 are, of course, > their complements. Only two tenths don't have problems in binary: > point zero and point five. I didn't work it out. The assertion was > > any base-2 representation right of the decimal should be > > precise to represent in base-10 which I understood to mean, "if you can represent it in decimal, you can represent it in binary". I didn't think that was true, but there seemed to be concensus that it was. Thanks for doing my homework. :-) --jkl P.S., To OFL, I wish the names were preserved in the From, so that mail software preserves the "Simon said, James said" context. It would also be less damaging if the addresses merely mangled with e.g. "-ciao-alexa" inserted. The malware is unlikely to adapt -- one way in which it is *not* like a real virus -- and human beings can easily remove the extra letters. One trick I've used with success is to insert the HTML zero-width space character into the email address. It looks the same, copies and pastes just fine, but scripts scraping a page will copy it verbatim and get a useless address.
[sqlite] Simple Math Question
On 10/28/2015 7:25 PM, SQLite mailing list wrote: > On 28 Oct 2015, at 11:23pm, SQLite mailing list mailinglists.sqlite.org> wrote: > >> This can't possibly work. "Fuzzy equality" is not transitive (x is close >> enough to y, y is close enough to z, but x is just far enough from z to be >> non-equal), which would break any indexing scheme. > > Oh crumbs. You're right. I didn't think of it like that. Is there a way to > do it correctly ? None that I know of. Which is probably why no DB engine is doing anything like this. -- Igor Tandetnik
[sqlite] Simple Math Question
On 10/28/2015 6:52 PM, SQLite mailing list wrote: > However, I would support improvement in its floating point calculations, > including implementing 'slop' in testing for equality. This is not only for > use when expressions include the equal sign, but also for cases where > comparing two numbers is done in important internal operations, like checking > that primary keys do not include duplicate entries. This can't possibly work. "Fuzzy equality" is not transitive (x is close enough to y, y is close enough to z, but x is just far enough from z to be non-equal), which would break any indexing scheme. -- Igor Tandetnik
[sqlite] Simple Math Question
On Wed, Oct 28, 2015 at 6:29 PM, SQLite mailing list < sqlite-users at mailinglists.sqlite.org> wrote: > On 10/28/2015 7:25 PM, SQLite mailing list wrote: > >> On 28 Oct 2015, at 11:23pm, SQLite mailing list < >> sqlite-users at mailinglists.sqlite.org> wrote: >> >> This can't possibly work. "Fuzzy equality" is not transitive (x is close >>> enough to y, y is close enough to z, but x is just far enough from z to be >>> non-equal), which would break any indexing scheme. >>> >> >> Oh crumbs. You're right. I didn't think of it like that. Is there a >> way to do it correctly ? >> > > None that I know of. Which is probably why no DB engine is doing anything > like this. It's probably why some have explicit decimal numeric types available. Often in a database engine the overhead of a manual math implementation is not the dominant factor in performance. -scott
[sqlite] Simple Math Question
> > Those binary representations can be converted back into precise decimal > representations, but those decimal representations will not be the original > decimal values, because they were translated from decimal strings into > binary floating-point values and back into decimal strings. > > -scott This explains the deficiency in the SQLite print function, but it doesn't have to be that way. See: Steele, Jr., Guy L., and White, Jon L. How to print floating-point numbers accurately. In Proc. ACM SIGPLAN ?90 Conf. Prog. Lang. Design and Implementation. ACM (White Plains, NY, June 1990), 112?126. ACM SIGPLAN Notices 25, 6 (June 1990). A retrospective by Steele & White is here: http://grouper.ieee.org/groups/754/email/pdfq3pavhBfih.pdf I'm not advocating that SQLite add Steele & White's Dragon algorithm, just pointing out that there are ways to fix the deficiency. e -- Doug Currie doug.currie at gmail.com
[sqlite] Simple Math Question
On 28 Oct 2015, at 5:08pm, James K. Lowden wrote: > If we accept what you say, above, then why should > >> (9.2+7.8+0+3.0+1.3+1.7) > > in particular present any problem? There's no division. Each value > has an exact decimal representation. You didn't work it out yourself, did you ? 0.2 in binary is 0.0011001100110011... 0.3 in binary is 0.0100110011001100... They both recur at the 1/16th level. 0.7 and 0.8 are, of course, their complements. Only two tenths don't have problems in binary: point zero and point five. Simon.
[sqlite] Simple Math Question
>> (9.2+7.8+0+3.0+1.3+1.7) >in particular present any problem? There's no division. Each value >has an exact decimal representation. I'm prepared to assert that any >permutation of their sums also has an exact decimal representation. >Therefore they should have an exact binary representation, too. Not true. They don't have an exact binary representation. If you check these numbers here.. http://www.h-schmidt.net/FloatConverter/IEEE754.html You'll find 9.2 -> 9.19809265137 7.8 -> 7.80190734863 and so on. So adding these numbers doesn't generate the same answer as doing it in base10 maths Andy Ling --- This email has been scanned for email related threats and delivered safely by Mimecast. For more information please visit http://www.mimecast.com ---
[sqlite] Simple Math Question
On Wed, Oct 28, 2015 at 3:52 PM, SQLite mailing list < sqlite-users at mailinglists.sqlite.org> wrote: > On 28 Oct 2015, at 10:34pm, SQLite mailing list < > sqlite-users at mailinglists.sqlite.org> wrote: > > This explains the deficiency in the SQLite print function, but it doesn't > > have to be that way. > > I'm with a previous poster. SQLite is primarily a database system. Its > primary jobs are storage and retrieval. It shouldn't really be used to > print at all and putting unusual effort into its print functions may not be > wise. > > However, I would support improvement in its floating point calculations, > including implementing 'slop' in testing for equality. This is not only > for use when expressions include the equal sign, but also for cases where > comparing two numbers is done in important internal operations, like > checking that primary keys do not include duplicate entries. > IMHO, this kind of thing can be subtle and full of bugs. For purposes of a value literally typed as a float, I think using the IEEE 754 value as a blob is the right thing to do. In addition, it would be reasonable to have a function for comparison including an epsilon. That wouldn't make sense for indices, though, because of the problem where two values may not be within epsilon of each other, but they may be within epsilon of a third, so insert success becomes order dependent (in one order, 1 of 3 succeeds, in the other order 2 of 3 succeed). Actually, I think I'd be willing to forbid UNIQUE plus REAL entirely :-). -scott (shess at google.com)
[sqlite] Simple Math Question
On Fri, 23 Oct 2015 10:43:44 -0700 Scott Hess wrote: > 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 :-). I'm either brave or naive enough to think I can still add to this discussion. If we accept what you say, above, then why should > (9.2+7.8+0+3.0+1.3+1.7) in particular present any problem? There's no division. Each value has an exact decimal representation. I'm prepared to assert that any permutation of their sums also has an exact decimal representation. Therefore they should have an exact binary representation, too. To the OP, I want to point out that whether or not a fraction can be presented exactly is a function of the base. Consider that 1/3 has no finite decimal representation. But in base 3 it's just 0.1 --jkl
[sqlite] Simple Math Question
On Wed, Oct 28, 2015 at 10:08 AM, James K. Lowden
wrote:
> On Fri, 23 Oct 2015 10:43:44 -0700 Scott Hess wrote:
> > 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 :-).
>
> I'm either brave or naive enough to think I can still add to this
> discussion. If we accept what you say, above, then why should
>
> > (9.2+7.8+0+3.0+1.3+1.7)
>
> in particular present any problem? There's no division. Each value
> has an exact decimal representation. I'm prepared to assert that any
> permutation of their sums also has an exact decimal representation.
> Therefore they should have an exact binary representation, too.
Of those numbers, only 0 and 3.0 have an exact binary representation:
echo 9.2 7.8 0 3.0 1.3 1.7 | xargs -n1 -I{} printf "{} is %a\n" {}
9.2 is 0x1.2p+3
7.8 is 0x1.fp+2
0 is 0x0p+0
3.0 is 0x1.8p+1
1.3 is 0x1.4cccdp+0
1.7 is 0x1.bp+0
Those binary representations can be converted back into precise decimal
representations, but those decimal representations will not be the original
decimal values, because they were translated from decimal strings into
binary floating-point values and back into decimal strings.
-scott
[sqlite] Simple Math Question
On 23 October 2015 at 23:34, 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.
>
To be pendatic, you haven't asked for the result of the internal
calculation. You've asked for that result converted to a printable string
with certain formatting.
To be fair you've asked for plenty digits of precision, and I think it
would be reasonable to say that the failure to provide them by sqlite's
printf is a bug. But the primary use case for printf is to provide output
in *readable* form -- these ludicrous precisions are not part of that use
case, and it seems the formatting algorithms include concessions which
prevent them from being printed correctly.
eg. the system's printf, (via python 2.6.6), vs sqlite's printf:
python> "%.66f" % (9.2+7.9+0+4.0+2.6+1.3)
'25.00355271367880050092935562133789062500'
sqlite> select printf("%.66f", (9.2+7.9+0+4.0+2.6+1.3));
25.00
python> "%.66f" % (1.1+2.6+3.2+0.1+0.1)
'7.09964472863211994990706443786621093750'
sqlite> select printf("%.66f", (1.1+2.6+3.2+0.1+0.1));
7.099000
I think this explains the discrepancy you're seeing. Of course this leaves
you with no straightforward way to get the actual result via the sqlite3
shell, which also applies formatting when it displays SELECT results. I
guess if you really want the exact bits you need to use the ieee754
extension.
Aha, looking at the code I see the reason for the 16-digit cutoff - 16 is
what the counter passed to et_getdigit is initialised to, unless you use
the ! flag to %f. Interestingly this doesn't give the full 66, but does
give more non-zero digits:
sqlite> select printf("%!.66f", (9.2+7.9+0+4.0+2.6+1.3));
25.003551846317
The comments claim that 16 is the default because that's how many
significant digits you have in a 64-bit float. So I'm less convinced now
that there's actually a bug here - nothing that printf does can change the
fact that floating point is inherently inexact, and historically its been
printf's job to /hide/ those innacuracies for the sake of readability which
it is doing here by saying "realistically we can only store 16 significant
digits - anything beyond that is floating point error".
It may still be worth documenting the behaviour though?
-Rowan
[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 > > &
[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.999999 > 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 :) > >
[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
[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_c
[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 :) > > >
[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 >
[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 eq
[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 > > > > > > > >
[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 t
[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] 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] 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
[sqlite] Simple Math Question
Le Thu, 22 Oct 2015 19:45:09 +, "Rousselot, Richard A" a ?crit : > Doing the following math, why is it that the results are not all returning > "yes"? > > SELECT > (9.2+7.9+0+4.0+2.6+1.3), > case when (9.2+7.9+0+4.0+2.6+1.3)=25.0 then "yes" else "no" > end, > (9.2+7.8+0+3.0+1.3+1.7), > case when (9.2+7.8+0+3.0+1.3+1.7)=23.0 then "yes" else "no" > end, > (9.2+7.9+0+1.0+1.3+1.6), > case when (9.2+7.9+0+1.0+1.3+1.6)=21.0 then "yes" else "no" > end > FROM > sometable; > > Result > 25.0 no 23.0 yes 21.0 no > > I'm sure this has an obvious answer but it isn't jumping out at me. > > Richard > > This communication is the property of CenturyLink and may contain > confidential or privileged > information. Unauthorized use of this communication is strictly prohibited > and may be unlawful. > If you have received this communication in error, please immediately notify > the sender by reply > e-mail and destroy all copies of the communication and any attachments. > ___ sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users just for the fun: round-off error... 1/3 has an infinite number of decimal number. meanwhile a computer can only store a finite number of decimal... this is the story who leads Lorenz to talk about his butterfly and the chaos... I do not remember the whole story but iirc that was when he worked on some numerical weather prediction. Data stored in some magnetic device (sound like science-fiction for someone young like me...) and the data in the RAM haven't the same number of decimal, so the calculus after several iterations gave totaly different results... regards, Nicolas J. n.b. : "meanwhile a computer can only store a finite number of decimal..." same in groceries when you see 98c and you have to pay 1$... thiefs!
[sqlite] Simple Math Question
If I recall, he had a printout of the state at each time unit. The state was represented as a set of numbers out to 6 decimal points. He wanted to rerun part of the simulation, so he entered in those super-precise numbers and let them run, but the model quick diverged because those numbers just weren't precise enough. > On Oct 22, 2015, at 10:56 PM, Nicolas J?ger > wrote: > > Le Thu, 22 Oct 2015 19:45:09 +, > "Rousselot, Richard A" a ?crit : > >> Doing the following math, why is it that the results are not all returning >> "yes"? >> >> SELECT >>(9.2+7.9+0+4.0+2.6+1.3), >>case when (9.2+7.9+0+4.0+2.6+1.3)=25.0 then "yes" else "no" >> end, >>(9.2+7.8+0+3.0+1.3+1.7), >>case when (9.2+7.8+0+3.0+1.3+1.7)=23.0 then "yes" else "no" >> end, >>(9.2+7.9+0+1.0+1.3+1.6), >>case when (9.2+7.9+0+1.0+1.3+1.6)=21.0 then "yes" else "no" >> end >> FROM >>sometable; >> >> Result >> 25.0 no 23.0 yes 21.0 no >> >> I'm sure this has an obvious answer but it isn't jumping out at me. >> >> Richard >> >> This communication is the property of CenturyLink and may contain >> confidential or privileged >> information. Unauthorized use of this communication is strictly prohibited >> and may be unlawful. >> If you have received this communication in error, please immediately notify >> the sender by reply >> e-mail and destroy all copies of the communication and any attachments. >> ___ sqlite-users mailing list >> sqlite-users at mailinglists.sqlite.org >> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > > just for the fun: round-off error... > > 1/3 has an infinite number of decimal number. meanwhile a computer can only > store a finite > number of decimal... > > this is the story who leads Lorenz to talk about his butterfly and the > chaos... I do not remember > the whole story but iirc that was when he worked on some numerical weather > prediction. Data > stored in some magnetic device (sound like science-fiction for someone young > like me...) and the > data in the RAM haven't the same number of decimal, so the calculus after > several iterations > gave totaly different results... > > regards, > Nicolas J. > > n.b. : "meanwhile a computer can only store a finite number of decimal..." > same in groceries when > you see 98c and you have to pay 1$... thiefs! > ___ > sqlite-users mailing list > 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 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 > "no" end > FROM > sometable; > Try the same with integers as you'll likely see different results. Floating-point math is fraught with problems when it comes to expecting exact results at a specific precision. See: http://floating-point-gui.de/ the first example of which demonstrates the problem you are seeing. -- - stephan beal http://wanderinghorse.net/home/stephan/ http://gplus.to/sgbeal "Freedom is sloppy. But since tyranny's the only guaranteed byproduct of those who insist on a perfect world, freedom will have to do." -- Bigby Wolf
[sqlite] Simple Math Question
On 22 Oct 2015, at 8:45pm, Rousselot, Richard A wrote: >(9.2+7.9+0+1.0+1.3+1.6), If you really are handling a lot of numbers with exactly one decimal place, the answer is to multiply them all by 10 are part of your input routine, and store them all as integers. On the other hand, you may have just come up with good examples to demonstrate your problem and this may not reflect your real data at all. Financial software frequently handles all currency amounts as pence or cents for the reasons you've just found out. Annoys the heck out of bankers until you have them work problems by hand and seen just how stupid computers really are. Simon.
[sqlite] Simple Math Question
I fully expected something like this from a fractional number like 1/3 but didn't realize that this would be the case if when the digits were explicitly stated and they summed to a whole number. I will use a Cast statement to resolve. FWIW, MySQL and Oracle both return all yes for that query. Thanks to everyone that replied. Richard -Original Message- From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Stephan Beal Sent: Thursday, October 22, 2015 2:57 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] Simple Math Question On Thu, Oct 22, 2015 at 9:45 PM, Rousselot, Richard A < 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 > "no" end FROM > sometable; > Try the same with integers as you'll likely see different results. Floating-point math is fraught with problems when it comes to expecting exact results at a specific precision. See: http://floating-point-gui.de/ the first example of which demonstrates the problem you are seeing. -- - stephan beal http://wanderinghorse.net/home/stephan/ http://gplus.to/sgbeal "Freedom is sloppy. But since tyranny's the only guaranteed byproduct of those who insist on a perfect world, freedom will have to do." -- Bigby Wolf ___ sqlite-users mailing list 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
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
> Financial software frequently handles all currency amounts as pence or > cents for the reasons you've just found out. Annoys the heck out of > bankers until you have them work problems by hand and seen just how stupid > computers really are. Usually as hundredths (1/100) of a cent -- that is, the amount multiplied by 1000, stored as an integer. "Bankers Rounding" is then done on any results. Usually Bankers Rounding is done as round-half-even (so 3.5 becomes 4 and 2.5 becomes 2). While they tend to be more accurate, stochastic rounding of halfs or alternating rounding are often not reproducible and therefore are not usually used where reproducibility is required.
[sqlite] Simple Math Question
Because there are differences. Double precision floating point is only accurate to 14 digits and there is a difference in the 15th decimal place. sqlite> SELECT ...> (9.2+7.9+0+4.0+2.6+1.3) - 25.0, ...> (9.2+7.8+0+3.0+1.3+1.7) - 23.0, ...> (9.2+7.9+0+1.0+1.3+1.6) - 21.0; 3.5527136788005e-15|0.0|3.5527136788005e-15 When comparing floating point numbers you should be doing something like this: sqlite> SELECT ...> (9.2+7.9+0+4.0+2.6+1.3), ...> case when abs((9.2+7.9+0+4.0+2.6+1.3) - 25.0) < 1e-14 then "yes" else "no" end, ...> (9.2+7.8+0+3.0+1.3+1.7), ...> case when abs((9.2+7.8+0+3.0+1.3+1.7) - 23.0) < 1e-14 then "yes" else "no" end, ...> (9.2+7.9+0+1.0+1.3+1.6), ...> case when abs((9.2+7.9+0+1.0+1.3+1.6) - 21.0) < 1e-14 then "yes" else "no" end; 25.0|yes|23.0|yes|21.0|yes The number "1e-14" is the "fuzz factor" used to determine the "closeness" of the numbers. See http://en.wikipedia.org/wiki/Floating_point for some information on floating point numbers used by computers. For comparing them see http://floating-point-gui.de/errors/comparison/ and in particular the paper referenced at the end of the page -- which links to a more correct article. > -Original Message- > From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users- > bounces at mailinglists.sqlite.org] On Behalf Of Rousselot, Richard A > Sent: Thursday, 22 October, 2015 13:45 > To: 'sqlite-users at mailinglists.sqlite.org' > Subject: [sqlite] Simple Math Question > > Doing the following math, why is it that the results are not all returning > "yes"? > > SELECT > (9.2+7.9+0+4.0+2.6+1.3), > case when (9.2+7.9+0+4.0+2.6+1.3)=25.0 then "yes" else > "no" end, > (9.2+7.8+0+3.0+1.3+1.7), > case when (9.2+7.8+0+3.0+1.3+1.7)=23.0 then "yes" else > "no" end, > (9.2+7.9+0+1.0+1.3+1.6), > case when (9.2+7.9+0+1.0+1.3+1.6)=21.0 then "yes" else > "no" end > FROM > sometable; > > Result > 25.0 no 23.0 yes 21.0 no > > I'm sure this has an obvious answer but it isn't jumping out at me. > > Richard > > This communication is the property of CenturyLink and may contain > confidential or privileged information. Unauthorized use of this > communication is strictly prohibited and may be unlawful. If you have > received this communication in error, please immediately notify the sender > by reply e-mail and destroy all copies of the communication and any > attachments. > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Simple Math Question
On 10/22/2015 4:25 PM, Rousselot, Richard A wrote: > I fully expected something like this from a fractional number like 1/3 but > didn't realize that this would be the case if when the digits were explicitly > stated and they summed to a whole number. I will use a Cast statement to > resolve. Just like 1/3 cannot be precisely represented as a finite decimal fraction, 1/10 aka 0.1 cannot be precisely represented as a finite binary fraction - which is what a floating point value really is. > FWIW, MySQL and Oracle both return all yes for that query. In MySQL, 9.2 is a literal of DECIMAL type, which is in fact represented as a finite decimal fraction. SQLite doesn't have such a type. You would likely observe similar results in MySQL if you write your constants like this: 9.2e0 (scientific notation makes them have FLOAT or DOUBLE type). See also: https://dev.mysql.com/doc/refman/5.0/en/problems-with-float.html -- Igor Tandetnik
[sqlite] Simple Math Question
On 10/22/2015 3:45 PM, Rousselot, Richard A wrote: > Doing the following math, why is it that the results are not all returning > "yes"? For the same reason that 0.33 + 0.33 + 0.33 != 1. 0.33 is only approximately equal to 1/3, and the error tends to accumulate. See also: https://docs.oracle.com/cd/E19957-01/806-3568/ncg_goldberg.html -- Igor Tandetnik
