On Sun, 13 Dec 2009 17:02:05 -0500, Alexey Pechnikov <pechni...@mobigroup.ru> wrote:
> Hello! > > On Monday 14 December 2009 00:24:42 Samuel Adam wrote: >> Smells like a decimal/binary rounding error. > > It'is not the answer to my question. Why the unique _index_ > can understand when REAL values are equal? Because the unique index is not rounding and not converting binary to decimal. Going back to your original post, when you did this: > select * from test; > 2455179.34204073 …it gave you a rounded decimal approximation[†], which you then used here: > select count(*) from test where save_date=2455179.34204073; > 0 By contrast, this used the actual floating-point value, not the rounded value: > insert into test select * from test; > Error: constraint failed As I said, take the result of your SELECT * programmatically and use parameter binding for your SELECT count()… WHERE. If it still returns 0, then it means my answer to your correction is incorrect[‡]. But I doubt it will return 0. For further information, do web searches regarding “decimal floating point” and “binary decimal conversion”. This kind of error is quite common, and is one of the computing industry’s dirtiest open secrets. Contemplate this: If you put a penny into a binary float, you do not get a penny back out. Binary floats are manifestly unsuitable for most applications found in day-to-day human life; but programmers don’t know this, so they keep using binary floats incorrectly, and most people don’t care, so platforms support of decimal floats is not universal as it should be. Samuel Adam [†] Again, I haven’t done the math to verify that this is your problem. It’s impossible to take that step without extracting the 8-byte floating point value stored in your database. But this has “decimal conversion error” written all over it. Or perhaps another floating point round error, with the same end results: = seems to fail. Decimal conversion looks like the most probable culprit here. [‡] See above. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
