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:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von Slava G Gesendet: Freitag, 06. September 2019 10:56 An: sqlite-users@mailinglists.sqlite.org 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 sqlite-users@mailinglists.sqlite.org 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 sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
