On Sun, Jun 05, 2011 at 04:35:25PM +0200, Sidney Cadot scratched on the wall:
> Is the way in which SQLite handlesNaN and Infinity values as defined > by IEEE-754 documented somewhere? I would also be interested to find a > discussion of the rationale behind the design decisions. Doubt it. I'm fairly sure the SQL standards do not address IEEE 754 in specific. Most databases use a more generic NUMERIC type that doesn't have a fixed representation or domain (such as IEEE 754). In general, SQLite is fairly hands-off and leaves floating point math to the underlying system architecture. There is an assumption that it is IEEE 754, and I believe this is one area where the files will not cross platforms. However, it is worth remembering that IEEE 754 is really about building processors, not about end-user interaction. While it is a rigid, formal specification of a numeric environment, at its heart it is about mechanics, not about consistent mathematical systems built on theorems and proofs. SQL is more interested in building a numeric environment that follows the theory of "math" as a high-school school student might understand that term. Like many math-heavy applications, SQLite (and parts of the SQL standard) act as a logical layer that sits between the mechanics of IEEE 754 and the real world expectations of non-technical users. SQL also has some additional environmental considerations, such as the NULL type. As others have pointed out, one of the meanings of NULL is essentially "unknown." This is followed very consistently in the logic operations, and it isn't a surprise this is extended into the numeric operations as well. > After some experimenting, it appears that ... > > * SELECT 1.0 / 0.0 yields NULL (where I would expect to see Inf) NULL is returned any time 0 or 0.0 is seen on the right side of a divide expression. For example, "2.0/0.0" => NULL. For that matter, "'abc'/0.0" => NULL. Given NULL to mean "unknown", this makes a lot more sense. This is a prime example of the difference between IEEE 754 and a "real world" numeric environment. Anyone that knows a bit of math isn't going to expect 2.0/0.0 and 1.0/0.0 to yield different answers. In fact, that would be rather dangerous in a database that might be computing monetary values. But NULL makes a lot of sense in the proscribed environment, and is much more consistent with the rest of SQL's operators. > * SELECT 1e10000 yields an actual IEEE-754 infinity, and it can be > stored in a table I suspect this basically a parsing and overflow issue. It might make more sense for it to be NULL, but I've noticed SQLite tends to use +/-Inf as an overflow flag of sorts. That is the best floating point representation of that value that SQLite has, after all. > * SELECT 1e10000 + 1e10000 yields Infinity, as expected according to IEEE-754. Again, that's just logical. +Inf + +Inf is going to be +Inf, especially if you consider Inf to be an overflow flag, rather than a true "Infinite" in the mathematical sense. IEEE 754 tends to use Inf more as an overflow flag as well. > * SELECT 1e10000 - 1e10000 yields NULL, where I would have expected to > see NaN in accordance with IEEE-754. Again, if you consider Inf to be an overflow of sorts-- a valid number that is just too large to represent-- then this makes sense. A "very large but not exactly known" number, minus one of the same, might result in anything from +Inf to -Inf. Hence, the "unknown" NULL. I'd guess most IEEE 754 results of NaN will produce a NULL in SQL, since both can mean "unknown" or "missing/illogical value." -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
