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). -- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
