On Sun, 13 Dec 2009 15:16:08 -0500, Alexey Pechnikov <pechni...@mobigroup.ru> wrote:
> Hello! > > I have some frustration about > > create table test ( > save_date REAL unique > ); > insert into test values (julianday('now')); > select * from test; > 2455179.34204073 > insert into test select * from test; > Error: constraint failed > select count(*) from test where save_date=2455179.34204073; > 0 > > As we can see, the unique index can check equlity of REAL values > but the "=" operator can not. it's fantastic I think :-) Smells like a decimal/binary rounding error. Try it from C (or whatever), feeding back *exactly* the result of your SELECT as a bound value. I would be quite surprised if = didn’t work. Although I haven’t done the math here, experience suggests that you are dealing with two values which are actually unequal on the bit level. SQLite really needs a DECIMAL type (e.g. based on IEEE 754-2008 BCD) so these questions can be answered “you’re using the wrong type” rather than “the database can’t count like a normal human”. BCD floats still have rounding issues common to all floats, but let’s take one thing at a time; on a different but related note, things like this should never exist: http://www.sqlite.org/faq.html#q16 I am aware that platform support is awful, of course. Samuel Adam _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users