Bug in unique index

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
4 messages Options
Reply | Threaded
Open this post in threaded view
|

Bug in unique index

Domingo Alvarez Duarte
Hello !

Maybe I found a bug in sqlite3 unique index, see example bellow:

====bug-unique.sql

drop table if exists test;
create table test(
     id integer primary key,
     v1 integer not null,
     v2 integer not null constraint not_equal check(v1 != v2),
     unique(v1, v2),
     unique(v2, v1)
);

insert into test values(1, 1, 2);
insert into test values(2, 2, 1);
select * from test;

====

sqlite3 < bug-unique.sql

====output

1|1|2
2|2|1
====

I was expecting to have an error trying to insert the second row but
sqlite3 accepted the duplicated index without error.

Cheers !


_______________________________________________
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 in unique index

Simon Slavin-3


> On 23 Jan 2018, at 4:35pm, Domingo Alvarez Duarte <[hidden email]> wrote:
>
> create table test(
>     id integer primary key,
>     v1 integer not null,
>     v2 integer not null constraint not_equal check(v1 != v2),
>     unique(v1, v2),
>     unique(v2, v1)
> );
>
> insert into test values(1, 1, 2);
> insert into test values(2, 2, 1); [snip]
>
> I was expecting to have an error trying to insert the second row but sqlite3 accepted the duplicated index without error.

You are not seeing any duplicates.

You have two separate UNIQUE requirements (which are equivalent to one-another, so one is not needed).  One of them is seeing (1, 2) and then (2, 1).  The other is seeing (2, 1) and then (1, 2).  Neither of them is seeing a duplicate.

Simon.
_______________________________________________
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: [EXTERNAL] Bug in unique index

Hick Gunter
In reply to this post by Domingo Alvarez Duarte
Not a bug.

Unique(v1,v2) implies unique(v2,v1) which makes the second definition superflous

(1,2) is distinct from (2,1) (because tuples are ordered), no violation of unique.

If you want to exclude equivalent sets, your check needs to be v1 < v2.

-----Ursprüngliche Nachricht-----
Von: sqlite-users [mailto:[hidden email]] Im Auftrag von Domingo Alvarez Duarte
Gesendet: Dienstag, 23. Jänner 2018 17:36
An: SQLite mailing list <[hidden email]>
Betreff: [EXTERNAL] [sqlite] Bug in unique index

Hello !

Maybe I found a bug in sqlite3 unique index, see example bellow:

====bug-unique.sql

drop table if exists test;
create table test(
     id integer primary key,
     v1 integer not null,
     v2 integer not null constraint not_equal check(v1 != v2),
     unique(v1, v2),
     unique(v2, v1)
);

insert into test values(1, 1, 2);
insert into test values(2, 2, 1);
select * from test;

====

sqlite3 < bug-unique.sql

====output

1|1|2
2|2|1
====

I was expecting to have an error trying to insert the second row but
sqlite3 accepted the duplicated index without error.

Cheers !


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


___________________________________________
 Gunter Hick | Software Engineer | Scientific Games International GmbH | Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.
_______________________________________________
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 in unique index

petern
In reply to this post by Domingo Alvarez Duarte
The second UNIQUE(v2,v1) constraint is redundant and equivalent to
UNIQUE(v1,v2)

Also consider that {(1,2),(2,1)} has no duplicates:

sqlite> WITH test(v1,v2) AS (VALUES (1,2),(2,1)) SELECT DISTINCT * FROM
test;
v1,v2
1,2
2,1

Peter


On Tue, Jan 23, 2018 at 8:35 AM, Domingo Alvarez Duarte <[hidden email]>
wrote:

> Hello !
>
> Maybe I found a bug in sqlite3 unique index, see example bellow:
>
> ====bug-unique.sql
>
> drop table if exists test;
> create table test(
>     id integer primary key,
>     v1 integer not null,
>     v2 integer not null constraint not_equal check(v1 != v2),
>     unique(v1, v2),
>     unique(v2, v1)
> );
>
> insert into test values(1, 1, 2);
> insert into test values(2, 2, 1);
> select * from test;
>
> ====
>
> sqlite3 < bug-unique.sql
>
> ====output
>
> 1|1|2
> 2|2|1
> ====
>
> I was expecting to have an error trying to insert the second row but
> sqlite3 accepted the duplicated index without error.
>
> Cheers !
>
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users