Search code examples
oraclecyclerecursive-querycross-join

Weird Oracle behaviour - cross join in recursive CTE works with comma but not with cross join clause

Suppose following simple recursive query r to list several numbers. When recursive part of query is cross joined with unnecessary 1-row table using old way with comma separation, everything works fine (dbfiddle):

with r (x) as (
  select 1 as x from dual
  union all
  select x + 1 from r,dual where x < 5
)
select * from r

If I change comma to cross join clause, I get ORA-32044: cycle detected while executing recursive WITH query error (dbfiddle): 

with r (x) as (
  select 1 as x from dual
  union all
  select x + 1 from r cross join dual where x < 5
)
select * from r;

Reproduced on Oracle 11g (my project db) and Oracle 18 (dbfiddle). The case is minimized, originally it is hierarchical query on graph data where auxiliary relation with 2 rows representing edge endpoints was cross joined in such a way.

I can (unwillingly) live with first syntax though I have bad feeling of dark undocumented corner or Oracle to build query upon. I didn't find any reasoning for such behaviour nor track of bug. Do anybody knows any? Thanks.

Solution

  • The closest explanation I've found until now is in this thread on MOS. In the thread, OP effectively joins tables with inner join with join condition in where clause. In from clause there are just two tables connected with either cross join or comma. My example differs in aspect the dual table is not involved in any join condition, which weakens some reasonings in thread such as anything involving 2 different tables is recognized as a join condition.

    Anyway, it seems Oracle actually evaluates comma-separated tables as inner join, which - for some unknown reason - orders differently in evaluation of recursion loop. If I change join condition to inner join on true - from r inner join dual on r.x = r.x - the query also succeeds, which supports this guess.

    I set up own thread on MOS for this problem. There is also answer suspecting bug but without clearer explanation.

    Summary: currently it's not clear whether it is a bug or some undocumented feature. Separation with comma and join condition in where clause seem to be the safest way in this situation.