On Mon, Jun 6, 2011 at 2:55 PM, Jean-Christophe Deschamps <j...@antichoc.net> wrote: > Allow me to add a humble bit to what Jay just posted. > > SQLite, as well as most other RDBMS around, allow you to perform FP > calculations in SQL statements. I assume no-one imagines an extended > FP fine-grain support of hundreds of computation options and status > reporting be part of SQL or SQLite. It's just not the purpose of SQL > and DBMSs in the first place.
I disagree emphatically. There is one thing that makes numerical support in RDBMSes important: aggregate functions. Aggregate functions are critical because they allow one to do much analysis at the data source, instead of having to transport it elsewhere for analysis. Allow me to illustrate via DTrace. DTrace is a tracing system for user-land processes and kernel code as well. It is extremely powerful, but it can quite easily be used to generate more output than you might know what to do with, which leads me to the most important feature of DTrace: aggregation functions. Just as in SQL, aggregation functions, and perhaps even more necessary in DTrace than in SQL, aggregation functions allow one to statistically analyze data at the source, thus avoiding any need to record or transport DTrace probe data. Statistical analysis at the source is a critical performance feature. It might seem _less_ critical in SQLite3 applications than in networked RDBMS applications because the "transport" is only within a single system image, but DTrace proves otherwise. And even without the DTrace example, aggregation at the source is still important because there are complex SQL queries that would require much programming effort to replicate via simpler queries with aggregation in the application. It seems like a shame to say that important features of this RDBMS are not to be used... That said, most users of SQLite3 will likely do just fine with IEEE754 doubles. And those who can't can always build user-defined functions for doing arithmetic with user-defined REAL types represented as TEXT or BLOBs. So I'm not saying that SQLite3 needs to change. I'm saying that it's deleterious to say that SQLite3 is not intended to do math. Because even if its authors think so, the fact is that statistical analysis at the source is one of SQL's most powerful features, and SQLite3 _does_ support it, and it'd be quite odd to hear that "well, not really". > Now if you take scientific computations (there I mean with all > fine-grain detail support) out of the SQL statements and just look at > the engine as a mean to store, organize, query data (scientific or > not), SQLite still offers much flexibility to you. > > You have a DOUBLE column where you need to store NaN? Go ahead and > store 'NaN' in offending rows. You need to store +Inf, -Inf, flag > denormals, a.s.o, just do it by storing such values or flags as BLOBs, > TEXT or whatever type you find most appropriate. Storing them is not the issue. Getting them as results of arithmetic operations in SQL statements is the issue. One could use CASE to decide when one would be getting infinities or NaNs, and then return text or blob representations of those, but means making statements quite complex just to work around a missing feature. OTOH, I agree with D.R. Hipp, that more than theoretical justification is highly desirable here, and backwards-compatibility is highly desirable too. I can see two ways to add proper IEEE754 support in a backwards-compatible way: a) via a pragma to enable raising exceptions or returning +-inf/NaNs, b) by adding a callback via which to report such exceptions, with exceptions raised only if the callback is set. I would prefer (a), though it'd mean re-compiling statements when the pragma is toggled. (A way to plug-in a full, transparent, extended-precision numeric library would also be nice. This seems feasible via calling out to the plugin from the relevant OPs in the VM. A header would be needed by which to distinguish IEEE754 values from extended precision value encodings.) Nico -- _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
