I have an engineering application, where double can be part of a key. In cases where I do a comparison that implies a test for equality, I don't necessarily care what happens in the really close cases.
SELECT x,y FROM mytable WHERE x >= 1234 AND x < 5678 There may be about a 10000 records in that time range, and for engineering purposes when I write that I don't particularly care whether I am getting 10000, 10001 or 10002 entries. However I do depend on getting consistent answers, and on (x >= 1234) being disjoint from (x < 1234) and that those two ranges cover the number line. I suspect that sqlite makes that kind of guarantee, at least if I explicitly cast values to double whenever I do an INSERT or UPDATE or WHERE, although that seems like an easy thing to forget to do. However, nothing in the sqlite documentation promises that sqlite won't internally perform an equality test on primary keys, so it seems that I have to worry that if equality is not transitive, my database can be in arbitrarily bad shape (select gives answers that aren't even close to looking right). Is the answer for this kind of thing to cast all of my values to double when feeding them to sqlite, if I want it to do math using "double" rules? I suspect sqlite could get the "correct" answers when doing comparisons between two types where one type is not a superset of the other by converting both values to bigint rationals and comparing those (I think the sqlite numeric types are all representable as bigint rationals). That may be overkill (and not all that lite). Regards -----Original Message----- From: Richard Hipp [mailto:d...@sqlite.org] Sent: Thursday, November 05, 2015 8:55 AM To: SQLite mailing list Subject: Re: [sqlite] Non-transitive numeric equality On 11/5/15, Zsb?n Ambrus <ambrus at math.bme.hu> wrote: >... It seems that equality of numeric values isn't transitive, when both > integers and reals are involved... You should not compare floating-point numbers for equality. Floating-point numbers are, by definition, approximations. When you compare floating-point numbers, therefore, you get an approximate answer. -- D. Richard Hipp drh at sqlite.org ************************************************************************************** This e-mail and any attachments thereto may contain confidential information and/or information protected by intellectual property rights for the exclusive attention of the intended addressees named above. If you have received this transmission in error, please immediately notify the sender by return e-mail and delete this message and its attachments. Unauthorized use, copying or further full or partial distribution of this e-mail or its contents is prohibited. **************************************************************************************
