Re: [sqlite] Unique index on REAL values and equality check

[Alexey Pechnikov]

Hello!

On Monday 14 December 2009 01:35:46 Samuel Adam wrote:
> > 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 converting binary to decimal.

So a index compare stored binary values... Thanks.

Best regards, Alexey Pechnikov.
http://pechnikov.tel/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Unique index on REAL values and equality check

[Samuel Adam]

On Sun, 13 Dec 2009 17:02:05 -0500, Alexey Pechnikov 
 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

Re: [sqlite] Unique index on REAL values and equality check

[Alexey Pechnikov]

Hello!

On Monday 14 December 2009 01:14:25 Doug Currie wrote:
> The problem is that floating point values (in SQLite shell) do not have 
> write-read idempotency

Please read my message again. The index work correct but why?

Best regards, Alexey Pechnikov.
http://pechnikov.tel/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Unique index on REAL values and equality check

[Doug Currie]

On Dec 13, 2009, at 3:16 PM, Alexey Pechnikov wrote:

> As we can see, the unique index can check equlity of REAL values 
> but the "=" operator can not. it's fantastic I think :-)

The problem is not the "=" operator...

sqlite> create table test (save_date REAL unique);
sqlite> insert into test values (julianday('now'));
sqlite> select rowid,* from test;
1|2455179.42227787
sqlite> insert into test select * from test;
SQL error: column save_date is not unique
sqlite> select count(*) from test where save_date=2455179.42227787;
0
sqlite> select count(*) from test where save_date in (select save_date from 
test where rowid=1);
1
sqlite> 

The problem is that floating point values (in SQLite shell) do not have 
write-read idempotency. I have moaned about that on this mailing list for 
years! ;-)
http://www.mail-archive.com/sqlite-users@sqlite.org/msg09529.html

e

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Unique index on REAL values and equality check

[Alexey Pechnikov]

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?

Best regards, Alexey Pechnikov.
http://pechnikov.tel/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Unique index on REAL values and equality check

[Samuel Adam]

On Sun, 13 Dec 2009 15:16:08 -0500, Alexey Pechnikov 
 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

[sqlite] Unique index on REAL values and equality check

[Alexey Pechnikov]

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 :-)

Best regards, Alexey Pechnikov.
http://pechnikov.tel/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users