On Sun, Mar 25, 2012 at 05:48:01AM +0100, Simon Slavin scratched on the wall:
> On 25 Mar 2012, at 3:48am, "Jay A. Kreibich" <j...@kreibi.ch> wrote:
> > On Sat, Mar 24, 2012 at 07:32:32AM -0400, Richard Hipp scratched on the
> > wall:
> >
> >> SQLite converts NaN inputs into NULL.
> >
> > I think this is the right choice. It is what I would expect.
>
> But it's not what the docs say:
>
> <http://sqlite.org/datatype3.html>
>
> "REAL. The value is a floating point value, stored as an 8-byte
> IEEE floating point number."
>
> That includes +infinity, -infinity, and NaN.
Again, I disagree. At least about the NaNs.
That line of the documentation happens to mention that, internally,
SQLite happens to use the 64-bit IEEE 754 *format* for storing data.
This allows the developer to understand quite a bit about the range
and precision of the REAL numbers in SQLite. It is good information
to have.
Storing data is a lot different than providing a full IEEE 754
numeric environment, however. The full environment includes very
specific rules about how operations are performed, and how error
conditions are handled and reported. And it is a hardware standard.
You can't really say IEEE 754 applies in full force unless you're
designing silicon or writing assembly instructions.
SQLite is dependent on the platform's hardware for all its floating
point math. SQLite does *not* provide a IEEE 754 numeric environment--
in fact, if SQLite is compiled and run on a non-IEEE 754 system, I
doubt you'll even be able to read or access values written into the
database by an IEEE 754 compliant system. SQLite doesn't "understand"
the IEEE 754 format, so much as it runs under the assumption that the
host platform uses IEEE 754 hardware, and is happy to shove values in
and out of the database. There is a big different between providing
a full numeric environment and just using an well known format for
storing numbers. SQLite uses the IEEE 754 *format*. It does not
provide a numeric environment.
And again, I come back to the fact that, in IEEE 754, NaNs are used
to represent error conditions. They are not considered valid values.
They are used to indicate an invalid result, or an inconsistent
value. They are not supposed to outlive a calculation. We're
talking about a data type that exists to express numbers, yet these
values are "Not-a-Number". I don't know how much more clear that can be.
This totally unlike INF and -INF, which represent real numerical
concepts. While clearly a "special" value, INF and -INF are
considered "valid" values. And, as has been pointed out several
times already, SQLite will calculate and store INF and -INF values.
Considering NaNs and INF/-INF to be similar shows a misunderstanding
of the purpose of these values in IEEE 754.
> If you boast IEEE you should be storing those.
I wouldn't consider the use of IEEE 754 a boast. Quite the opposite,
in fact. Most large-scale databases offer arbitrary precision real
numeric values. The use of floating-point values is usually
considered a big step down. Anyone working with money should shutter
at the thought of using of floating point numbers. It's a short-cut
to keep the "lite" in SQLite. Many database professionals would
point at the use of IEEE 754 as an indication that SQLite is but a
toy, and can't be used for serious numeric work.
> Can those values be passed from a 'double' C variable ? I believe so.
> So I see no reason why SQLite shouldn't be storing them.
If, in the sense of "can I arrange the bits that way", then yes.
However, in the sense of "is this a valid floating point number?" the
answer is no.
And again, I'd point out that the parameters for sqlite3_bind_text()
allow me to pass in a negative string length. Just because I can
arrange the bits that way, does that make it right or sensible?
Should SQLite store strings with a negative length just because the
API allows me to express them? What does that even mean?
Or does it make more sense for SQLite to look at the incoming
values, and consider some subset of values to be nonsense, and in
need of conversion (or an error condition)?
If you really want to store arbitrary 8-byte patterns, go ahead and
shove them into a BLOB. But if you want something that works with
SQLite's ability to express values and perform operations, you need
to use value values. SQL already has a "special" value that all
operations, functions, and calculations understand: NULL.
> And given that SQLite does calculations internally you also have to
> get the correct IEEE-specified results if someone does
Ahh... but SQLite doesn't do those calculations "internally". It is
completely dependent on the host platform to provide a numeric
environment.
> SELECT a * b FROM myTable
>
> or such things. SQLite should return the right result.
Fine. But, define "right." In this case, for two very large values,
the answer would be +INF, which SQLite does return, and will happily
store.
On the other hand, the classic 1.0/0.0 returns NULL-- which if you
knew something about math and databases, but didn't know much about
IEEE 754, sounds about right... and that's assuming you didn't expect
it to just return an error.
-j
--
Jay A. Kreibich < J A Y @ K R E I B I.C H >
"Intelligence is like underwear: it is important that you have it,
but showing it to the wrong people has the tendency to make them
feel uncomfortable." -- Angela Johnson
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
