On Tue, Nov 1, 2011 at 10:34 AM, Ryan Belcher <[email protected]> wrote:
> Sometimes queries using < and > will include values that are equal. Here > is an example. > > SELECT id,base,thick FROM test_col WHERE base > 281.01 AND base - thick < > 283.11; > > Id|base|thick > 89|281.04|0.03 > 90|282.09|1.05 > 91|283.11|1.02 > 92|290.08|6.97 > > For that last result, 290.08 - 6.97 = 283.11; so base - thick is equal to > 283.11 but not less. Is this a bug or something I'm not doing right or > don't understand? > Floating-point numbers are approximations. There is no way to represent 290.08 or 6.97 or 283.11 in binary. The closest you can get to these numbers using IEEE doubles is: 290.07999999999998408384271897375583648681640625 6.96999999999999975131004248396493494510650634765625 283.1100000000000136424205265939235687255859375 And to compound the difficulty, SQLite does not attempt to get that close, as doing so requires infinite-precision integer math in the worst case and a noticeable performance hit. SQLite, therefore, goes for a rougher approximation and settles for 14 significant digits. Floating point is designed to represent physical measurements (distance, voltage, mass, etc.) and no instrument you have ever seen in your lifetime is able to measure a physical quantity to anything close to 14 significant digits. Even financial quantities are never truly known to 14 significant digits, as 1% interest compounded continuously changes the 13th digit every millisecond or so. This is why you are admonished to never compare floating point numbers for equality - in any system, not just in SQLite. > > ________________________________ > This communication contains information which is confidential and may also > be privileged. It is for the exclusive use of the intended recipient(s). If > you are not the intended recipient(s), please note that any distribution, > copying or use of this communication or the information in it is strictly > prohibited. If you have received this communication in error, please notify > the sender immediately and then destroy any copies of it. > _______________________________________________ > sqlite-users mailing list > [email protected] > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- D. Richard Hipp [email protected] _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
