Re: [sqlite] Truncation of floating point numbers in SQLite?

[Scott Robison]

On Mon, Feb 2, 2015 at 8:08 PM, Donald Shepherd 
wrote:

> 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:
>

Someone mentioned the hardware representation of double & it being used in
the database. There are other formats in use, though the prevalence of the
x86 line of CPUs (particularly the 80387 coprocessor and the floating point
units included in 80486 & later chips [don't get me started on the 80486
SX, though]) have moved most of the world in the direction of the IEEE-754
1985 standard. Still, some processors don't have any floating point, some
have some type of fixed point, and yet others use completely different
binary representations of floating point numbers. Given how ubiquitous
IEEE-754 is in "modern" processors, I doubt there is anything in SQLite
that tries to accommodate any other floating point standards (though I
could be wrong).

If that is true (no other format support), the free SoftFloat library could
be used to provide floating point support on systems which do not support
IEEE-754 natively.

SDR
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Truncation of floating point numbers in SQLite?

[Donald Shepherd]

On Tue Feb 03 2015 at 12:23:29 PM James K. Lowden 
wrote:

> On Sun, 1 Feb 2015 02:13:15 +0100
> Stephan Beal  wrote:
>
> > On Sun, Feb 1, 2015 at 2:07 AM, Simon Slavin 
> > 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
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Truncation of floating point numbers in SQLite?

[James K. Lowden]

On Sun, 1 Feb 2015 02:13:15 +0100
Stephan Beal  wrote:

> On Sun, Feb 1, 2015 at 2:07 AM, Simon Slavin 
> 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.  

--jkl
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Truncation of floating point numbers in SQLite?

[Stephan Beal]

On Sun, Feb 1, 2015 at 2:07 AM, Simon Slavin  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.


> Because there's nothing in the SQLite documentation that says it can store
> values like NaN or -0.0.  The documentation just says it can store
> numbers.  It should be possible to find out when the distinction between
> 0.0 and -0.0 gets lost.
>

sqlite's platform (C89) doesn't natively support it, so if it's there then
sqlite3 is either supporting it itself (custom code) or is relying on
C99-specific APIs (which, to the best of my (fallible) knowledge, it does
not do).

-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
http://gplus.to/sgbeal
"Freedom is sloppy. But since tyranny's the only guaranteed byproduct of
those who insist on a perfect world, freedom will have to do." -- Bigby Wolf
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Truncation of floating point numbers in SQLite?

[Simon Slavin]

On 31 Jan 2015, at 11:14pm, James K. Lowden  wrote:

> That said, https://www.sqlite.org/datatype3.html says REAL is "stored
> as an 8-byte IEEE floating point number".  I can't see why a
> non-signalling NaN couldn't be stored and interpreted.  You'd need some
> convention for collation, and a function like is_nan() would be helpful
> for WHERE clauses.  

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 ...

There's a bit representation for storing NaN in a binary64.  Presumably, if you 
have NaN in a C variable, it's using that representation in memory.

Then you use sqlite3_bind_double() to get this into an SQLite variable, and use 
INSERT to get SQLite to put that into a database.  Do we expect to be able to 
see that pattern by looking through the database file using hexdump ?

Because there's nothing in the SQLite documentation that says it can store 
values like NaN or -0.0.  The documentation just says it can store numbers.  It 
should be possible to find out when the distinction between 0.0 and -0.0 gets 
lost.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Truncation of floating point numbers in SQLite?

[James K. Lowden]

On Fri, 30 Jan 2015 10:39:31 +0200
RSmith  wrote:

> At first I thought SQLite (or any RDBMS) should really strore
> whatever you give and return it untouched, but that cannot be true
> for an RDBMS because it has to interpret the data, it isn't just a
> binary store. It has to answer questions like SELECT ("colA"+3),
> (7/"colB");  or sort by colA or use a collation on strings etc. etc.
> - all of which means it must care about what the value relates to and
> cannot simply ignore it unless stored as an ignorant type (i.e Blob).

Yes, that's true.  Whenever a column value is interpreted by SQLite,
that interpretation might differ from the one used by the application
that inserted it.  

For example, I might have an 8-bit unsigned integer stored in column A
with the value 0xFF (all bits on).  The clause "WHERE A > 128" would
not return that row because SQLite interprets the column as signed and
the value as -1.  Similarly ORDER BY would not work in accordance with
the application's interpretation.  

That said, https://www.sqlite.org/datatype3.html says REAL is "stored
as an 8-byte IEEE floating point number".  I can't see why a
non-signalling NaN couldn't be stored and interpreted.  You'd need some
convention for collation, and a function like is_nan() would be helpful
for WHERE clauses.  

It could be argued that SQLite should *not* do that, in the interest of
simplicity.  NaN represents an invalid output, say sqrt(-2).  For any
function F, NaN = F (NaN), and NaN <> NaN.  Because SQL NULL works
similarly, ISTM a defensible alternative would be to say all NaNs
become NULL in the database, or raise an error.  That loses the
distinction between "missing" and "not computable", but that probably
doesn't matter in most practical senses.  

On the third hand, many numerical and stats packages, e.g. R, use NaN
(mistakenly, in my view) to represent missing data.  Integration with
such packages might be facilitated by "NaN fidelity" if we can call it
that, so that they can retrieve what they saved.  

> > Trying to retrieve a stored qNaN or sNaN returns a column type of
> > NULL and a value of 0.

That's consistent with division-by-zero yielding zero, and is an
impediment to using SQLite for scientific work.   

--jkl
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Truncation of floating point numbers in SQLite?

[Stephan Beal]

On Fri, Jan 30, 2015 at 3:45 AM, Donald Shepherd 
wrote:

> I'm still not convinced whether it's the behaviour causing my problem, but
> it does look like negative zero is another special case:
>
> SQLite version 3.8.7.2 2014-11-18 20:57:56
> Enter ".help" for usage hints.
> sqlite> create table datatable2 (doublevalue real);
> sqlite> insert into datatable2 values(-0.0);
>

FWIW, "special" doubles like inf, nan, and -0 have no standardized C89 APIs
(they were added in C99), so it is unsurprising that sqlite (C89, aside
from its use of "long long") treats signed and unsigned 0 the same.

http://en.wikipedia.org/wiki/C99#IEEE.C2.A0754_floating_point_support
http://stackoverflow.com/questions/9657993/negative-zero-in-c


According to this page:
http://stackoverflow.com/questions/5095968/does-float-have-a-negative-zero-0f

"the standard" (it's not clear if they mean C89 or C99) _requires_
"positive and negative zero to test as equal," an implication of which is
that it would be impossible to tell them apart in SQL implementations based
on that.

-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
http://gplus.to/sgbeal
"Freedom is sloppy. But since tyranny's the only guaranteed byproduct of
those who insist on a perfect world, freedom will have to do." -- Bigby Wolf
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Truncation of floating point numbers in SQLite?

[RSmith]

On 2015/01/30 05:49, Donald Shepherd wrote:

Trying to retrieve a stored qNaN or sNaN returns a column type of NULL and a 
value of 0.


Thank you for letting us know.

Well I suppose that's SQLite's method to answer with errors of the sort, returning NULL (as is the case with div0 for instance). 
Which makes some sense and is probably documented (though I did not check).  It seems to get really unhandled bitwise round-trips 
you will need to store/receive  blobs or strings in the 0xNNN format.


At first I thought SQLite (or any RDBMS) should really strore whatever you give and return it untouched, but that cannot be true for 
an RDBMS because it has to interpret the data, it isn't just a binary store. It has to answer questions like SELECT ("colA"+3), 
(7/"colB");  or sort by colA or use a collation on strings etc. etc. - all of which means it must care about what the value relates 
to and cannot simply ignore it unless stored as an ignorant type (i.e Blob).



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Truncation of floating point numbers in SQLite?

[Donald Shepherd]

Trying to retrieve a stored qNaN or sNaN returns a column type of NULL and
a value of 0.

On Thu Jan 29 2015 at 8:56:35 PM RSmith  wrote:

>
> On 2015/01/29 05:05, James K. Lowden wrote:
> > There's no reason to think, if the data are provided in binary form,
> that they won't be returned in the identical form absent an
> > explicit conversion. If that's not so, I'd sure like to know why. I'm
> faintly surprised NaNs can't be stored, too. Why should
> > SQLlite interpret them if they're bound to a double?
>
> Indeed, which is what all the posts have been saying more or less in terms
> of round-tripping all but NaNs.
>
> In the case of NaN though, there are two defined NaNs, namely qNaN and
> sNan which both means the same but the sNaN will cause an
> exception even at hardware level by merely passing through any register,
> which I believe is its intended purpose. This means that in
> order for software to work correctly, it should never let an sNaN pass
> through untouched, it should produce an error so all parties
> are savvy to the wrongness that just occured (Even if the software in use
> is not specifically checking for NaN, an sNaN should still
> cause an exception from lower down).
>
> qNaN should pass through and round-trip same as any other float. I'm not
> sure how SQLite handles either of these NaNs, but am now
> quite interested to know.
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Truncation of floating point numbers in SQLite?

[Richard Hipp]

On 1/29/15, Donald Shepherd  wrote:
> I'm still not convinced whether it's the behaviour causing my problem, but
> it does look like negative zero is another special case:
>
> SQLite version 3.8.7.2 2014-11-18 20:57:56
> Enter ".help" for usage hints.
> sqlite> create table datatable2 (doublevalue real);
> sqlite> insert into datatable2 values(-0.0);
> sqlite> select * from datatable2;
> 0.0
>

I suppose so.  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.

-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Truncation of floating point numbers in SQLite?

[Donald Shepherd]

I'm still not convinced whether it's the behaviour causing my problem, but
it does look like negative zero is another special case:

SQLite version 3.8.7.2 2014-11-18 20:57:56
Enter ".help" for usage hints.
sqlite> create table datatable2 (doublevalue real);
sqlite> insert into datatable2 values(-0.0);
sqlite> select * from datatable2;
0.0

When inserting it through the API I get the same results, i.e. the signed
bit looks like it gets stripped so technically the value retrieved is not
the bitwise equivalent of what's written in even though it is the logical
equivalent (-0.0 == 0.0).

On Thu Jan 29 2015 at 10:13:55 AM Donald Shepherd 
wrote:

> Thanks for the reassurances.  I have a case where differences in doubles
> would explain what I'm seeing but I have no evidence that it is the case
> (evidence compilation is still underway), hence my attempt to plumb the
> depths of the list's knowledge to see if there was any known edge cases to
> be aware of (other than the NaN one I'd already run into). :)
>
> On Thu Jan 29 2015 at 10:10:35 AM Simon Slavin 
> wrote:
>
>>
>> On 28 Jan 2015, at 10:47pm, Donald Shepherd 
>> wrote:
>>
>> > This is a bit of a speculative question related to a problem I'm having
>> -
>> > are there legal values of a C++ double that would get truncated when
>> > written into and read from an SQLite database?
>>
>> In theory there should be no problem here.
>>
>> C doubles have 15 to 16 digits of precision.
>>
>> In SQLite databases, numbers which can't be stored as integers are stored
>> as IEEE 754-2008 64-bit floating point numbers, sometimes known as
>> 'binary64'.  These give 15 to 17 digits of precision.
>>
>> My understanding is that it is possible to store every distinct C double
>> value as a distinct binary64 value.
>>
>> If it's the conversion that worries you, you can read the SQLite source
>> code to find the programming used to encode and decode numbers into this
>> format.  I am not competent to read that source code and tell you
>> definitely that it works for all C double values.
>>
>> Simon.
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Truncation of floating point numbers in SQLite?

[Keith Medcalf]

>I wonder what happens if you put SQLite on a computer with no native IEEE
>maths library.

Same as compiling with SQLITE_OMIT_FLOATING_POINT on a computer/compiler that 
*does* have floating point I should imagine -- you end up with a version of 
SQLite with all floating point omitted.

---
Theory is when you know everything but nothing works.  Practice is when 
everything works but no one knows why.  Sometimes theory and practice are 
combined:  nothing works and no one knows why.




___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Truncation of floating point numbers in SQLite?

[RSmith]

On 2015/01/29 05:05, James K. Lowden wrote:
There's no reason to think, if the data are provided in binary form, that they won't be returned in the identical form absent an 
explicit conversion. If that's not so, I'd sure like to know why. I'm faintly surprised NaNs can't be stored, too. Why should 
SQLlite interpret them if they're bound to a double?


Indeed, which is what all the posts have been saying more or less in terms of 
round-tripping all but NaNs.

In the case of NaN though, there are two defined NaNs, namely qNaN and sNan which both means the same but the sNaN will cause an 
exception even at hardware level by merely passing through any register, which I believe is its intended purpose. This means that in 
order for software to work correctly, it should never let an sNaN pass through untouched, it should produce an error so all parties 
are savvy to the wrongness that just occured (Even if the software in use is not specifically checking for NaN, an sNaN should still 
cause an exception from lower down).


qNaN should pass through and round-trip same as any other float. I'm not sure how SQLite handles either of these NaNs, but am now 
quite interested to know.


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Truncation of floating point numbers in SQLite?

[Igor Tandetnik]

On 1/28/2015 10:05 PM, James K. Lowden wrote:

I'm faintly surprised NaNs can't be stored, too.  Why should SQLlite
interpret them if they're bound to a double?


Signaling NaN may trigger a hardware exception (aka a signal) merely by 
being loaded into an FPU register (that's what makes it "signaling"), 
depending on how FPU is configured; which in turn could happen merely by 
passing it to a function, or returning from one. Quiet NaNs should 
round-trip just fine.

--
Igor Tandetnik

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Truncation of floating point numbers in SQLite?

[Simon Slavin]

On 29 Jan 2015, at 3:05am, James K. Lowden  wrote:

> We can make stronger statements than that, can't we?  It's not like
> there's some mystery about it: database and memory share a single
> floating-point format.  
> 
> If the caller uses _bind_double and _column_double, he's storing and
> fetching a 64-bit double.  SQLlite's only job is to collect those 8
> bytes, store them, and return them unmolested.

Ah well, the reason I didn't state this was that I didn't know it.  Thanks for 
the information.

I wonder what happens if you put SQLite on a computer with no native IEEE maths 
library.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Truncation of floating point numbers in SQLite?

[James K. Lowden]

On Wed, 28 Jan 2015 23:09:21 +
Simon Slavin  wrote:

> > This is a bit of a speculative question related to a problem I'm
> > having - are there legal values of a C++ double that would get
> > truncated when written into and read from an SQLite database?
> 
> In theory there should be no problem here.
> 
> C doubles have 15 to 16 digits of precision.
> 
> In SQLite databases, numbers which can't be stored as integers are
> stored as IEEE 754-2008 64-bit floating point numbers, sometimes
> known as 'binary64'.  These give 15 to 17 digits of precision.

We can make stronger statements than that, can't we?  It's not like
there's some mystery about it: database and memory share a single
floating-point format.  

If the caller uses _bind_double and _column_double, he's storing and
fetching a 64-bit double.  SQLlite's only job is to collect those 8
bytes, store them, and return them unmolested.  It has no private
definition of "double", offers no conversion between different
representations of double.  The bit pattern -- IEEE 754-2008, as you
say -- is defined by the *hardware*.  That format is shared up and down
the stack, regardless of whether or not SQLite and the application were
compiled using the same compiler.  

(That's no 100% true, of course.  Is anyone seen SQLite running on a
VAX?  Has it ever been done?)  

So I think it's a bit disingenuous to speak in terms of digits of
precision.  Application and library alike share a single IEEE
double-precision floating point representation.  There's no reason to
think, if the data are provided in binary form, that they won't be
returned in the identical form absent an explicit conversion.  If
that's not so, I'd sure like to know why.  

I'm faintly surprised NaNs can't be stored, too.  Why should SQLlite
interpret them if they're bound to a double?  

--jkl
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Truncation of floating point numbers in SQLite?

[Donald Shepherd]

Thanks for the reassurances.  I have a case where differences in doubles
would explain what I'm seeing but I have no evidence that it is the case
(evidence compilation is still underway), hence my attempt to plumb the
depths of the list's knowledge to see if there was any known edge cases to
be aware of (other than the NaN one I'd already run into). :)

On Thu Jan 29 2015 at 10:10:35 AM Simon Slavin  wrote:

>
> On 28 Jan 2015, at 10:47pm, Donald Shepherd 
> wrote:
>
> > This is a bit of a speculative question related to a problem I'm having -
> > are there legal values of a C++ double that would get truncated when
> > written into and read from an SQLite database?
>
> In theory there should be no problem here.
>
> C doubles have 15 to 16 digits of precision.
>
> In SQLite databases, numbers which can't be stored as integers are stored
> as IEEE 754-2008 64-bit floating point numbers, sometimes known as
> 'binary64'.  These give 15 to 17 digits of precision.
>
> My understanding is that it is possible to store every distinct C double
> value as a distinct binary64 value.
>
> If it's the conversion that worries you, you can read the SQLite source
> code to find the programming used to encode and decode numbers into this
> format.  I am not competent to read that source code and tell you
> definitely that it works for all C double values.
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Truncation of floating point numbers in SQLite?

[Simon Slavin]

On 28 Jan 2015, at 10:47pm, Donald Shepherd  wrote:

> This is a bit of a speculative question related to a problem I'm having -
> are there legal values of a C++ double that would get truncated when
> written into and read from an SQLite database?

In theory there should be no problem here.

C doubles have 15 to 16 digits of precision.

In SQLite databases, numbers which can't be stored as integers are stored as 
IEEE 754-2008 64-bit floating point numbers, sometimes known as 'binary64'.  
These give 15 to 17 digits of precision.

My understanding is that it is possible to store every distinct C double value 
as a distinct binary64 value.

If it's the conversion that worries you, you can read the SQLite source code to 
find the programming used to encode and decode numbers into this format.  I am 
not competent to read that source code and tell you definitely that it works 
for all C double values.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Truncation of floating point numbers in SQLite?

[RSmith]

On 2015/01/29 01:00, Donald Shepherd wrote:

I can say there's no string round-trips with fairly high confidence and I
am using _bind_double and _column_double.

I can also confirm NaNs are a special case, as I've had to write code to
store those as a BLOB.


So you have a case where you have a 64-bit IEEE754 Float (C - double) which isn't a NaN and add it to a query by 
sqlite3_bind_double() and then retrieve it back using column_double() and the bit pattern across the 8 bytes is different to the 
original?


Please  do share!



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Truncation of floating point numbers in SQLite?

[Donald Shepherd]

I can say there's no string round-trips with fairly high confidence and I
am using _bind_double and _column_double.

I can also confirm NaNs are a special case, as I've had to write code to
store those as a BLOB.

On Thu Jan 29 2015 at 9:57:14 AM Igor Tandetnik  wrote:

> On 1/28/2015 5:47 PM, Donald Shepherd wrote:
> > This is a bit of a speculative question related to a problem I'm having -
> > are there legal values of a C++ double that would get truncated when
> > written into and read from an SQLite database?
>
> Written into and read from how, exactly? Do they, say, round-trip
> through a string representation at any point?
>
> I'm pretty sure that, if you put a value into a column with correct
> affinity using sqlite3_bind_double, you'd get the exact same value out
> using sqlite3_column_double (signalling NaN possibly excepted).
> --
> Igor Tandetnik
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Truncation of floating point numbers in SQLite?

[Igor Tandetnik]

On 1/28/2015 5:47 PM, Donald Shepherd wrote:

This is a bit of a speculative question related to a problem I'm having -
are there legal values of a C++ double that would get truncated when
written into and read from an SQLite database?


Written into and read from how, exactly? Do they, say, round-trip 
through a string representation at any point?


I'm pretty sure that, if you put a value into a column with correct 
affinity using sqlite3_bind_double, you'd get the exact same value out 
using sqlite3_column_double (signalling NaN possibly excepted).

--
Igor Tandetnik

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users