SQLite returns NULL when subtracting

[Allan Vidal]

Binary package hint: sqlite3

When I execute the following statements in SQLite 3.4.2 on Ubuntu 8.04...

CREATE TABLE Test (rowID INTEGER PRIMARY KEY AUTOINCREMENT, value REAL);
INSERT INTO Test VALUES (NULL, 200);
UPDATE Test SET value = (value - 10.205) WHERE rowID = 1;
SELECT * FROM Test;

...the result is 189.795, as expected.

However, when executing the same statements in SQLite 3.5.9 on Ubuntu 8.10 Beta, the result is NULL.

[Allan Vidal (alnvdl)]

I also reported this bug directly to the SQLite team:
http://www.sqlite.org/cvstrac/tktview?tn=3414,35

The bug seems to be caused by compiling with the --fast-math option, though I did not try this solution myself.

[miraks (s-mankowski)]

I also do it:
http://www.sqlite.org/cvstrac/tktview?tn=3465,35

Do you know a work around because it's a blocking point for me ?

[miraks (s-mankowski)]

It's a major issue because of this also happens when selecting direct values:

  sqlite> select 6.6989+22.912345;

  sqlite> select 6.5989+22.912345;
  29.511245

Crazy ! no ?

WARNING: I think that libsql3-0 is impacted too.

[Allan Vidal (alnvdl)]

miraks, I think the only workaround is doing the math in the application code, and not in SQLite. That is, if you are using SQLite bindings for a programming language.
However, this is not a good solution...

[Rolf Leggewie (r0lf)]

Can those affected by this bug please try installing sqlite3 and libsqlite3 from my PPA (https://launchpad.net/~r0lf/+archive) and report if that fixes the problem? In that case, we'd see a duplicate of bug 254228 where you can read more about the background.

[Allan Vidal (alnvdl)]

Well, I tested the packages you suggested on Intrepid, and it worked fine. So, I'm marking this bug as a duplicate of bug 254228.

[Rolf Leggewie (r0lf)]

Thanks, Allan, for giving it a shot and reporting back.