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

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

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.  

sqlite-users mailing list

Reply via email to