Re: [sqlite] A constraint bug?

[Jeremy Hinegardner]

On Sat, Oct 31, 2009 at 08:31:01AM -0400, D. Richard Hipp wrote:
> 
> On Oct 30, 2009, at 10:25 PM, Mick wrote:
> 
> > This is more FYI than needing it (as I have already worked around  
> > it), but I
> > have discovered that an IGNORE constraint on an insert, when one of  
> > the
> > fields in the constraint is NULL, will insert a duplicate record  
> > into the
> > database.
> 
> This is not a bug.  See, for example, the explanation on 
> http://www.sqlite.org/cvstrac/tktview?tn=3463
> 
> >
> > i.e.
> >
> > CREATE TABLE mytable (
> > ID1 INTEGER NOT NULL,
> > ID2 INTEGER NOT NULL,
> > SomeText VARCHAR(100) COLLATE NOCASE,
> > PRIMARY KEY (ID1, ID2, SomeText) ON CONFLICT IGNORE);
> >
> > INSERT INTO mytable VALUES (1, 1, NULL);
> > INSERT INTO mytable VALUES (1, 1, NULL);

As an aside, Postgres 8.4 does not allow the insert to happen in the first
place.  http://www.postgresql.org/docs/8.4/interactive/sql-createtable.html

"The primary key constraint specifies that a column or columns of a table 
can
 contain only unique (non-duplicate), nonnull values."

  [jer...@[local]] 12:59:21> create table mytable( id1 integer not null, 
   id2 integer not null, 
  sometext varchar(100),  
primary key(id1, id2, sometext));
  NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "mytable_pkey" 
for table "mytable" CREATE TABLE
  Time: 492.766 ms
  [jer...@[local]] 13:00:04> insert into mytable values(1,1,NULL);
  ERROR:  null value in column "sometext" violates not-null constraint

Change the PRIMARY KEY to a UNIQUE contraint and sqlite and postgres
agree on the behavior.  On the same postgresql page:

"For the purpose of a unique constraint, null values are not considered 
 equal."

  [jer...@[local]] 13:03:32> create table mytable( id1 integer not null, 
   id2 integer not null, 
  sometext varchar(100),
   unique(id1, id2, sometext));
  NOTICE:  CREATE TABLE / UNIQUE will create implicit index "mytable_id1_key" 
for table "mytable"
  CREATE TABLE
  Time: 121.473 ms
  [jer...@[local]] 13:03:49> insert into mytable values(1,1,NULL);
  INSERT 0 1
  Time: 18.476 ms
  [jer...@[local]] 13:03:54> insert into mytable values(1,1,NULL);
  INSERT 0 1
  Time: 0.539 ms
  [jer...@[local]] 13:03:56> select * from mytable;
   id1 | id2 | sometext 
  -+-+--
 1 |   1 | NULL
 1 |   1 | NULL
  (2 rows)

  Time: 14.775 ms

enjoy,

-jeremy

-- 

 Jeremy Hinegardner  jer...@hinegardner.org 

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

Re: [sqlite] A constraint bug?

[D. Richard Hipp]

On Oct 30, 2009, at 10:25 PM, Mick wrote:

> This is more FYI than needing it (as I have already worked around  
> it), but I
> have discovered that an IGNORE constraint on an insert, when one of  
> the
> fields in the constraint is NULL, will insert a duplicate record  
> into the
> database.

This is not a bug.  See, for example, the explanation on 
http://www.sqlite.org/cvstrac/tktview?tn=3463

>
> i.e.
>
> CREATE TABLE mytable (
> ID1 INTEGER NOT NULL,
> ID2 INTEGER NOT NULL,
> SomeText VARCHAR(100) COLLATE NOCASE,
> PRIMARY KEY (ID1, ID2, SomeText) ON CONFLICT IGNORE);
>
> INSERT INTO mytable VALUES (1, 1, NULL);
> INSERT INTO mytable VALUES (1, 1, NULL);
>
> Creates 2 records with the same primary key. It makes no difference  
> whether
> you use "OR IGNORE" in the insert statements, either.
>
> Cheers,
>
> Mick O'Neill
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

D. Richard Hipp
d...@hwaci.com



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

Re: [sqlite] A constraint bug?

[Simon Davies]

2009/10/31 Mick :
> This is more FYI than needing it (as I have already worked around it), but I
> have discovered that an IGNORE constraint on an insert, when one of the
> fields in the constraint is NULL, will insert a duplicate record into the
> database.
>
> i.e.
>
> CREATE TABLE mytable (
> ID1 INTEGER NOT NULL,
> ID2 INTEGER NOT NULL,
> SomeText VARCHAR(100) COLLATE NOCASE,
> PRIMARY KEY (ID1, ID2, SomeText) ON CONFLICT IGNORE);
>
> INSERT INTO mytable VALUES (1, 1, NULL);
> INSERT INTO mytable VALUES (1, 1, NULL);
>
> Creates 2 records with the same primary key. It makes no difference whether
> you use "OR IGNORE" in the insert statements, either.

NULL != NULL, so the primary key is not conflicting

>
> Cheers,
>
> Mick O'Neill
>

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

[sqlite] A constraint bug?

[Mick]

This is more FYI than needing it (as I have already worked around it), but I
have discovered that an IGNORE constraint on an insert, when one of the
fields in the constraint is NULL, will insert a duplicate record into the
database. 
 
i.e.
 
CREATE TABLE mytable (
ID1 INTEGER NOT NULL,
ID2 INTEGER NOT NULL,
SomeText VARCHAR(100) COLLATE NOCASE,
PRIMARY KEY (ID1, ID2, SomeText) ON CONFLICT IGNORE);
 
INSERT INTO mytable VALUES (1, 1, NULL);
INSERT INTO mytable VALUES (1, 1, NULL);
 
Creates 2 records with the same primary key. It makes no difference whether
you use "OR IGNORE" in the insert statements, either.
 
Cheers,
 
Mick O'Neill
 
 
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users