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 game in town.

> 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

Reply via email to