On Sat, Mar 24, 2012 at 3:18 AM, Stephan Buchert <stephanb...@gmail.com>wrote:
> Here the simplified scenario: > > We have measurements from a sensor array. The data are checked and plotted. > Then they are inserted into an SQLITE database. We find out that after > retrieving (selecting) the data back from the database, the plots look > different, the data have been changed! First we suspect a problem with our > software, but it seems that SQLITE is the culprit! > > Occasionally the sensor has a fault or the values are invalid (like > negative temperatures). To mark them once and for all as invalid, the > faulty values are replaced (in a C environment) by NaNs. Then the data are > inserted into the database with a C porgram, in a loop with the > sqlite3_bind_double function. The date are retrieved in a loop with the > sqlite3_column_double function. It turns out that SQLITE has quietly turned > our invalid data (NaNs) into valid 0.0 values! Is this a bug? > SQLite converts NaN inputs into NULL. And sqlite3_column_double() is defined to return 0.0 for any non-numeric value, including NULL. I recommend that you first check the datatype coming back using sqlite3_column_type() first, and only use sqlite3_column_double() if the type is SQLITE_INTEGER or SQLITE_REAL and return a NaN if sqlite3_column_type() is anything else. In hindsight, I suppose I should have designed sqlite3_column_double() to return NaN for any non-numeric value. But that cannot be "fixed" now without a backwards-compatibility break, which is something we are unwilling to do. But I'll keep the issue in mind, and perhaps provide a new sqlite3_column_double_v2() API that works more sensibly. > > /Stephan > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- D. Richard Hipp d...@sqlite.org _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
