Quantcast

obscure bug: duplicate rowid after insert or replace

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
10 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

obscure bug: duplicate rowid after insert or replace

E.Pasma
Hello, I have a duplicate rowid in a 3.16.2 database and this is  
essentially produced as follows:

CREATE TABLE t(i INTEGER PRIMARY KEY, a TEXT)
;
INSERT INTO t VALUES
     (NULL, 'generates row 1')
;
REPLACE INTO t VALUES
     (NULL, 'generates row 2'),
     (1, 'replaces row 1')
;

It is alright after changing the order in the multiple values. Hope  
I'm not mistaken.Thanks, E. Pasma

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

Re: obscure bug: duplicate rowid after insert or replace

Richard Hipp-3
On 5/1/17, E.Pasma <[hidden email]> wrote:
> Hello, I have a duplicate rowid in a 3.16.2 database and this is
> essentially produced as follows:
>
> CREATE TABLE t(i INTEGER PRIMARY KEY, a TEXT);;
> INSERT INTO t VALUES
>      (NULL, 'generates row 1');
> REPLACE INTO t VALUES
>      (NULL, 'generates row 2'),
>      (1, 'replaces row 1');

What were you expecting this to do?

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

Re: obscure bug: duplicate rowid after insert or replace

Stephen Chrzanowski
I'm not the OP, but I would have expected a fail to replace.

sqlite> create table t(i integer primary key, a text);
sqlite> insert into t values (null,'generates row 1');
sqlite> replace into t values (null,'generates row 2'),(1,'replaces row 1');
sqlite> select * from t;
1|generates row 1
1|replaces row 1

If primary keys are to be unique, this isn't valid.  But I've not looked at
the documentation for specs.

On Mon, May 1, 2017 at 12:41 PM, Richard Hipp <[hidden email]> wrote:

> On 5/1/17, E.Pasma <[hidden email]> wrote:
> > Hello, I have a duplicate rowid in a 3.16.2 database and this is
> > essentially produced as follows:
> >
> > CREATE TABLE t(i INTEGER PRIMARY KEY, a TEXT);;
> > INSERT INTO t VALUES
> >      (NULL, 'generates row 1');
> > REPLACE INTO t VALUES
> >      (NULL, 'generates row 2'),
> >      (1, 'replaces row 1');
>
> What were you expecting this to do?
>
> --
> D. Richard Hipp
> [hidden email]
> _______________________________________________
> 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
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: obscure bug: duplicate rowid after insert or replace

R Smith
In reply to this post by Richard Hipp-3
Well, I for one expected that script to produce on completion a table t
with two rows like this:

1, 'replaces tow 1'
2, 'generates row 2'

But the actual script produces a confusing table with a duplicate
Primary Key (Row-id alias no less), like this:
(Using SQLite 3.17.0)

CREATE TABLE t(i INTEGER PRIMARY KEY, a TEXT);

INSERT INTO t VALUES
     (NULL, 'generates row 1')
;

SELECT * FROM t;

   --       i      | a
   -- ------------ | -----------------
   --       1      | generates row 1


REPLACE INTO t VALUES
     (NULL, 'generates row 2'),
     (1, 'replaces row 1')
;


SELECT * FROM t;

   --  i  | a
   -- --- | -----------------
   --  1  | generates row 1
   --  1  | replaces row 1


Surely that ain't right? Or am I missing something?


On 2017/05/01 6:41 PM, Richard Hipp wrote:

> On 5/1/17, E.Pasma <[hidden email]> wrote:
>> Hello, I have a duplicate rowid in a 3.16.2 database and this is
>> essentially produced as follows:
>>
>> CREATE TABLE t(i INTEGER PRIMARY KEY, a TEXT);;
>> INSERT INTO t VALUES
>>       (NULL, 'generates row 1');
>> REPLACE INTO t VALUES
>>       (NULL, 'generates row 2'),
>>       (1, 'replaces row 1');
> What were you expecting this to do?
>

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

Re: obscure bug: duplicate rowid after insert or replace

Richard Hipp-3
In reply to this post by Richard Hipp-3
On 5/1/17, Richard Hipp <[hidden email]> wrote:
>
> What were you expecting this to do?
>

Never mind.  After actually running the test case, I see that it gives
an assertion fault.  We're working on it.

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

Re: obscure bug: duplicate rowid after insert or replace

R Smith
I've managed to reproduce this down to SLIte 3.8.1 so far... going lower
will take more time, but I will get there.

Also, if it helps, pragma integrity_check succeeds with Ok.

Multiple inserts of the same type keeps duplicating, and keeps working.

REINDEX, ANALYZE and VACUUM all works without a hitch.

Foreign keys link wrong (as if there could be a "right" in this
situation) but they do not error out and queries still work, albeit I am
able to make some nonsense outputs using joins after the insert.


HTH, good luck!
Ryan


On 2017/05/01 7:07 PM, Richard Hipp wrote:
> On 5/1/17, Richard Hipp <[hidden email]> wrote:
>> What were you expecting this to do?
>>
> Never mind.  After actually running the test case, I see that it gives
> an assertion fault.  We're working on it.
>

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

Re: obscure bug: duplicate rowid after insert or replace

Richard Hipp-3
In reply to this post by E.Pasma
On 5/1/17, E.Pasma <[hidden email]> wrote:

> Hello, I have a duplicate rowid in a 3.16.2 database and this is
> essentially produced as follows:
>
> CREATE TABLE t(i INTEGER PRIMARY KEY, a TEXT)
> ;
> INSERT INTO t VALUES
>      (NULL, 'generates row 1')
> ;
> REPLACE INTO t VALUES
>      (NULL, 'generates row 2'),
>      (1, 'replaces row 1')
> ;
>

Thanks for the bug report.  This should now be fixed on trunk.  The
ticket is https://www.sqlite.org/src/info/f68dc596c4
--
D. Richard Hipp
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: obscure bug: duplicate rowid after insert or replace

Stephen Chrzanowski
I was just looking at the ticket, and was wondering if that actually is the
right answer?

Does REPLACE do an actual INSERT regardless if the PK exists?  Doesn't it
search and and update?  If NULL is the key, wouldn't it do a search for PK
of NULL (Doesn't exist) and replace that, essentially becoming a NO-OP?  I
can see how "generates row 1" becomes "replaces row 1" but, I wouldn't
expect the REPLACE keyword to do an insert.  Might be just a linguistic
meaning thing to me, but if thats as it operates, looks fine to me then.

On Mon, May 1, 2017 at 2:50 PM, Richard Hipp <[hidden email]> wrote:

> On 5/1/17, E.Pasma <[hidden email]> wrote:
> > Hello, I have a duplicate rowid in a 3.16.2 database and this is
> > essentially produced as follows:
> >
> > CREATE TABLE t(i INTEGER PRIMARY KEY, a TEXT)
> > ;
> > INSERT INTO t VALUES
> >      (NULL, 'generates row 1')
> > ;
> > REPLACE INTO t VALUES
> >      (NULL, 'generates row 2'),
> >      (1, 'replaces row 1')
> > ;
> >
>
> Thanks for the bug report.  This should now be fixed on trunk.  The
> ticket is https://www.sqlite.org/src/info/f68dc596c4
> --
> D. Richard Hipp
> [hidden email]
> _______________________________________________
> 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
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: obscure bug: duplicate rowid after insert or replace

Simon Slavin-3

On 2 May 2017, at 1:35am, Stephen Chrzanowski <[hidden email]> wrote:

> I was just looking at the ticket, and was wondering if that actually is the
> right answer?
>
> Does REPLACE do an actual INSERT regardless if the PK exists?  Doesn't it
> search and and update?  If NULL is the key, wouldn't it do a search for PK
> of NULL (Doesn't exist) and replace that, essentially becoming a NO-OP?

REPLACE is short for INSERT OR REPLACE.

INSERT OR REPLACE, where the new record is a duplicate, does a DELETE and then an INSERT.  In other words, there’s a short time when the old record has been deleted but the new record hasn’t been inserted yet.  The result is not the same as if SQLite did an UPDATE instead.

An additional complication is that for some purposes SQLite considers that two different NULLs are not the same value.  But not all purposes.  So you have to know exactly what you’re doing if you’re messing with NULL values in primary keys.

<https://sqlite.org/nulls.html>

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
|  
Report Content as Inappropriate

Re: obscure bug: duplicate rowid after insert or replace

Keith Medcalf

> INSERT OR REPLACE, where the new record is a duplicate, does a DELETE and
> then an INSERT.  In other words, there’s a short time when the old record
> has been deleted but the new record hasn’t been inserted yet.  The result
> is not the same as if SQLite did an UPDATE instead.

Since the statement is executed inside a transaction thusly:

BEGIN;
DELETE ...
INSERT ...
COMMIT;

an external viewer can see no difference between an UPDATE or a REPLACE with the single exception that if you are enforcing foreign keys and there is a foreign key dependancy (with cascade) on the row being deleted, all the children will be deleted also.  

sqlite> create table x(id integer primary key, desc text collate nocase);
sqlite> create table y(fk integer references x on delete cascade, descy text collate nocase);
sqlite> insert into x values (1, 'new 1');
sqlite> insert into y values (1, 'depends on new 1');
sqlite> replace into x values (1, 'replace 1');
sqlite> select * from x;
1|replace 1
sqlite> select * from y;
sqlite>

If no on delete constraint were specified, you get this:

sqlite> create table x(id integer primary key, desc text collate nocase);
sqlite> insert into x values (1, 'new 1');
sqlite> create table y(fk integer references x, descy text collate nocase);
sqlite> insert into y values (1, 'depends on new 1');
sqlite> replace into x values (1, 'replace 1');
sqlite> select * from y;
1|depends on new 1
sqlite> select * from x;
1|replace 1

However, if you had ON DELETE RESTRICT, you would get this:

sqlite> create table x(id integer primary key, desc text collate nocase);
sqlite> insert into x values (1, 'new 1');
sqlite> create table y(fk integer references x on delete restrict, descy text collate nocase);
sqlite> insert into y values (1, 'depends on new 1');
sqlite> replace into x values (1, 'replace 1');
Error: FOREIGN KEY constraint failed

Of course, the above only applies if the "duplicate" is the ROWID.  If it is some other constraint your results will vary.  

However, the fact is that on EVERY OTHER connection, the REPLACE is atomic and from the perspective of another connection there is NO INSTANT IN TIME (no matter how small) at which the row does not exist.
 
> An additional complication is that for some purposes SQLite considers that
> two different NULLs are not the same value.  But not all purposes.  So you
> have to know exactly what you’re doing if you’re messing with NULL values
> in primary keys.

This does not apply to ROWID's (which cannot be NULL).




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