Cascaded delete unexpectedly triggered by upsert

classic Classic list List threaded Threaded
8 messages Options
Reply | Threaded
Open this post in threaded view
|

Cascaded delete unexpectedly triggered by upsert

Michael Kappert
Hi,

as this is my first posting to this list, let me first say thank you for
providing such a great library. It's fun to work with it. I'm intending
to use SQLite in a game engine.

I've encountered some unexpected behavior of foreign keys w.r.t an upsert:
When an entry of the parent table is 'touched' but not deleted or
modified, the child table entry is nevertheless deleted.
Although the foreign key is declared 'ON DELETE CASCADE ON UPDATE
RESTRICT', I did not expect the child entry to be deleted, because the
parent entry was not deleted.

Here is a script that shows the behavior:
-------------------------------------------------------------------------
PRAGMA foreign_keys = ON;

-- Create tables
CREATE TABLE t1 (id char(36), name varchar(40), CONSTRAINT pk_t1 PRIMARY
KEY (id));
CREATE TABLE t2 (id char(36), t1_id char(36), data char(36), CONSTRAINT
pk_t2 PRIMARY KEY (id), CONSTRAINT fk_t1_id FOREIGN KEY (t1_id)
REFERENCES t1(id) ON DELETE CASCADE ON UPDATE RESTRICT DEFERRABLE
INITIALLY DEFERRED);

-- Create table content
INSERT INTO T1 (ID, NAME) VALUES ('A', 'line 1');
INSERT INTO T1 (ID, NAME) VALUES ('B', 'line 2');
INSERT INTO T2 (ID, T1_ID, DATA) VALUES ('T2-A', 'A', 'abc');
INSERT INTO T2 (ID, T1_ID, DATA) VALUES ('T2-B', 'B', 'xyz');

-- Modify one parent entry of foreign key fk_t1_id
REPLACE INTO T1 (ID, NAME) VALUES ('A', 'line 1-new');

-- Although the parent field was not modified, a cascading delete is
triggered:
SELECT *  FROM T2;

-- The cascading delete happens even if the upsert is a no-op:
REPLACE INTO T1 (ID, NAME) VALUES ('B', 'line 2');
SELECT *  FROM T2;
------------------------------------------------------------------------

If I understand correctly, the upsert should behave like UPDATE in the
examples above, but it behaves like a DELETE followed by INSERT instead?


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

Re: Cascaded delete unexpectedly triggered by upsert

Simon Slavin-3
On 8 Jan 2020, at 12:00am, Michael Kappert <[hidden email]> wrote:

> REPLACE INTO

REPLACE INTO is an alias for INSERT OR REPLACE.  So you should assume that the command will do either an INSERT or a REPLACE.

See the notes about REPLACE on this page:

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

Is the behaviour you're seeing consistent with that documentation ?
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Cascaded delete unexpectedly triggered by upsert

Michael Kappert
On 8.01.20 01:04, Simon Slavin wrote:

> On 8 Jan 2020, at 12:00am, Michael Kappert <[hidden email]> wrote:
>
>> REPLACE INTO
>
> REPLACE INTO is an alias for INSERT OR REPLACE.  So you should assume that the command will do either an INSERT or a REPLACE.
>
> See the notes about REPLACE on this page:
>
> <https://sqlite.org/lang_conflict.html>
>
> Is the behaviour you're seeing consistent with that documentation ?

Argh, I somehow managed to skip the pointer to that page when reading
this <https://sqlite.org/lang_insert.html>.
So I need to provide an upsert clause to make the replace behave as an
UPDATE.



> _______________________________________________
> 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
|

Re: Cascaded delete unexpectedly triggered by upsert

R Smith-2
In reply to this post by Michael Kappert
On 2020/01/08 2:00 AM, Michael Kappert wrote:
> -- Modify one parent entry of foreign key fk_t1_id
> REPLACE INTO T1 (ID, NAME) VALUES ('A', 'line 1-new');
> ------------------------------------------------------------------------
>
> If I understand correctly, the upsert should behave like UPDATE in the
> examples above, but it behaves like a DELETE followed by INSERT instead?


You say "upsert" but the operation in question is not an "upsert", it's
a REPLACE which is really a DELETE followed by an INSERT, which is why
you are seeing the behaviour you noted.

To do an actual UPSERT in SQLite, use the UPSERT method, which will look
somewhat like this for your example:

INSERT INTO t1(id, name) VALUES ('A', 'line 1-new')
   ON CONFLICT (id) DO UPDATE SET name = excluded.name

;


HTH and Good luck,
Ryan
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Cascaded delete unexpectedly triggered by upsert

Simon Slavin-3
I advise you avoid the idea of UPSERT when dealing with SQLite (or better still, all SQL).  It is rarely implemented as a single operation, and you can get unexpected results with triggers and foreign key children.

Think of your operation as a combinations of INSERT, DELETE and UPDATE and you will be able to correctly predict its effects.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Cascaded delete unexpectedly triggered by upsert

R Smith-2
On 2020/01/08 1:10 PM, Simon Slavin wrote:
> I advise you avoid the idea of UPSERT when dealing with SQLite (or better still, all SQL).  It is rarely implemented as a single operation, and you can get unexpected results with triggers and foreign key children.

I advise you to avoid the idea of driving a Bus to work (or better
still, at all).  It is rarely easy to navigate narrow lanes and you can
get unexpected results when trying to park it at the mall.

Rather use a bicycle.

If however your objective is to take 100 other people to work, you'll
find the bus is a godsend - much like UPSERT.

<tongue-removed-from-cheek>

Simon is quite correct, using a function without proper understanding,
results can be somewhat unexpected.  Even with proper understanding of
the SQL, it can still be unexpected, considering that different engines
implement it differently. A shining example of this is "REPLACE" -
almost everyone who posted about it here misunderstood what it really
does[1], often causing the exact problems Simon warns about.

That said, I can't agree with advising against UPSERT.

There is no unknown magic going on with UPSERT (or any other fully
supported SQL feature in SQLite), if you do need it, do read up properly
on it (specific to the platform you will use it on) and it will work as
advertised. (And in the rare case it doesn't, a bug report here usually
remedies that in about a day).


Cheers,
Ryan

[1] - They often use REPLACE having come from a MySQL background, and
then they often learn that even in MySQL it doesn't work as they had
imagined.


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

Re: Cascaded delete unexpectedly triggered by upsert

Richard Hipp-3
In reply to this post by Michael Kappert
On 1/7/20, Michael Kappert <[hidden email]> wrote:
>
> If I understand correctly, the upsert should behave like UPDATE in the
> examples above, but it behaves like a DELETE followed by INSERT instead?
>

REPLACE and UPSERT are different things.  See
https://www.sqlite.org/lang_conflict.html for information about
REPLACE and https://www.sqlite.org/lang_UPSERT.html for information
about UPSERT.

REPLACE works by first DELETE-ing any rows that would cause a conflict
and then doing the INSERT.

UPDATE works by converting the INSERT into an UPDATE.
--
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
|

Re: Cascaded delete unexpectedly triggered by upsert

Julian Dohmen
This does look very useful - I’ve often thought of the INSERT/EPLACE style but the implicit DELETE [when REPLACE occurs] was a barrier - it causes [as I understand it] CASCADE DELETE to be in effect.
It seems that this UPSERT style does not cause that effect.

> On Jan 8, 2020, at 5:22 AM, Richard Hipp <[hidden email]> wrote:
>
> On 1/7/20, Michael Kappert <[hidden email]> wrote:
>>
>> If I understand correctly, the upsert should behave like UPDATE in the
>> examples above, but it behaves like a DELETE followed by INSERT instead?
>>
>
> REPLACE and UPSERT are different things.  See
> https://www.sqlite.org/lang_conflict.html for information about
> REPLACE and https://www.sqlite.org/lang_UPSERT.html for information
> about UPSERT.
>
> REPLACE works by first DELETE-ing any rows that would cause a conflict
> and then doing the INSERT.
>
> UPDATE works by converting the INSERT into an UPDATE.
> --
> 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