3

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.

Tomáš Záluský
  • 10,735
  • 2
  • 36
  • 64
  • 1
    I can reproduce with Oracle 19.5. It looks like a bug. If you have an Oracle support contract, you know what you have to do otherwise you can ask this question on https://asktom.oracle.com/pls/apex/f?p=100:1000:::::: to have a free answer from Oracle. – pifor Jun 03 '20 at 12:48

3 Answers3

2

Using the first recursive query, you will get 5 records, when you try to cross join on the recursive result, each time it iterates the data, for each iteration according to the value 5, the values gets looped against each other resulting in, ORA-32044: cycle detected while executing recursive WITH query error. Instead you have to cross join out side the recursive result as below,

with r (x) as (
  select 1 as x from dual
  union all
select x + 1 from r,dual where x < 5
)
select * from r
cross join r;
Jim Macaulay
  • 4,709
  • 4
  • 28
  • 53
  • 1
    This will give a very different result - 25 rows with two columns, instead of 5 rows with one column? – Alex Poole Jun 03 '20 at 13:08
  • Thanks for response Jim, my intention is not to cross join relation `r` with itself. My intention is to cross join relation `r` with dummy table (looks silly in minimalistic example but makes sense in real) which has limited number of rows (1 in example, 2 actually though it imho does not matter). – Tomáš Záluský Jun 03 '20 at 13:30
  • Please provide the expected result, so that can help you – Jim Macaulay Jun 03 '20 at 13:33
  • Expected result is table with 1 column, 5 rows and values 1 to 5 as returned from first query in my question. My question is rather about the reason of different behaviour of two kinds of syntax which I thought they were equivalent until now. – Tomáš Záluský Jun 03 '20 at 13:38
  • Both will not provide you same behavior. Respective to the iterations, cyclic range would differ as i explained in the answer – Jim Macaulay Jun 03 '20 at 13:40
  • I don't understand the _for each iteration according to the value 5, the values gets looped against each other_ sentence. I expect every new level of recursive query appends only one new row into result, regardless if dual table is joined or not. Why should db engine process value again? – Tomáš Záluský Jun 03 '20 at 14:00
2

You can add the cycle clause to solve it:

with r (x) as (
      select 1 as x from dual
      union all
      select x + 1   from r cross join dual where x < 5
    ) cycle x set is_cycle to 1 default 0 
    select * from r;

Gives the required result.

gsalem
  • 1,957
  • 1
  • 8
  • 7
  • 1
    Worth noting that `is_cycle` comes out as 0, further supporting that this is a bug. – Alex Poole Jun 03 '20 at 14:04
  • 1
    Yep, I suspect that in the transformation in the optimizer, something is going wrong and this is inhibited by the cycle clause – gsalem Jun 03 '20 at 14:39
1

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.

Tomáš Záluský
  • 10,735
  • 2
  • 36
  • 64