On Sat, Mar 24, 2012 at 07:32:32AM -0400, Richard Hipp scratched on the wall:
> SQLite converts NaN inputs into NULL. I think this is the right choice. It is what I would expect. Well, no... what I would actually expect is that sqlite3_bind_double() should return an error, since it was provided with an invalid value. But if it isn't going to do that, conversion to NULL seems most correct. NaNs are, by definition, not actual numbers, and represent no meaningful numeric value or concept (such as infinity). What they represent is outside the domain of real numbers that float point values attempt to represent. They are a side-effect of the IEEE 754 standard, and are used to represent invalid or inconsistent results from floating point operations. They exist to support the operators defined in the standard, and are not inherent to the binary representation of floating point numbers. NaNs and NULLs, as a concept, share a lot. Both are value-less, in the sense that A != A for both NULLs and NaNs. Both are also unordered, meaning they cannot be sorted. In this case, the NaN is being used as a "flag value" to indicate something outside the normal domain of numbers. In the database world, this is exactly how NULLs are used. In the context of an application interfacing with a database, using NULLs for missing values is the appropriate thing to do. I'd go so far to say that if your application uses NaNs as flag values, the application itself should be doing the NULL conversion, and not depending on the database to do a more subtle conversion. Databases understand NULLs. Everything in a database environment already understands three-value-logic, and how to deal with it. Aggregate functions understand NULLs, and do the right thing. NULLs are part of SQL and understood by any SQL relational database. IEEE 754, on the other hand, and the NaNs that go with it, is not a database standard. Many databases don't even use IEEE 754 to represent numeric values, making it impossible to insert a NaN in any form. I also think allowing NaN values into an SQLite database would be dangerous. Never forget that NaNs are used to represent error conditions. Inserting a signaling-NaN is going to cause floating point exceptions with almost any operation. Or are we limiting NaNs to quiet-NaNs? Or converting signaling-NaNs to quite-NaNs? Should SQLite really have to know that much about IEEE 754? Proper support would require SQLite being very aware of the values it is dealing with. Just like NULLs, NaNs tend to propagate and take over any calculation they enter. They also risk throwing exceptions. Unless every expression, function, and aggregate is made aware of NaNs, and how to properly special-case them (test that!), I suspect a database would largely become unusable, except as a simple data store. You would also need to do things like define a sort order. Is a NaN less than -INF? Greater than +INF? Does that question even make sense? To me, allowing NaNs to enter the database makes about as much sense as allowing strings with negative length values. Just because I can come up with the bits to represent it doesn't make it real or right. Database systems, including SQLite, already have a well defined, well understood, and well documented system for representing and dealing with special-case flag values, especially when it comes to "missing", "undefined", or "unknown" values. It is best to go with the existing system which already covers all these cases. -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Intelligence is like underwear: it is important that you have it, but showing it to the wrong people has the tendency to make them feel uncomfortable." -- Angela Johnson _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
