How can I handle cycles / infinite loops in recursive CTEs in MySQL

Question

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 ;

Answer 1

In this case UNION DISTINCT in CTE is enough for to prevent cycle:

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');
TABLE people;
create table friends (
  friend1 integer,
  FOREIGN KEY (friend1) references people (person_id),
  friend2 integer,
  FOREIGN KEY (friend2) references people (person_id),
  primary key (friend1, friend2)
  );
insert into friends (friend1, friend2) values
  (1,2),
  (2, 3),
  (3, 4),
  (5, 6),
  (2, 1), 
  (3, 2),
  (4, 3),
  (6, 5);
TABLE friends;

person_id	name
1	tom
2	dick
3	harry
4	susan
5	mary
6	jill

friend1	friend2
2	1
1	2
3	2
2	3
4	3
3	4
6	5
5	6

WITH RECURSIVE
cte AS (
  SELECT friend1, friend2
  FROM friends
--  WHERE friend1 = 1
  UNION DISTINCT
  SELECT cte.friend1, friends.friend2
  FROM cte
  JOIN friends ON cte.friend2 = friends.friend1
  )
SELECT DISTINCT t1.*, t2.*
FROM cte
JOIN people t1 ON t1.person_id = cte.friend1
JOIN people t2 ON t2.person_id = cte.friend2
WHERE cte.friend1 <> cte.friend2
ORDER BY 1,3;

person_id	name	person_id	name
1	tom	2	dick
1	tom	3	harry
1	tom	4	susan
2	dick	1	tom
2	dick	3	harry
2	dick	4	susan
3	harry	1	tom
3	harry	2	dick
3	harry	4	susan
4	susan	1	tom
4	susan	2	dick
4	susan	3	harry
5	mary	6	jill
6	jill	5	mary

fiddle

if you need the friendship data for definite user or for a list of users then edit/uncomment the condition in base query of the CTE.

If you need to use this query as a view then add CREATE VIEW collect_friends AS at the beginning.