Re: [sqlite] Truncation of floating point numbers in SQLite?
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?
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?
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?
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?
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?
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?
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?
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?
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?
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?
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?
>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?
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?
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?
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?
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?
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?
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?
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?
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?
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] Truncation of floating point numbers in SQLite?
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? The column is specified as having REAL affinity though I gather that shouldn't matter. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users