Endless loop in update recursive query with UNION ALL

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

Endless loop in update recursive query with UNION ALL

Slava G
I have query that enters into endless loop in update recursive query with
UNION ALL, but when I remove ALL from the UNION it's works fine :
WITH recursive parentitems(itemid) AS (
VALUES("58f6fb3e-40a0-4b32-90a1-37945c44a649_c476ed54-217a-432a-9857-4fbb1eb5bc7a")

*UNION ALL *
SELECT snapshotdata.itemid
FROM   snapshotdata,
       parentitems
WHERE  folder = parentitems.itemid
AND    ifnull(deleteddate ,99999999999999) = 99999999999999 )
UPDATE snapshotdata
SET    deleteddate = 20190903142833
WHERE  itemid IN parentitems
AND    backupdate < 20190903142833
AND    ifnull(deleteddate,99999999999999) = 99999999999999

As far as I understand ALL in UNION should provide better performance, but
somehow it enters into endless loop and eat all computer resources.

Thanks
_______________________________________________
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: [EXTERNAL] Endless loop in update recursive query with UNION ALL

Hick Gunter
Does your "parent" relationship contain (at least one) loop(s)? UNION will break the loop by eliminating already visited rows, whereas UNION ALL will run faster precisely because it does not keep track of the visited rows.

-----Urspr√ľngliche Nachricht-----
Von: sqlite-users [mailto:[hidden email]] Im Auftrag von Slava G
Gesendet: Freitag, 06. September 2019 10:56
An: [hidden email]
Betreff: [EXTERNAL] [sqlite] Endless loop in update recursive query with UNION ALL

I have query that enters into endless loop in update recursive query with UNION ALL, but when I remove ALL from the UNION it's works fine :
WITH recursive parentitems(itemid) AS (
VALUES("58f6fb3e-40a0-4b32-90a1-37945c44a649_c476ed54-217a-432a-9857-4fbb1eb5bc7a")

*UNION ALL *
SELECT snapshotdata.itemid
FROM   snapshotdata,
       parentitems
WHERE  folder = parentitems.itemid
AND    ifnull(deleteddate ,99999999999999) = 99999999999999 )
UPDATE snapshotdata
SET    deleteddate = 20190903142833
WHERE  itemid IN parentitems
AND    backupdate < 20190903142833
AND    ifnull(deleteddate,99999999999999) = 99999999999999

As far as I understand ALL in UNION should provide better performance, but somehow it enters into endless loop and eat all computer resources.

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


___________________________________________
 Gunter Hick | Software Engineer | Scientific Games International GmbH | Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users