Infinite loop with recursive SQL query

Question

I can't seem to find the reason behind the infinite loop in this query, nor how to correct it.

Here is the context : I have a table called mergesWith with this description : mergesWith: information about neighboring seas. Note that in this relation, for every pair of neighboring seas (A,B), only one tuple is given – thus, the relation is not symmetric. sea1: a sea sea2: a sea.

I want to know every sea accessible from the Mediterranean Sea by navigating. I have opted for a recursive query using "with" :

With 
   acces(p,d) as (
       select sea1 as p, sea2 as d
       from MERGESWITH
       UNION ALL
       select a.p, case when mw.sea1=a.d 
                       then mw.sea2 
                       else mw.sea1 
                   end as d
       from acces a, MERGESWITH mw
       where a.d=mw.sea1 or a.d=mw.sea2)
select d
from acces
where p= 'Mediterranean Sea';

I think the cause is either the case when or the a.d=mw.sea1 or a.d=mw.sea2 that is not restrictive enough, but I can't seem to pinpoint why.

I get this error message : 32044. 00000 - "cycle detected while executing recursive WITH query" *Cause: A recursive WITH clause query produced a cycle and was stopped in order to avoid an infinite loop. *Action: Rewrite the recursive WITH query to stop the recursion or use the CYCLE clause.

Answer 1

The cycles are caused by the structure of your query, not by cycles in the data. You ask for the reason for cycling. That should be obvious: at the first iteration, one row of output has d = 'Aegean Sea'. At the second iteration, you will find a row with d = 'Mediterranean Sea', right? Can you now see how this will result in cycles?

Recursive queries have a cycle clause used exactly for this kind of problem. For some reason, even many users who learned the recursive with clause well, and use it all the time, seem unaware of the cycle clause (as well as the unrelated, but equally useful, search clause - used for ordering the output).

In your code, you need to make two changes. Add the cycle clause, and also in the outer query filter for non-cycle rows only. In the cycle clause, you can decide what to call the "cycle" column, and what values to give it. To make this look as similar to connect by queries as possible, I like to call the new column IS_CYCLE and to give it the values 0 (for no cycle) and 1 (for cycle). In the outer query below, add is_cycle to the select list to see what it adds to the recursive query.

Notice the position of the cycle clause: it comes right after the recursive with clause (in particular, after the closing parenthesis at the end of the recursive factored subquery).

with 
   acces(p,d) as (
       select sea1 as p, sea2 as d
       from   MERGESWITH
       UNION ALL
       select  a.p, case when mw.sea1=a.d 
                         then mw.sea2 
                         else mw.sea1 
                    end  as d
       from   acces a, MERGESWITH mw
       where  a.d=mw.sea1 or a.d=mw.sea2)
       cycle  d set is_cycle to 1 default 0           -- add this line
select d
from   acces
where  p= 'Mediterranean Sea'
  and  is_cycle = 0                                   -- and this line
;

Answer 2

Clearly, this would be data-dependent due to cycles in the data. I typically include a lev value when developing recursive CTEs. This makes it simpler to debug them.

So, try something like this:

with acces(p, d, lev) as (
      select sea1 as p, sea2 as d, 1 as lev
      from MERGESWITH
      union all
      select a.p,
             (case when mw.sea1 = a.d then mw.sea2 else mw.sea1 end) as d,
             lev + 1
       from acces a join
            MERGESWITH mw
            on a.d in (mw.sea1, mw.sea2)
       where lev < 5)
select d
from acces
where p = 'Mediterranean Sea';

If you find the reason but can't fix the code, ask a new question with sample data and desired results. A DB fiddle of some sort is also helpful.