Re: [sqlite] insert or replace on PK and UNIQUE INDEX

[James K. Lowden]

On Tue, 12 Jul 2016 15:35:20 +0200
Dominique Devienne  wrote:

> Now we know OR REPLACE is never what we want (in our use cases),

Besides being nonstandard, REPLACE is not atomic.  I haven't seen the
use case that benefits from those characteristics but, judging
from this list, you're not the first person bitten by them..  

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

Re: [sqlite] insert or replace on PK and UNIQUE INDEX

[R Smith]

On 2016/07/12 3:55 PM, R Smith wrote:



On 2016/07/12 2:12 PM, Dominique Devienne wrote:
In the session above, we can see that an insert or replace w/o an id 
(the

PK) value,
results in the id changing in the table, which is not what we'd like.

etc.

By the way, another important problem with this is maintaining foreign 
keys linked to ID fields. Best not to do it, or if you do, ALWAYS 
specify the new ID yourself, never rely on the DB engine's feelings of 
what the  ID should be. (As I've mentioned many times before). :)


Cheers,
Ryan

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

Re: [sqlite] insert or replace on PK and UNIQUE INDEX

[R Smith]

On 2016/07/12 2:12 PM, Dominique Devienne wrote:

In the session above, we can see that an insert or replace w/o an id (the
PK) value,
results in the id changing in the table, which is not what we'd like.


If you ask any DB engine to INSERT, and you do not specify the 
autoinc/pk/identity ID, then a new ID will always be used. By specifying 
"OR REPLACE" in SQLite it /replaces/ the offending row by deleting it 
and inserting the new version of the row as you asked (it never 
overwrites /some/ values in the row) - however, if you fail to specify 
the ID for the row, then any newly inserted row (like any other inserted 
row) MUST get a new row ID.




The (incorrect on our part) thinking was that the UNIQUE index would also
be used
to recognize that the row already existed, and the id to remain the same
(In reality in
this case the natural key checked by the UNIQUE index is passed in
unchanged to the
insert or replace, and it's other columns that change), but apparently the
autoincrement
is first applied, and then the UNIQUE index is apparently still used,
resulting in this
unwanted ID change.


The best way to think about this or remember it, is that SQLite (or any 
DB engine I think) will never adjust the data within a row unless asked 
to UPDATE. When you ask it to INSERT or REPLACE or any other such a 
thing, an entire row will always be affected and handled like a new row 
should you omit any fields. (This is usually documented and expected, 
even by the standard).


You kind-of expected the INSERT OR REPLACE to be more of an INSERT OR 
UPDATE (which is the commonly-referred to UPSERT operation) and not 
really supported natively by many - unless you count MERGE (which does 
quite a bit more).


Interestingly, as is evident from this list - This assumption is 
extremely common and happens all the time, even if it gets discussed 
here no end... (just search the list for "UPSERT"), so don't feel alone, 
near everyone has this wrong initially.



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

Re: [sqlite] insert or replace on PK and UNIQUE INDEX

[Dominique Devienne]

Thank you all, Clemens, Richard, Simon, Hick.

Now we know OR REPLACE is never what we want (in our use cases),
and will rewrite into two statements, as Clemens and Simon indicated. --DD
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] insert or replace on PK and UNIQUE INDEX

[Hick Gunter]

The conflict action "replace" looks at ALL the PRIMARY KEY and UNIQUE 
constraints (express or implied) and deletes ALL the existing rows that prevent 
the candidate row from being inserted. As in "I don't care what it takes, I 
want THIS row to be in the table."

The other conflict actions just reflect the "level of panic" you associate with 
not being able to insert the row, from "don't care" (IGNORE) to "bloody hell" 
(ROLLBACK).

-Ursprüngliche Nachricht-
Von: sqlite-users-boun...@mailinglists.sqlite.org 
[mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von Dominique 
Devienne
Gesendet: Dienstag, 12. Juli 2016 14:39
An: SQLite mailing list 
Betreff: Re: [sqlite] insert or replace on PK and UNIQUE INDEX

On Tue, Jul 12, 2016 at 2:31 PM, Richard Hipp  wrote:

> On 7/12/16, Dominique Devienne  wrote:
> >
> > Is that normal or expected?
>
> The operation of REPLACE is defined here:
>
> https://www.sqlite.org/mark/lang_conflict.html?the+REPLACE+al*+current
> +row#mark


So the plural in "deletes pre-existing rows" explain that in my second example, 
with both the PK and NK where each point to different rows, both rows are first 
deleted, then the new row is inserted?

I've never used "or replace" (this question comes from a different usage of 
SQLite than mine), and I guess they didn't read or understand the implications 
of the doc either.

Thanks. Makes sense now, even though I still find it surprising a bit.
Not what I'd naively assume without the benefit of reading the doc :) --DD 
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


___
 Gunter Hick
Software Engineer
Scientific Games International GmbH
FN 157284 a, HG Wien
Klitschgasse 2-4, A-1130 Vienna, Austria
Tel: +43 1 80100 0
E-Mail: h...@scigames.at

This communication (including any attachments) is intended for the use of the 
intended recipient(s) only and may contain information that is confidential, 
privileged or legally protected. Any unauthorized use or dissemination of this 
communication is strictly prohibited. If you have received this communication 
in error, please immediately notify the sender by return e-mail message and 
delete all copies of the original communication. Thank you for your cooperation.


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

Re: [sqlite] insert or replace on PK and UNIQUE INDEX

[Simon Slavin]

On 12 Jul 2016, at 1:39pm, Dominique Devienne  wrote:

> So the plural in "deletes pre-existing rows" explain that in my second
> example,
> with both the PK and NK where each point to different rows, both rows are
> first
> deleted, then the new row is inserted?

Correct.  There is a common assumption that INSERT OR REPLACE is just a name, 
and that behind the scenes the SQL engine just makes some complicated changes 
inside the database.  But in SQLite it really does do a number of DELETEs and 
then an INSERT.

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

Re: [sqlite] insert or replace on PK and UNIQUE INDEX

[Dominique Devienne]

On Tue, Jul 12, 2016 at 2:31 PM, Richard Hipp  wrote:

> On 7/12/16, Dominique Devienne  wrote:
> >
> > Is that normal or expected?
>
> The operation of REPLACE is defined here:
>
> https://www.sqlite.org/mark/lang_conflict.html?the+REPLACE+al*+current+row#mark


So the plural in "deletes pre-existing rows" explain that in my second
example,
with both the PK and NK where each point to different rows, both rows are
first
deleted, then the new row is inserted?

I've never used "or replace" (this question comes from a different usage of
SQLite than mine),
and I guess they didn't read or understand the implications of the doc
either.

Thanks. Makes sense now, even though I still find it surprising a bit.
Not what I'd naively assume without the benefit of reading the doc :) --DD
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] insert or replace on PK and UNIQUE INDEX

[Simon Slavin]

On 12 Jul 2016, at 1:26pm, Clemens Ladisch  wrote:

> An INSERT OR UPDATE (or MERGE) statement does not exist in SQLite.
> 
> Just try the UPDATE, and if the number of affected rows is zero, do the
> INSERT.

I prefer to do

INSERT OR IGNORE ...
UPDATE ...

That way you do not have to count the affected rows.

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

Re: [sqlite] insert or replace on PK and UNIQUE INDEX

[Richard Hipp]

On 7/12/16, Dominique Devienne  wrote:
>
> Is that normal or expected?

The operation of REPLACE is defined here:
https://www.sqlite.org/mark/lang_conflict.html?the+REPLACE+al*+current+row#mark

-- 
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] insert or replace on PK and UNIQUE INDEX

[Clemens Ladisch]

Dominique Devienne wrote:
> sqlite> select * from t;
> 1|one bis
> 2|two
> sqlite> insert or replace into t (name) values ('one bis');
> sqlite> select * from t;
> 2|two
> 3|one bis
>
> In the session above, we can see that an insert or replace w/o an id (the PK) 
> value,
> results in the id changing in the table, which is not what we'd like.
>
> Is that normal or expected?

REPLACE always deletes the old row, if it exists.  This is documented.

> Is there a SQL way (one statement ideally) to achieve what we'd like?

An INSERT OR UPDATE (or MERGE) statement does not exist in SQLite.

Just try the UPDATE, and if the number of affected rows is zero, do the
INSERT.


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

Re: [sqlite] insert or replace on PK and UNIQUE INDEX

[Dominique Devienne]

On Tue, Jul 12, 2016 at 2:12 PM, Dominique Devienne 
wrote:

> C:\Users\ddevienne>sqlite3
> SQLite version 3.10.2 2016-01-20 15:27:19
> Enter ".help" for usage hints.
> Connected to a transient in-memory database.
> Use ".open FILENAME" to reopen on a persistent database.
> sqlite> create table t (id integer primary key autoincrement, name text
> unique);
> sqlite> insert into t values (1, 'one'), (2, 'two');
> sqlite> select * from t;
> 1|one
> 2|two
> sqlite> insert into t values (1, 'one bis');
> Error: UNIQUE constraint failed: t.id
> sqlite> insert or replace into t values (1, 'one bis');
> sqlite> select * from t;
> 1|one bis
> 2|two
> sqlite> insert or replace into t (name) values ('one bis');
> sqlite> select * from t;
> 2|two
> 3|one bis
>
> In the session above, we can see that an insert or replace w/o an id (the
> PK) value,
> results in the id changing in the table, which is not what we'd like.
>
> The (incorrect on our part) thinking was that the UNIQUE index would also
> be used
> to recognize that the row already existed, and the id to remain the same
> (In reality in
> this case the natural key checked by the UNIQUE index is passed in
> unchanged to the
> insert or replace, and it's other columns that change), but apparently the
> autoincrement
> is first applied, and then the UNIQUE index is apparently still used,
> resulting in this
> unwanted ID change.
>
> Is that normal or expected?
> Is there a SQL way (one statement ideally) to achieve what we'd like?
>

sqlite> insert or replace into t values (3, 'two');
sqlite> select * from t;
3|two

Worse, and that's even more unexpected to me, I tried doing the insert or
replace above,
but specifying the PK the time, *and* explicitly trying to conflict on the
UNIQUE index,
and to me great surprise (horror?), that actually deleted one row instead
of failing!

That an insert or replace can result in fewer rows (i.e. akin to a delete)
is shocking to me.
Can someone please shed some light on this too? Thanks again, --DD
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users