I have problem with 'cycle detected while executing recursive WITH query' error where I wouldn't expect it. The goal in my sql query is to control output with 'i' variable. The 'j' variable is automatically controlled by MOD() second parameter. But when I try query from the Statement 2 it results in 'ORA-32044: cycle detected while executing recursive WITH query' error. The workaround is to add conjunction with second condition for i times j, as shown in Statement 1. But this is redundant and in my opinion this should be handled by db engine gracefully with just one condition.
What is more Statement 2 works without any problems on PostgreSQL and SQL Server.
This is the documented behaviour in Oracle Database:
If you omit the CYCLE clause, then the recursive WITH clause returns an error if cycles are discovered. In this case, a row forms a cycle if one of its ancestor rows has the same values for all the columns in the column alias list for query_name that are referenced in the WHERE clause of the recursive member. https://docs.oracle.com/en/database/oracle/oracle-database/21/sqlrf/SELECT.html#GUID-CFA006CA-6FF1-4972-821E-6996142A51C6__GUID-8EE64250-3C9A-40C7-A81D-46695F8B2EB9 When the only column in the WHERE clause of the recursive part is I the database detects a loop if two rows in the hierarchy have the same value for I. This trips on the second row as you can see if you add the CYCLE clause:
WITH temp1 (i, j, name1, name2)
AS
(
SELECT
1,
1,
'nameA_1',
'nameB_1'
FROM dual
UNION ALL
SELECT
CASE
WHEN MOD(j + 1,10) != 0 THEN i
ELSE i + 1
END,
j + 1,
'nameA_' || i,
'nameB_' || (j + 1)
FROM temp1
WHERE
i <= 3
)
CYCLE i SET is_loop TO 'Y' DEFAULT 'N'
SELECT *
FROM temp1;
I J NAME1 NAME2 IS_LOOP
1 1 nameA_1 nameB_1 N
1 2 nameA_1 nameB_2 Y Adding J to the WHERE clause avoids this because the combination of (I, J) is unique in this data set.
An alternative to a dummy WHERE clause is to use the CYCLE clause to set I, J to be what's used to detect loops:
WITH temp1 (i, j, name1, name2)
AS
(
SELECT
1,
1,
'nameA_1',
'nameB_1'
FROM dual
UNION ALL
SELECT
CASE
WHEN MOD(j + 1,10) != 0 THEN i
ELSE i + 1
END,
j + 1,
'nameA_' || i,
'nameB_' || (j + 1)
FROM temp1
WHERE
i <= 3
)
CYCLE i, j SET is_loop TO 'Y' DEFAULT 'N'
SELECT *
FROM temp1;
I J NAME1 NAME2 IS_LOOP
1 1 nameA_1 nameB_1 N
1 2 nameA_1 nameB_2 N
1 3 nameA_1 nameB_3 N
1 4 nameA_1 nameB_4 N
1 5 nameA_1 nameB_5 N
1 6 nameA_1 nameB_6 N
1 7 nameA_1 nameB_7 N
1 8 nameA_1 nameB_8 N
1 9 nameA_1 nameB_9 N
2 10 nameA_1 nameB_10 N
2 11 nameA_2 nameB_11 N
2 12 nameA_2 nameB_12 N
2 13 nameA_2 nameB_13 N
2 14 nameA_2 nameB_14 N
2 15 nameA_2 nameB_15 N
2 16 nameA_2 nameB_16 N
2 17 nameA_2 nameB_17 N
2 18 nameA_2 nameB_18 N
2 19 nameA_2 nameB_19 N
3 20 nameA_2 nameB_20 N
3 21 nameA_3 nameB_21 N
3 22 nameA_3 nameB_22 N
3 23 nameA_3 nameB_23 N
3 24 nameA_3 nameB_24 N
3 25 nameA_3 nameB_25 N
3 26 nameA_3 nameB_26 N
3 27 nameA_3 nameB_27 N
3 28 nameA_3 nameB_28 N
3 29 nameA_3 nameB_29 N
4 30 nameA_3 nameB_30 N