Re: [sqlite] A possible double bug?
> On Oct 17, 2016, at 2:12 AM, Quan Yong Zhai wrote: > > Or after prepare “ INSERT INTO test VALUES(?) “ > Bind_text “62.027393” > Bind_double 62.027393 > > In all the four situation, the value insert into foo field is binary > identical, it’s a 8-bytes REAL value. I suspect the OP generated that number through computation, and it’s not exactly equal to a double parsed from the string “62.027393”. In other words, the OP ended up with a number n, such that string(n) = “62.027393” (using some particular format like “%.6f”) but n != parse(“62.027393”) The original code must be doing Bind_double(n); whereas in your code the parser had to read the string “62.027393” at parse time and generate a double from it, which is not equal to n. —Jens ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] A possible double bug?
Discussions on floating point aside, I'm likewise getting results that are equal when trying it. So I'm curious as to the original poster's SQLite version, platform, language they're coding in, etc. When you run "select foo, typeof(foo) from test;" are you getting two results of (62.027393, 'real') or is one getting a real type and one getting a text type? -Original Message- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Quan Yong Zhai Sent: Monday, October 17, 2016 5:13 AM To: Keith Medcalf; SQLite mailing list Subject: Re: [sqlite] A possible double bug? I can’t reproduce the problem, http://sqlite.org/datatype3.html#type_affinity “When text data is inserted into a NUMERIC column, the storage class of the text is converted to INTEGER or REAL (in order of preference) if such conversion is lossless and reversible” So after “create table test (foo REAL)”. Foo field have “REAL” type affinity. INSERT INTO test VALUES(62.027393); or INSERT INTO test VALUES(“62.027393”); Or after prepare “ INSERT INTO test VALUES(?) “ Bind_text “62.027393” Bind_double 62.027393 In all the four situation, the value insert into foo field is binary identical, it’s a 8-bytes REAL value. #include #include int main() { sqlite3* db; sqlite3_stmt* stmt; sqlite3_open("double.sqlite", &db); sqlite3_exec(db, "CREATE TABLE IF NOT EXISTS test(foo REAL);", 0, 0, 0); sqlite3_prepare(db, "INSERT INTO test VALUES(?)", -1, &stmt, 0); sqlite3_bind_text(stmt, 1, "62.027393", -1, SQLITE_STATIC); sqlite3_step(stmt); sqlite3_finalize(stmt); sqlite3_prepare(db, "INSERT INTO test VALUES(62.027393)", -1, &stmt, 0); sqlite3_step(stmt); sqlite3_finalize(stmt); sqlite3_close(db); } e:\Nana>sqlite3 double.sqlite sqlite> select typeof(foo) from test; real real sqlite> select * from test a cross join test b where a.foo=b.foo; 62.027393|62.027393 62.027393|62.027393 62.027393|62.027393 62.027393|62.027393 ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] A possible double bug?
On 10/17/2016 08:29 AM, Quan Yong Zhai wrote: "The database file format is cross-platform - you can freely copy a database between 32-bit and 64-bit systems or between big-endian and little-endian architectures. Quote: "In your machine, implementation, and SQLite installation the two value representations may be identical, while in others it may not be." Does it mean the promise about cross-platform is broken? A fair question, but no. Not as I see it, at least. The statement - which you called a promise - still holds. It will work by just copying the data between 32-bit and 64-bit or BE and LE architecture. The fact that two different implementations approximate a rational number differently does not violate that. See that fabs(a - b) > ε for some ε relatively close to zero and even if the value of fabs(a - b) may differ on these machines, the number is still a correct approximation for the rational in question. For instance, if it became a large negative number instead, it would be a portability problem, but this is a mere characteristic of a how floating point arithmetic works. This is different from integer arithmetic, which is exact and should behave the same way on all systems, according to that statement. -- Bernardo Sulzbach http://www.mafagafogigante.org/ mafagafogiga...@mafagafogigante.org ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] A possible double bug?
"The database file format is cross-platform - you can freely copy a database between 32-bit and 64-bit systems or between big-endian and little-endian architectures. Quote: "In your machine, implementation, and SQLite installation the two value representations may be identical, while in others it may not be." Does it mean the promise about cross-platform is broken? 发件人: Bernardo Sulzbach<mailto:mafagafogiga...@gmail.com> 发送时间: 2016/10/17 17:19 收件人: sqlite-users@mailinglists.sqlite.org<mailto:sqlite-users@mailinglists.sqlite.org> 主题: Re: [sqlite] A possible double bug? On 10/17/2016 07:12 AM, Quan Yong Zhai wrote: > I can’t reproduce the problem, As it has already been pointed out, this is normal for floating point arithmetic. In your machine, implementation, and SQLite installation the two value representations may be identical, while in others it may not be. fabs(a - b) is the simplest way to have an idea of how close two values are, and dividing by the magnitude of one of them (after checking that it is not zero, etc.) afterwards is also a good idea in some cases. -- Bernardo Sulzbach http://www.mafagafogigante.org/ mafagafogiga...@mafagafogigante.org ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] A possible double bug?
On 10/17/2016 07:12 AM, Quan Yong Zhai wrote: I can’t reproduce the problem, As it has already been pointed out, this is normal for floating point arithmetic. In your machine, implementation, and SQLite installation the two value representations may be identical, while in others it may not be. fabs(a - b) is the simplest way to have an idea of how close two values are, and dividing by the magnitude of one of them (after checking that it is not zero, etc.) afterwards is also a good idea in some cases. -- Bernardo Sulzbach http://www.mafagafogigante.org/ mafagafogiga...@mafagafogigante.org ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] A possible double bug?
I can’t reproduce the problem, http://sqlite.org/datatype3.html#type_affinity “When text data is inserted into a NUMERIC column, the storage class of the text is converted to INTEGER or REAL (in order of preference) if such conversion is lossless and reversible” So after “create table test (foo REAL)”. Foo field have “REAL” type affinity. INSERT INTO test VALUES(62.027393); or INSERT INTO test VALUES(“62.027393”); Or after prepare “ INSERT INTO test VALUES(?) “ Bind_text “62.027393” Bind_double 62.027393 In all the four situation, the value insert into foo field is binary identical, it’s a 8-bytes REAL value. #include #include int main() { sqlite3* db; sqlite3_stmt* stmt; sqlite3_open("double.sqlite", &db); sqlite3_exec(db, "CREATE TABLE IF NOT EXISTS test(foo REAL);", 0, 0, 0); sqlite3_prepare(db, "INSERT INTO test VALUES(?)", -1, &stmt, 0); sqlite3_bind_text(stmt, 1, "62.027393", -1, SQLITE_STATIC); sqlite3_step(stmt); sqlite3_finalize(stmt); sqlite3_prepare(db, "INSERT INTO test VALUES(62.027393)", -1, &stmt, 0); sqlite3_step(stmt); sqlite3_finalize(stmt); sqlite3_close(db); } e:\Nana>sqlite3 double.sqlite sqlite> select typeof(foo) from test; real real sqlite> select * from test a cross join test b where a.foo=b.foo; 62.027393|62.027393 62.027393|62.027393 62.027393|62.027393 62.027393|62.027393 ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] A possible double bug?
I’d say the mistake here is converting a double to a string before inserting it into the database. Any time you convert between floating point and decimal (or vice versa) you can lose accuracy, and are not guaranteed round-trip fidelity. (0.1, 0.01, 0.001, etc. do not have finite-length exact representations in binary, just like 1/7 doesn’t in decimal. So most non-integers that look reasonable in decimal are in fact subject to round-off errors in binary floating point.) As I said earlier today about strings: don’t hardcode data values into SQL statements. Use bindings instead. —Jens ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] A possible double bug?
On Sunday, 16 October, 2016 12:03, Victor Evertsson wrote: > I was wondering about the different behavior of inserting a Double as a > String vs as a value with a prepare statement in C. > Consider an example when the value: 62.027393 is inserted as a String and > as value with a prepared statement, for instance: The value 62.027393 cannot be represented exactly in IEEE 754 double precision floating point. The bounding values are 62.027392645 and 62.027393355. The 1 ULP (epsilon) value is 7.105427357601e-15. > "CREATE TABLE test (foo REAL)" > "INSERT INTO test (foo) VALUES (?)" > "INSERT INTO test (foo) VALUES (62.027393)" > "SELECT * FROM test" > If the content of the table test, is printed, then the output of the > values is equal i.e. 62.027393. However, if the stored value is compared > with for instance a cross join: > select * from test as a cross join test as b where a.foo = b.foo; > Then two rows are returned which indicates that the values are not equal > (four rows should be returned if they are equal). They are equal for all intents and purposes. Your comparison is simply too exacting, requiring the approximations to be "equal", whereas both values are valid approximations of 62.027393. > If the value 62.0273934 is inserted as value with the prepare > statement instead of 62.027393 in the example, then the insert as String > and the insert as prepare statement is equal. > The double seems to be changed from 62.027393 to 62.0273934 when > inserted as a String. This happens with some other values too (but not > all). > The values should be equal and i wonder if this is a bug or intendent > behavior? http://floating-point-gui.de/errors/comparison/ https://en.wikipedia.org/wiki/IEEE_floating_point It is neither a bug nor intended behaviour. It is simply how binary floating point works. When you compare floating point numbers, you need to compute the distance between them in epsilon units of the comparand. If they are within a reasonable "distance" of each other, then they are equal. For example, if abs((x-y)/epsilon(x)) < T then then the numbers are equal. For non-pathological computations, a value of 5 for T is more than adequate. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users