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

Re: [sqlite] obscure bug: duplicate rowid after insert or replace

[Simon Slavin]

On 2 May 2017, at 1:35am, Stephen Chrzanowski  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.



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

Re: [sqlite] 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  wrote:

> On 5/1/17, E.Pasma  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
> d...@sqlite.org
> ___
> 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] sqlite3_open_v2("",... schema name?

[Cezary H. Noweta]

Hello,

On 2017-04-28 12:16, Olivier Mascia wrote:

http://sqlite.org/c3ref/open.html says:



"If the filename is an empty string, then a private, temporary on-disk database will 
be created. This private database will be automatically deleted as soon as the database 
connection is closed."


The same behavior is when filename is NULL. In both cases a pager's 
filename is set to an empty string.



On such a successfully opened database, sqlite3_db_filename(db, "main") returns 
a NOT null pointer (to '\0').
The same behavior is seen when the opened database is an in-memory one (using 
":memory:" in the sqlite3_open_v2 call).


Indeed, look at the code (``sqlite3PagerFilename''):

return (nullIfMemDb && pPager->memDb) ? "" : pPager->zFilename;


Yet http://sqlite.org/c3ref/db_filename.html says:



"If there is no attached database N on the database connection D, or if database N 
is a temporary or in-memory database, then a NULL pointer is returned."


Actually, NULL means that there is no such schema (if provided N is 
NULL, then "main"'s filename is returned). An empty string (ptr to '\0') 
means that the schema is an on-disk temp, or an in-mem db, or ``temp'' 
schema.



So I would have expected (from the documentation) to get a NULL pointer, at 
least for the :memory: database. And for the on-disk but private temporary one, 
the documentation is mute about what to except from sqlite3_db_filename().


I agree, that a minor DOC improvement would be nice in this case.

-- best regards

Cezary H. Noweta
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] obscure bug: duplicate rowid after insert or replace

[Richard Hipp]

On 5/1/17, E.Pasma  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
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

Re: [sqlite] obscure bug: duplicate rowid after insert or replace

[Richard Hipp]

On 5/1/17, Richard Hipp  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
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] obscure bug: duplicate rowid after insert or replace

[R Smith]

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

Re: [sqlite] 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  wrote:

> On 5/1/17, E.Pasma  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
> d...@sqlite.org
> ___
> 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] obscure bug: duplicate rowid after insert or replace

[Richard Hipp]

On 5/1/17, E.Pasma  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
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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