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