On Sun, 13 Dec 2009 15:16:08 -0500, Alexey Pechnikov
<[email protected]> 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
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
