I always followed the advice on https://sqlite.org/lang_with.html and use UNION ALL in the compound select statement. This is why cycles trigger infinite looping. In the case of my edges table, it does not make sense to have cycles so my goal is to develop INSERT and UPDATE triggers that prevent this possibility.
On 21 December 2017 at 12:11, Lifepillar <lifepil...@lifepillar.me> wrote: > On 20/12/2017 22:31, Shane Dev wrote: > >> Hello, >> >> I have an edges table - >> >> sqlite> .sch edges >> CREATE TABLE edges(parent, child); >> >> sqlite> select * from edges; >> parent child >> 1 2 >> 1 3 >> 2 4 >> 3 1 >> 4 5 >> 5 2 >> >> Here we have two cycles - >> >> 1) 1 => 3 => 1 (length 1) >> 2) 2 => 4 => 5 => 2 (length 3) >> >> Cycles cause recursive common table expression queries to become infinite >> loops. >> > Maybe you could show an example of such queries? This: > > with recursive Visit(node) as ( > select parent from Edges where parent = 1 > union > select child from Edges join Visit on parent = node > ) > select node from Visit; > > returns a finite result (note that use of 'union' rather than 'union > all'). > > Life. > > _______________________________________________ > 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
