On Mon, Jun 06, 2011 at 05:36:03PM +0100, Simon Slavin scratched on the wall:
> It's just a shame that the SQL and IEEE standards are mutually incompatible. Yes and no. First off, I would point out that the SQL standards are *significantly* older than the IEEE 754 standard. Languages that we would recognize as early SQL were around 15 years before the first IEEE 754 standard was ratified-- and even longer until 754 hardware was available and common. Secondly, SQL is not really designed around floating point numbers. Most databases have a generic "NUMERIC" type that allows arbitrary size and precision, and are not tied to any specific floating point model. The fact that SQLite *only* offers the IEEE 754 numbers (beyond integers) actually makes it a bit of an oddball. Traditionally, databases use BCD or some similar "bignum" representation of numbers, specially to avoid all the representation issues IEEE 754 tries to standardize (notice I didn't say "solve"). While most database products have moved in the direction of offering more specific types that tie directly to common representations, (such as fixed size integers and IEEE 754 floating point values), in the 40 year history of relational database products, this is a somewhat new thing. As such, it makes little sense to define the environment's numeric model around a representation that isn't even in the standard. One should never assume a database uses IEEE 754, so one should never assume it uses similar semantics. 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. Using 754 as a reference for the rest of the environment strikes me as poorly thought out and putting the tail before the dog. And lastly, even if the IEEE 754 was as standardized back then as it is now, and even if SQL did everything in IEEE 754 (as SQLite mostly does), I still stand by the idea that it isn't something you want to expose to the user in its raw form. SQL is a user language, not a systems programming language. Its express purpose was to allow non-technical people to write queries and build business applications. The vast majority of people working in that environment don't expect to deal with specific rounding modes or processor status bits. They just want to do math like their grade school teacher taught them to do it. Adding layers between the computational numeric model and the presented numeric model makes sense. This is what every calculator does, be it pocket or desktop. This is what Excel does. This is what most high-level scripting languages like Perl and Python do. 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. Write a C or assembly program and have all the close, fine-grain detail you want. As you've pointed out, SQLite is more than capable of storing and retrieving non-numeric IEEE 754 values, so it is doing its core job just fine. -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 [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
