Thanks to all who responded. I understand the problem now. I also see the wisdom of using integer type as a substitute for not having decimal type.
I do have one other question. Are there more downsides to using a tolerance value in my comparisons? It seems to me that either going the integer or tolerance route, I'm committing to a fixed precision for base and thick. If I know that base and thick never use more than .0001 of precision and sqlite uses 13 digits then I should be safe using .0000001 as a tolerance value, right? Or am I heading for trouble going that route? -----Original Message----- From: [email protected] [mailto:[email protected]] On Behalf Of Simon Slavin Sent: Tuesday, November 01, 2011 11:03 AM To: General Discussion of SQLite Database Subject: Re: [sqlite] Occasional problems with < and > On 1 Nov 2011, at 2:52pm, Richard Hipp wrote: > This is why you are admonished to never compare floating point numbers for > equality - in any system, not just in SQLite. And in this case, the most rigid and correct solution is to hold those numbers as integers. So given the numbers you supplied were > Id|base|thick > 89|281.04|0.03 > 90|282.09|1.05 > 91|283.11|1.02 > 92|290.08|6.97 my guess is that you should multiply those measurements by 100. Alternatively you can simply expect to lose the precision of the last digit. In other words, if the database holds a number that looks like abc.de and 'e' is a 7 in this case, you can expect that comparisons might interpret this as a 6, 7 or 8 randomly, and plan your application accordingly. Which of the two above solutions you choose depends on the nature of your system. The first gives you technically correct answers but requires reformatting of numbers when they're input and output. The second is simpler and requires less programming but requires users to understand 'slop'. Simon. _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users 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
