Hi Jay, > One should never assume a database uses IEEE 754, so one should never > assume it uses similar semantics.
One should not assume it unless it is documented, of course. Postgres, for example, half-heartedly embraces IEEE-754 'on platforms that use it' (see section 8.1.3 of its manual). It documents the fact that +/- infinity and NaN are useable on such systems. > Even those databases that do use > IEEE 754 for a select few of their types have other considerations. > In the bigger picture, IEEE 754 makes up, at most, a small part of > the SQL numeric environment. For SQL: yes. For SQLite though, it is the only option. > Using 754 as a reference for the rest of the environment strikes me as > poorly thought out and putting the tail before the dog. In terms of generic SQL you may be right (although I'd be willing to argue for it). However, I think that for a specific DB product, it is a good thing to document without ambiguity what the properties and guarantees of the numeric types and operations are; and IEEE-754 is the only game in town when it comes to properly specified floating point numbers. I feel this is especially true for the light-weight database system that is SQLite. I get the impression that you are advocating to keep floating point operations deliberately vague and underspecified (please correct me if I am wrong). To me as a developer that is useless; I will never be able to reason about the correctness of anything, and I am effectively dependent on the (undocumented) effort that the makers of the FP implementation did. Effectively, that would be a return to the pre-IEEE 754 wild west of floating point calculations. > [...] Its express purpose was to allow non-technical people to write > queries and build business applications. That may have been the optimistic idea 40 years ago, but I think it is time to admit that this was completely misguided. If 40 years of relational database experience has taught us anything, it is that doing proper SQL (anything beyond a basic SELECT) is an actual skill that requires technical prowess. > [...] This is what most high-level scripting languages like Perl and Python > do. Perl and Python support NaNs and infinities just fine. > If you want bare metal IEEE 754 for your scientific computing > application, then you might want to rethink doing your math operations > in a data storage system. You are making it sound as if proper support for IEEE-754 types would open up some can of worms for regular users, but I really don't see why you think that is the case. They would see an occasional "NaN" instead of NULL if they did something naughty; I personally think that is a lot more informative. Compare currently: sqlite> SELECT 0.0/0.0, 1.0/0.0; | sqlite> ... versus what I would like to see: sqlite> SELECT 0.0/0.0, 1.0/0.0; NaN|Inf sqlite> > As you've pointed out, SQLite is more than capable of storing and retrieving > non-numeric IEEE 754 values No, it doesn't support storing and retrieving NaNs. That is an arbitrary limit that bites those of us who actually know what they are doing. Sidney _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
