Last night I upgraded from SQLite 3.3.6 to SQLite 3.4.0. This morning a
transaction in my application failed because SQLite detected that the
application was trying to insert a row with a NULL in a REAL NOT NULL
column. Actually, it turns out that it was a NaN, which SQLite 3.4.0
apparently converts to NULL.
I don't necessarily mind this change, although I would consider NaN and
NULL to be different (although they have similar semantics). In fact,
I'm rather glad that SQLite failed in this case, otherwise it surely
would have been much longer until I noticed a problem in my application.
However, I have existing databases that have NaN values in some
fields. I noticed that when I dump tables containing NaN using the
SQLite 3.4.0 shell, the value is now represented as (NaN) rather than
the previous (0.0) which was clearly incorrect. However, the problem
now is that a table can't be dumped and restored using the SQLite shell
because (NaN) is neither a column nor a special value (such as NULL).
For example:
sqlite> .dump foo
BEGIN TRANSACTION;
CREATE TABLE foo (bar REAL);
INSERT INTO "foo" VALUES(NaN);
COMMIT;
sqlite> INSERT INTO "foo" VALUES(NaN);
SQL error: no such column: NaN
This is not necessarily a problem for me, but it's something I noticed
through some experimentation that may have been an unexpected
consequence of the NaN fix. I've also noticed that I can perform an
INSERT-SELECT statement using rows that contain NaN, and the NOT NULL
constraint is not enforced then, although it is with UPDATE operations
when the result would be NaN.
cheers,
Arun
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
