Cariotoglou Mike wrote: > > as you may remember, some time ago I raised an issue with floating point > accuracy, and how this may affect sqlite. > > I now have a concrete example, which actually happened in an > installation, and helps to demonstrate the severity of the issue: > > try this code: > > create table test(f double); > insert into test values(13.04); > update test set f=f+0.02; > select * from test where f=13.06; -- returns no data > > can you imagine how many bugs waiting to happen are out there, because > of code like this ? > > I know that there are a number of solutions to this problem, all > involving changing the sql involved. > however, these are not applicable to people using SQLITE via wrappers > that generate their own UUPDATE code. > > what I have been trying to say is that, there is also a neat solution to > the problem, and one that can be implemented easily : > > pragma floating_accuracy=0.0000001 > > setting the threshold for float comparisons to some predictable value. > > Am I the only one that sees the problem? if not, please speak up, and > maybe we can get a neat solution!
Well, I have come across it in many other contexts and there is no general solution, unfortunately - well, using decimal floating-point arithmetic might in some cases solve it (at least give more predictable results, though not always). What about the scale? I assume that the above accuracy refers to the range [value-0.0000001,value+0.0000001]. That would fail with numbers smaller than 1/1000000 - not that uncommon. On the other hand a relative accuracy of say 1.0e-4% would fail if you have numbers around zero: -0.0000001 could well be approximately 0 or 0.0000001. I think you will need (at least?) two measures of tolerance: an absolute value and a relative one ... Regards, Arjen
