On Fri, 30 Jan 2015 10:39:31 +0200
RSmith <rsm...@rsweb.co.za> wrote:
> At first I thought SQLite (or any RDBMS) should really strore
> whatever you give and return it untouched, but that cannot be true
> for an RDBMS because it has to interpret the data, it isn't just a
> binary store. It has to answer questions like SELECT ("colA"+3),
> (7/"colB"); or sort by colA or use a collation on strings etc. etc.
> - all of which means it must care about what the value relates to and
> cannot simply ignore it unless stored as an ignorant type (i.e Blob).
Yes, that's true. Whenever a column value is interpreted by SQLite,
that interpretation might differ from the one used by the application
that inserted it.
For example, I might have an 8-bit unsigned integer stored in column A
with the value 0xFF (all bits on). The clause "WHERE A > 128" would
not return that row because SQLite interprets the column as signed and
the value as -1. Similarly ORDER BY would not work in accordance with
the application's interpretation.
That said, https://www.sqlite.org/datatype3.html says REAL is "stored
as an 8-byte IEEE floating point number". I can't see why a
non-signalling NaN couldn't be stored and interpreted. You'd need some
convention for collation, and a function like is_nan() would be helpful
for WHERE clauses.
It could be argued that SQLite should *not* do that, in the interest of
simplicity. NaN represents an invalid output, say sqrt(-2). For any
function F, NaN = F (NaN), and NaN <> NaN. Because SQL NULL works
similarly, ISTM a defensible alternative would be to say all NaNs
become NULL in the database, or raise an error. That loses the
distinction between "missing" and "not computable", but that probably
doesn't matter in most practical senses.
On the third hand, many numerical and stats packages, e.g. R, use NaN
(mistakenly, in my view) to represent missing data. Integration with
such packages might be facilitated by "NaN fidelity" if we can call it
that, so that they can retrieve what they saved.
> > Trying to retrieve a stored qNaN or sNaN returns a column type of
> > NULL and a value of 0.
That's consistent with division-by-zero yielding zero, and is an
impediment to using SQLite for scientific work.
--jkl
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
