Re: [sqlite] A possible double bug?

[Jens Alfke]

> 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?

[David Raymond]

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", );
  sqlite3_exec(db, "CREATE TABLE IF NOT EXISTS test(foo REAL);", 0, 0, 0);
  sqlite3_prepare(db, "INSERT INTO test VALUES(?)", -1, , 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, , 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?

[Bernardo Sulzbach]

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?

[Quan Yong Zhai]

"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?

[Bernardo Sulzbach]

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?

[Quan Yong Zhai]

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", );
  sqlite3_exec(db, "CREATE TABLE IF NOT EXISTS test(foo REAL);", 0, 0, 0);
  sqlite3_prepare(db, "INSERT INTO test VALUES(?)", -1, , 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, , 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?

[Jens Alfke]

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?

[Keith Medcalf]

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

[sqlite] A possible double bug?

[Victor Evertsson]

Hi,

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:

"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).


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?


Best regards

Victor
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users