On Wednesday, 31 July, 2019 17:29, Simon Slavin <slav...@bigfraud.org> wrote:
>On 31 Jul 2019, at 11:58pm, Keith Medcalf <kmedc...@dessus.com> wrote: >> it depends on the application of affinity. If you are storing the >floating point value in a column that does not have an affinity (ie, >no conversions are performed), then it is stored exactly (except for >NaN). Application of affinity (ie, real) will cause the -0.0 to be >stored as the integer 0 and thus the sign will be lost on retrieval >(as well as the conversion of NaN to NULL). ><https://www.sqlite.org/datatype3.html> >in section 3.2 states "Every table column has a type affinity (one of >BLOB, TEXT, INTEGER, REAL, or NUMERIC)" Section 3 states: (Historical note: The "BLOB" type affinity used to be called "NONE". But that term was easy to confuse with "no affinity" and so it was renamed.) >Do you feel that your demonstration (which I find convincing) agrees >or disagrees with that statement ? You seem to have found a sixth >column affinity: none. Certainly columns declared with no affinity >do not behave the same as any of the five documented affinities. It agrees. Columns declared with no affinity behave as if they had been declared with BLOB infinity and v/v. That means that they do not attempt to convert whatever is presented to be stored as something else. What is presented is stored as presented. A column affinity (other than BLOB) means that for affinity X if the thing being stored looks like X and quacks like X and can be losslessly converted to X, then store X (which includes storing floating values that will fit in an integer as integers in order to save space). Affinity BLOB means to not be doing that -- store what you is given. >Should the documentation be updated ? No. Though maybe it should be put on the quirks page for those very few things that need to distinguish between -0.0 and +0.0 >What happens if you add a line to your demo code > > for row in db.execute('select x from x ORDER BY x'): print row > >Does -0.0 get sorted before or with 0.0 ? I'd do it myself but I >don't know Python. No. -0.0 and 0.0 sort equal, as they should because they are equal. Mostly. -- 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
