On 2015-09-09 09:02 PM, Constantine Yannakopoulos wrote: > On Wed, Sep 9, 2015 at 9:47 PM, R.Smith <rsmith at rsweb.co.za> wrote: > >> On 2015-09-09 05:19 PM, Constantine Yannakopoulos wrote: >> >>> On Wed, Sep 9, 2015 at 4:54 PM, Igor Tandetnik <igor at tandetnik.org> >>> wrote: >>> >>> ?Out of curiosity, doesn't this also apply also to numeric (real number) >>> comparisons since SQLite3 uses IEEE floating point arithmetic?? >>> >> IEEE Float comparisons do not work this way - you are more likely to find >> the opposite: two numbers that seem to be near perfectly equal might fail >> an equality test. >> > ?That is the problem. There are cases where two numbers that come out of > different calculations? (especially if a division is included) are expected > to be equal but they fail the equality test. A classic case is when you > distribute an amount (e.g. a total) among several rows using a certain > column as weight and you expect the sum of the distributed amounts to be > exactly equal to the original total but it is not.
Indeed - but you can always avoid it when comparing values using your own epsilon difference test, something like: WHERE abs(x-y) < 0.00001 (Or whatever small value seems adequate to satisfy your equality specification). While on the subject - found this re-print and edited version of an old paper by David Goldberg highlighting all the IEEE pitfalls in computing - a valuable read to any programmer: https://docs.oracle.com/cd/E19957-01/806-3568/ncg_goldberg.html
