Hi Jay, > 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.
While it is a bit off-topic, I disagree with this assessment. The 754 standard has been carefully crafted to allow rigorous statements about the stability of numerical algorithms. It chooses deliberately to sacrifice easy hardware implementation in favor of well-defined semantics. In fact, it took quite some time before compliant hardware implementations were available after its inception. > As others have pointed out, one of the meanings of NULL is essentially > "unknown." Yes, but in terms of IEEE-754, there exist no "unknown" results. Any of the elementary operations (+ - * /) has a fully bitwise predictable result (subject to the rounding mode). >> * 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. Is that behavior mandated behavior by any of the SQL standards, or is that an implementation choice by SQLite? > 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 IEEE-754, they don't. Both return +infinity. You may be thinking of 0.0 / 0.0, which does return NaN in IEEE-754. For what it is worth: I know a bit of math, and I actually expect floating point operations to follow the IEEE-754 mandated behavior nowadays. Unless the SQL standard mandates specific behavior, of course; in the context of SQLite, that would clearly take precedence over IEEE-754. Unfortunately I do not know any of the SQL standards nearly as well as IEEE-754. As a matter of principle, I think it is not good practice to give "this is what people expect in the real world" precedence over a well-defined, rigorous standard. For one thing, it is quite debatable what people expect in the real world. For another thing, rather smart people have thought real hard to make IEEE-754 semantically consistent; overriding such deliberations with gut feelings about how things should behave doesn't sound like a good idea to me. > But NULL makes a lot of sense in the proscribed environment, and is much more >consistent with the rest of SQL's operators. That is a respectable position to take on the issue. However, it limits the usefulness of SQLite for storing scientific data (which is what I am trying to use it for, currently). Here, there is a clear and useful distinction between a "missing value" and a NaN -- the latter meaning "a calculation was done but it failed in any of the standard-prescribed ways". The difference is subtle but real. Many, probably most, languages used for scientific computation distinguish between those two concepts. Lastly: if SQLite chooses to unify the floating-point concept of NaN and the SQL concept of NULL, I feel it should do the same for +/- infinity. As it stands, it seems to incorporate one concept of IEEE-754 (+/- infinity) while omitting the NaN. My personal feeling is that a clear choice should be made. But more importantly: the behavior needs to be documented. The SQLite docs are silent on the issue of floating point behavior, as far as I can tell. Sidney _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
