Re: [sqlite] Bug in unique index

[petern]

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 
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
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Bug in unique index

[Simon Slavin]

> On 23 Jan 2018, at 4:35pm, Domingo Alvarez Duarte  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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] 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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users