On 26 Mar 2012, at 3:37pm, "Francis J. Monari, Esquire" <monarifj....@juno.com> wrote:
> Storing +infinity, for example. It seems that in IEEE terms +infinity is > different from NaN, but SQLite return NULL in both instances. Oh dear. Where is the conversion happening ? Is the value stored in the SQLite database correct for '-infinity' or has it already been converted to NULL before it is stored ? > Is there a standard "SQLite" style of handling this situation? There are three special values for IEEE reals: NaN, +infinity, and -infinity. To be compliant with IEEE you have to not only consider each of these values separately (don't confuse one with another) but you also have to deal correctly with each operation you perform on them. IEEE provides a table, but I'll give just two examples as illustration: (-infinity) + (-infinity) = (-infinity) (-infinity) + (+infinity) = (NaN) Since SQLite does mathematical operations inside itself (e.g. SELECT a + b FROM myTable ) it would have to do these correctly too. I can think of two SQLite3-style ways to handle this: A) Convert all three special values to NULL at the _bind() or _step() stage, and say so in the same parts of the documentation that says REALs are stored as IEEE values. This favours SQL over IEEE. A bit. B) Don't accept any of those three values: reject them at the _bind() or _step() stage, producing some SQLITE_ result code which explains the failure. This favours IEEE over SQL. A bit. And then there's a change which would be upsetting for the existing userbase, but could be done for SQLite4 or a SQLite3_v2() routine: C) handle them all correctly. This shouldn't be too hard because SQLite already relies on your operating system's IEEE library for other operations. This would require the SQLite team to come up with canonical text representations for all three values, because _exec() would require them. It's these picky things that make implementing SQL not just a trivial matter of making sure you do everything that's documented. These are real problems that would be easy to handle correctly if you're designing an engine from scratch, but are hard to force into an existing engine when you're adding features. Simon. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
