On Sunday, 16 October, 2016 12:03, Victor Evertsson <victor.everts...@gmail.com> 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.02739299999999645 and 62.02739300000000355. 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.027393000000004 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.027393000000004 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
