On Tue Feb 03 2015 at 12:23:29 PM James K. Lowden <[email protected]> wrote:
> On Sun, 1 Feb 2015 02:13:15 +0100 > Stephan Beal <[email protected]> wrote: > > > On Sun, Feb 1, 2015 at 2:07 AM, Simon Slavin <[email protected]> > > wrote: > > > > > So, having established that NaN and -0 do not make the round trip > > > from a C variable through a database and back into a C variable ... > > > at least I think we have ... > > > > If you're assuming C89 (which sqlite3 is, by and large), it's a > > technical fact that there is no standard representation of either > > negative zero, NaN, or Infinity. Any such support would be > > non-C-standard. > > As you know, C quite intentionally does not define bit-patterns for any > numeric type. It doesn't specify endianism, twos-complement negatives, > or IEEE floating point format. It doesn't even specify the number of > bits used. That is part of what makes C code portable. > > IEEE floating point is implemented in hardware. The format has been > universally adopted for floating-point units for 25 years or so. There > are processors that lack floating point support, but I've never heard > of one that provides floating point in some other format. > > The compiler is free to decide what the bit pattern for > > double x = 1.2; > > would be. As a practical matter, normally the obvious choice would be > to implement IEEE format and rely the processor's floating point > support. But that's not an obligation; it's implementation-defined. > > SQLite's documentation states that REAL is "stored as an 8-byte IEEE > floating point number". I suspect that's actually an incidental > by-product of being compiled exclusively on modern machines, all of > which use IEEE floating point (if any). I suspect a more accurate (but > obscure) description would be "stored in the format of a C double as > implemented by the compiler that compiled SQLite (normally IEEE 754)". > > If the following are not true, they should be, and we should understand > why not: > > 1. For storage and retrieval as REAL with binary bindings, SQLite > simply copies the bits to and from the database. There's no reason the > 64 bits presented to the database can't be kept and returned on > demand. > > 2. For interpretation -- sorting, SQL computation, user-defined > functions -- SQLite again relies on the compiler and perhaps math > routines in the standard library. Want to know how rounding works, or > how your single-precision variable is widened to double-precision? > RTFM! > > I mentioned collation before, but I don't think SQLite need have any > rule about e.g. "how to sort NaN". The processor surely has a rule for > comparing NaNs. The compiler will cause the processor to make the > comparison and report the result, which SQLite can use without further > assessment. > > It was alleged earlier that denormal numbers, -0, and NaN cannot be > bound and stored to a column with REAL affinity. If that's true, > SQLite is interpreting the values or applying rules that afaik aren't > explicitly stated and aren't strictly necessary. > sqlite3_bind_double calls sqlite3VdbeMemSetDouble which has a specific check against NaN. My assumption is that this is what results in NaNs not round tripping and instead coming back out as SQLITE_NULL: SQLITE_PRIVATE void sqlite3VdbeMemSetDouble(Mem *pMem, double val){ sqlite3VdbeMemSetNull(pMem); if( !sqlite3IsNaN(val) ){ pMem->u.r = val; pMem->flags = MEM_Real; } } Richard answered on -0.0, i.e. it's a side effect of an optimization: > SQLite converts integer floating point values to actual > integers for storage (because that takes up less space on disk) and > then converts back to double upon retrieval. That round-trip would > change -0.0 into +0.0. _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
