Cycles in graphs lead to infinite loops in CTEs.
Dealing with them in Postgres is straightforward.
Since 8.0 MySQL also allows for CTEs. How I can detect cycles and infinite loops in MySQL CTEs?
The goal is not to interrupt the query after 1000 or whatever number of iterations, but to actually handle it in the code (for instance by collecting in an array the list of visited nodes and having an inequality condition to avoid loops).
Or are there any "in-built" options to deal with this, like
cycle COLNAME in Postgres CTEs?
My current code looks something like this
with recursive circle as ( select friend2, name2, 0 as depth from my_view1 where friend1 = 1 union select m.friend2, m.name2, c.depth+1 from my_view1 m inner join circle c on c.friend2 = m.friend1) select * from circle where circle.depth < 2;
The underlying tables can be created with:
create table people (person_id integer primary key, name varchar(20) not null); insert into people (person_id, name) values (1, 'tom'), (2, 'dick'), (3, 'harry'), (4, 'susan'), (5, 'mary'), (6, 'jill'); create table friends (friend1 integer references people (person_id), friend2 integer references people (person_id), primary key (friend1, friend2)); insert into friends (friend1, friend2) values (1,2), (2, 3), (3, 4), (5, 6); insert into friends (friend1, friend2) values (2,1), (3,2), (4,3), (6,5); create view my_view1 as select f.friend1, p.name as name1, f.friend2, p1.name as name2 from friends f join people p on p.person_id = f.friend1 join people p1 on p1.person_id = f.friend2 ;