On Tue Feb 03 2015 at 12:23:29 PM James K. Lowden <jklow...@schemamania.org>

> On Sun, 1 Feb 2015 02:13:15 +0100
> Stephan Beal <sgb...@googlemail.com> wrote:
> > On Sun, Feb 1, 2015 at 2:07 AM, Simon Slavin <slav...@bigfraud.org>
> > 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?
> 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){
  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

Reply via email to