Bug: float granularity breaking unique contraint?

classic Classic list List threaded Threaded
8 messages Options
Reply | Threaded
Open this post in threaded view
|

Bug: float granularity breaking unique contraint?

szmate1618
Dear SQLite people,
Please bless me with your infinite wisdom.
I'm using SQLite 3.25.2 on Windows, downloaded the latest precompiled
binaries from the official page https://sqlite.org/download.htmlExecuting
the following code

DROP TABLE IF EXISTS TestReal;CREATE TABLE TestReal(A REAL
UNIQUE);INSERT INTO TestReal values (9223372036854775807.0);INSERT
INTO TestReal values (9223372036854775807.0 - 1);INSERT INTO TestReal
values (9223372036854775807.0 - 2);INSERT INTO TestReal values
(9223372036854775807.0 - 3);

fails as expected, since 9223372036854775807.0 is 2^63, these numbers are
way out of the range where all integers are exactly representable as
doubles. I mean

sqlite> select 9223372036854775807.0 = 9223372036854775807.0 - 1;1
sqlite> select 9223372036854775807.0 = 9223372036854775807.0 - 512;1

And column A is unique, so it makes perfect sense to print a 'UNIQUE
constraint failed: TestReal.A' message. But there seems to be an unintended
workaround

DROP TABLE IF EXISTS TestReal;CREATE TABLE TestReal(A REAL
UNIQUE);INSERT INTO TestReal values (9223372036854775807);INSERT INTO
TestReal values (9223372036854775807 - 1);INSERT INTO TestReal values
(9223372036854775807 - 2);INSERT INTO TestReal values
(9223372036854775807 - 3);

runs without any problems. The following queries confirm that the table now
has exactly 4 values inserted, but only one distinct value, despite of
having a unique constraint

sqlite> SELECT * FROM
TestReal;9.22337203685478e+189.22337203685478e+189.22337203685478e+189.22337203685478e+18
sqlite> SELECT DISTINCT(A) FROM TestReal;9.22337203685478e+18
sqlite> .schemaCREATE TABLE TestReal(A REAL UNIQUE);

So my question is: is this a bug in SQLite? Or do I not understand
correctly what 'unique' actually means?

I posted an identical question on StackOverflow.com,
because I'm still not 100% sure it is a bug. If so, sorry for wasting you
time.

Sincerely,
Máté Szabó
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Bug: float granularity breaking unique contraint?

Bernardo Sulzbach-2
It is not a bug AFAIK. SQLite uses what the documentation calls
dynamic typing for its actual values. So if you are inserting integers
into a real column, you are going to store integers. However, when you
select from it they are presented as reals and mix up (looking as if
there were duplicates, even though the stored values are truly
unique).
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Bug: float granularity breaking unique contraint?

James K. Lowden
On Thu, 1 Nov 2018 01:18:26 +0100
szmate1618 <[hidden email]> wrote:

> But there seems to be an unintended
> workaround
>
> DROP TABLE IF EXISTS TestReal;CREATE TABLE TestReal(A REAL
> UNIQUE);INSERT INTO TestReal values (9223372036854775807);INSERT INTO
> TestReal values (9223372036854775807 - 1);INSERT INTO TestReal values
> (9223372036854775807 - 2);INSERT INTO TestReal values
> (9223372036854775807 - 3);
>
> runs without any problems.

On Wed, 31 Oct 2018 23:05:19 -0300
Bernardo Sulzbach <[hidden email]> wrote:

> So if you are inserting integers
> into a real column, you are going to store integers. However, when you
> select from it they are presented as reals and mix up (looking as if
> there were duplicates [...]

I don't think that explanation holds water.  

sqlite>
DROP TABLE IF EXISTS TestReal;CREATE TABLE TestReal(A REAL
UNIQUE);INSERT INTO TestReal values (9223372036854775807);INSERT INTO
TestReal values (9223372036854775807 - 1);INSERT INTO TestReal values
(9223372036854775807 - 2);INSERT INTO TestReal values
(9223372036854775807 - 3);sqlite>    ...>    ...>    ...>    ...>

sqlite> select cast(A as integer) from TestReal;
9223372036854775807
9223372036854775807
9223372036854775807
9223372036854775807

sqlite> select hex(A) from TestReal;
392E3232333337323033363835343738652B3138
392E3232333337323033363835343738652B3138
392E3232333337323033363835343738652B3138
392E3232333337323033363835343738652B3138

sqlite> select count(*), hex(A) from TestReal group by hex(A);
4|392E3232333337323033363835343738652B3138

sqlite> .schema TestReal
CREATE TABLE TestReal(A REAL
UNIQUE);
sqlite>

Curiouser and curiouser.  

--jkl
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Bug: float granularity breaking unique contraint?

Bernardo Sulzbach-2
>
> I don't think that explanation holds water.
>

I don't have proof that I am correct, but you can try to test my
hypothesis the following way:

Let there be 4 databases:

-- f1.db
create table t(a real unique); insert into t values(9223372036854775807.0);
-- f2.db
create table t(a real unique); insert into t values(9223372036854775806.0);
-- i1.db
create table t(a real unique); insert into t values(9223372036854775807);
-- i2.db
create table t(a real unique); insert into t values(9223372036854775806);

Hash all 4 of them:

-- sha256sum *.db
a50f856aaaacefb0c6cedb3ca66a6539685a2463b47e9a4dc53c174d0a14b6f5  f1.db
a50f856aaaacefb0c6cedb3ca66a6539685a2463b47e9a4dc53c174d0a14b6f5  f2.db
c12ffcb265433dc61ed848962c4f5a05e215bba81ee6ad6db17b71f22cdbb463  i1.db
b2f58d7f372023145537978a0d13296d0bc47b1d75694feabf377908d0a5a7a8  i2.db

The ones which insert an integral-looking literal are storing
something different from what the ones that insert a
fractional-looking literal are. Also see that i1.db and i2.db are
different while f1.db and f2.db are equal.

Then, one might assume that internally the comparisons executed to
ensure uniqueness will behave differently too.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Bug: float granularity breaking unique contraint?

David Empson
In reply to this post by James K. Lowden
> On 2/11/2018, at 8:23 AM, James K. Lowden <[hidden email]> wrote:
>
> On Wed, 31 Oct 2018 23:05:19 -0300
> Bernardo Sulzbach <[hidden email]> wrote:
>
>> So if you are inserting integers
>> into a real column, you are going to store integers. However, when you
>> select from it they are presented as reals and mix up (looking as if
>> there were duplicates [...]
>
> I don't think that explanation holds water.  
>
> sqlite>
> DROP TABLE IF EXISTS TestReal;CREATE TABLE TestReal(A REAL
> UNIQUE);INSERT INTO TestReal values (9223372036854775807);INSERT INTO
> TestReal values (9223372036854775807 - 1);INSERT INTO TestReal values
> (9223372036854775807 - 2);INSERT INTO TestReal values
> (9223372036854775807 - 3);sqlite>    ...>    ...>    ...>    ...>
>
> sqlite> select cast(A as integer) from TestReal;
> 9223372036854775807
> 9223372036854775807
> 9223372036854775807
> 9223372036854775807
>
> sqlite> select hex(A) from TestReal;
> 392E3232333337323033363835343738652B3138
> 392E3232333337323033363835343738652B3138
> 392E3232333337323033363835343738652B3138
> 392E3232333337323033363835343738652B3138
>
> sqlite> select count(*), hex(A) from TestReal group by hex(A);
> 4|392E3232333337323033363835343738652B3138
>
> sqlite> .schema TestReal
> CREATE TABLE TestReal(A REAL
> UNIQUE);
> sqlite>
>
> Curiouser and curiouser.  

Continuing with James’s test case above, the following shows that the integer values are actually stored in the database as real.

select A,typeof(A) from TestReal;
9.22337203685478e+18|real
9.22337203685478e+18|real
9.22337203685478e+18|real
9.22337203685478e+18|real

Testing with smaller values:

INSERT INTO TestReal values(1); INSERT INTO TestReal values(1);
Error: UNIQUE constraint failed: TestReal.A

That did what I expected.

SELECT cast(A as integer) from TestReal;
9223372036854775807
9223372036854775807
9223372036854775807
9223372036854775807
1

INSERT INTO TestReal values(9223372036854775807);
Error: UNIQUE constraint failed: TestReal.A

The UNIQUE constraint works if the integer exactly matches the real-to-integer conversion of existing values in the column.

INSERT INTO TestReal values(9223372036854775807+1);

No error this time. The UNIQUE constraint didn’t pick that (9223372036854775807+1) will match existing values in the column when converted to real.

SELECT cast(A as integer) from TestReal;
9223372036854775807
9223372036854775807
9223372036854775807
9223372036854775807
1
9223372036854775807

Therefore it looks like the UNIQUE test is being done using the type of the value being inserted, rather than the type that will be stored in the column.


_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Bug: float granularity breaking unique contraint?

David Raymond
In reply to this post by James K. Lowden
I did a little Python script to go find what the data was that was actually being stored. They're each getting stored as serial type 6: "Value is a big-endian 64-bit twos-complement integer."

So apparently unique indexes consider uniqueness based on the *stored* value, whereas distinct queries, grouping, etc work on the value *after* it has been converted back to the column's declared type.


-----Original Message-----
From: sqlite-users [mailto:[hidden email]] On Behalf Of James K. Lowden
Sent: Thursday, November 01, 2018 3:23 PM
To: [hidden email]
Subject: Re: [sqlite] Bug: float granularity breaking unique contraint?

INSERT INTO TestReal values (9223372036854775807);INSERT INTO
TestReal values (9223372036854775807 - 1);INSERT INTO TestReal values
(9223372036854775807 - 2);INSERT INTO TestReal values
(9223372036854775807 - 3);sqlite>    ...>    ...>    ...>    ...>

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Bug: float granularity breaking unique contraint?

Dennis Clarke

>
> INSERT INTO TestReal values (9223372036854775807);INSERT INTO
> TestReal values (9223372036854775807 - 1);INSERT INTO TestReal values
> (9223372036854775807 - 2);INSERT INTO TestReal values
> (9223372036854775807 - 3);sqlite>    ...>    ...>    ...>    ...>
>

I recognize that number on sight and it would be more clear to everyone
if it were expressed in hex 0x7fffffffffffffff. At the limit of int64_t.

Dennis
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

json_each() in a table trigger for an 'attached' db does not work

Lindsay Lawrence
In reply to this post by David Raymond
Has anyone else run into this issue? Is there a workaround?

I have the following tables and trigger....

- Data table
CREATE TABLE people (
    id INTEGER PRIMARY KEY,
    created TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    name TEXT,
    age INTEGER
);

-- Change log table
CREATE TABLE change_log (
    id INTEGER PRIMARY KEY,
    created TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    action TEXT,
    table_name TEXT,
    obj_id INTEGER,
    changes TEXT
);

-- Insert Trigger
CREATE TRIGGER people_track_insert
AFTER INSERT ON people
BEGIN
  INSERT INTO change_log (action, table_name, obj_id, changes)
  SELECT
    'INSERT', 'people', NEW.id, changes
  FROM
    (SELECT
      json_group_object(col, json_array(oldval, newval)) AS changes
    FROM
      (SELECT
        json_extract(value, '$[0]') as col,
        json_extract(value, '$[1]') as oldval,
        json_extract(value, '$[2]') as newval
      FROM
        json_each(
          json_array(
            json_array('id', null, NEW.id),
            json_array('created', null, NEW.created),
            json_array('name', null, NEW.name),
            json_array('age', null, NEW.age)
          )
        )
      WHERE oldval IS NOT newval
      )
    );
END;

If I then do the following, the trigger works correctly, updating the
change_log table, etc

$> sqlite3  test.db
sqlite> INSERT INTO people (name, age) VALUES ('Alice', 30), ('Bob', 42);
sqlite> UPDATE people SET age = age + 2;
sqlite> UPDATE people SET name = 'Eva' WHERE name='Alice';
sqlite> DELETE FROM people WHERE name = 'Bob';
sqlite> SELECT * FROM change_log;

However, the above queries for the same db, when 'attached', fails.:

$> sqlite3
sqlite> attach './test.db' as test;
...

In the attached case any inserts into the table cause the trigger to fail
with something like the following error:


*Error: near line 1694: no such table: napp.json_each*

Somehow the virtual table json_each in the db trigger is not available for
an 'attached' db. It does work correctly for the main db. It also works for
an attached db as a normal query.

I am running the latest amalgamation code with the json1 extension enabled

sqlite-amalgamation-3250200.zip
(2.17 MiB) C source code as an amalgamation, version 3.25.2.
(sha1: c9ff08b91a0faacabe2acb240e5dba3cf81071f3)

Compiled with:

gcc -Os -I. -DSQLITE_THREADSAFE=0 -DSQLITE_ENABLE_JSON1 -DHAVE_USLEEP
-DHAVE_READLINE shell.c sqlite3.c -ldl -lreadline -lncurses -Os -o sqlite3

/Lindsay
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users