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.

Its almost impossible to give you a solution without seeing the sample data. — Ankit Bajpai, Dec 08 '19 at 16:21

score 2 · Answer 1 · 2019-12-08T17:03:22.610

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
;

score 0 · Answer 2 · edited Dec 08 '19 at 14:26

0

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.

edited Dec 08 '19 at 14:26

sticky bit

36,626
12
31
42

answered Dec 08 '19 at 14:25

Gordon Linoff

1,242,037
58
646
786

I have tried the code that you wrote, but I still get the exact same error : 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. – CSstudZ Dec 08 '19 at 14:38
Obviously, even if this did work, the query is not equivalent to the original one. It doesn't find seas connected to the original one, but that require too many hops through other seas. The question still remains, though - why does Oracle still find a cycle in your query. The answer is that there is a **bug** in the implementation of the automatic detection of cycles; I wrote about it here: https://community.oracle.com/thread/4197768 – Dec 08 '19 at 16:58
Specifically: automatic cycle detection is based on the columns referenced in the `where` clause of the recursive member. But when ANSI joins are used, only the join conditions are inspected for "referenced columns" and other columns that appear only in the `where` clause are ignored. Again, this is a **bug**. I did not report the bug (and indeed I don't know if it has been reported) since I am not a paying customer of Oracle. The "workaround" is, of course, the **correct** way to detect cycles - the `cycle` clause. – Dec 08 '19 at 16:59

Infinite loop with recursive SQL query

2 Answers2