Lifepillar 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.
Brilliant. Now I see the difference between UNION and UNION ALL in
recursion. It is documented as below. Although it needs careful
reading to understand that UNION effectively eliminates loops.
https://www.sqlite.org/lang_with.html#recursivecte
If a UNION operator connects the initial-select with the recursive-
select, then only add rows to the queue if no identical row has been
previously added to the queue. Repeated rows are discarded before
being added to the queue even if the repeated rows have already been
extracted from the queue by the recursion step. If the operator is
UNION ALL, then all rows generated by both the initial-select and the
recursive-select are always added to the queue even if they are
repeats. When determining if a row is repeated, NULL values compare
equal to one another and not equal to any other value.
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
