At 21:53 24/09/2014, you wrote:
> If the default cannot be represented exactly, its rounding will be the
> least of the difficulties.
Not always: in scientific applications I've had column values default
to plenty of irrational numbers: fractions of pi, sqrt(2.0) etc.
My main gripe is when the value being read from a database isn't
identical to the value written to the database. I've had problems in
the past where conversion between double and text, e.g. sprintf() and
atof(), isn't perfectly invertible even at 17+ significant figures.
Perhaps this may only be an issue on some of the older platforms I've
worked on or on traditional client/server architectures (with which
I'm more familiar) where client and server can be different platforms.
I haven't (yet) tested SQLite empirically, though I notice SQLite has
its own atof() implementation sqlite3AtoF() which weakens my
confidence that I'll get out exactly what float I put in represented
as text. In my (admittedly limited) experience, IEEE754
implementations are transferable, when endian-flipped as appropriate.
While it's only almost true that IEEE754 (which flavor?) are
"transferable", you still seem to bark at the wrong tree.
Like other contributors have already said, you definitely can't rely on
a double stored value to represent exactly a real literal you supply.
For instance as shown below, 0.75 = 0.5 + 0.25 has a bounded
representation as fractional binary (1/2 + 1/4). On the contrary, 0.3
doesn't enjoy the same property and it would need an infinite string of
decreasing powers of 2 to represent: its floating point representation
is inexact.
0.75 is represented exactly in IEEE754 by X'3FE8000000000000'
0.3 is represented approximately in IEEE754 by X'3FD3333333333333'
The finite set of reals (rational numbers in fact) that IEEE754 can
represent is infinitesimally smaller than the set of reals, whatever
finite number of bits the FP representation uses.
Here's a real-world scenario where this could cause an issue (I've
actually encountered this for real) Imagine an application writer
who's instructed my library to use pi (say) as a column's default.
When reading the value out of the database, they may want to test if
the value equals the default...
One way to get rid of that: use the output of
select
3.1415926535897932384626433832795028841971693993751058209749445923078164062862089986280348253421170679
which gives:
3.14159265358979
That value is now safe.
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
