Note that even when the column is declared with no affinity that NaNs are stored as null. Other than NaN, all other IEEE754 double values (including subnormals and +/- Inf) seem to round-trip correctly through the bind_double and column_double interface ...
--- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. >-----Original Message----- >From: sqlite-users [mailto:sqlite-users- >boun...@mailinglists.sqlite.org] On Behalf Of Donald Shepherd >Sent: Thursday, 13 June, 2019 15:59 >To: General Discussion of SQLite Database >Subject: Re: [sqlite] [SPAM?] Re: [EXTERNAL] Re: Should SQLite >distinguish between +0.0 and -0.0 on output? > >On Fri., 14 Jun. 2019, 7:43 am Keith Medcalf, <kmedc...@dessus.com> >wrote: > >> On Thursday, 13 June, 2019 15:21, Donald Shepherd < >> donald.sheph...@gmail.com> wrote: >> >> >On Fri, 14 Jun 2019 at 7:11 am, Richard Hipp <d...@sqlite.org> >wrote: >> >> >> On 6/13/19, Donald Shepherd <donald.sheph...@gmail.com> wrote: >> >> >>> Given there's been numerous comments to the effect that SQLite >> >>> now supports -0.0 storing and retrieval other than printing, >> >>> I'm curious which version this was implemented in as I wouldn't >> >>> mind removing my custom code when we move to a SQLite version >> >>> with this improvement. >> >> >> SQLite version 3.0.0 from 2004-06-17. >> >> >When I reported that testing on 3.8.7.2 stored -0.0 but retrieved >> >0.0, your comment on this list was that "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." >> >> >I was wondering when this changed? >> >> >That doesn't seem like "supporting -0.0 storing and retrieval" >since >> >3.0.0.0 if it was previously being modified to another value >during >> >storage, losing the sign in the process. >> >> Actually, it depends on whether the database column has real >affinity or >> not. If it has no affinity then the value is not "converted". >> >> If you declare the database column thusly: >> >> create table x(x); -- column x has no affinity >> >> then storing a double -0.0 in the column either via the statement >INSERT >> INTO X VALUES (-0.0); or through binding a double containing -0.0 >as a >> parameter to INSERT INTO X VALUES (?) results in the double -0.0 >being >> stored in the database and retrieved with column_double against the >query >> SELECT X FROM X; >> >> However, if you declare the database column thusly: >> >> create table x(x real); -- column x has real affinity >> >> then all manner of attempting to store -0.0, 0, 0.0 results in >> column_double retrieving 0.0 (stripping the sign) since what is >actually >> stored is the two's complement integer 0 which does not have a sign >(or >> rather, the sign is +ve). >> > >Thanks, that's an interesting wrinkle I might be able to use. > >Regards, >Donald Shepherd. > >> >_______________________________________________ >sqlite-users mailing list >sqlite-users@mailinglists.sqlite.org >http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
