Oracle version 12.2.0.1 Enterprise Edition
Going back on an older post: Head scratcher (bug?) - seesaw recursive CTE
The question there was, how does Oracle detect cycles in a recursive CTE? More specifically, even though I may not have used these exact words, the question was - when the CYCLE clause is not used explicitly, which columns does Oracle use to define "cycles"?
This is actually written in the documentation, I was just not reading it right (until today):
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.
However, I will show that this is not entirely correct (unless I am making new mistakes). If I am right, this is a bug in the Oracle implementation of the CYCLE clause. "This" being an example I will show a little later.
First a simple but important question: if there is no WHERE clause, or if there is a WHERE clause like WHERE 0 = 0, there are NO columns in the column alias list of the recursive CTE referenced in the WHERE clause. So, in this case, absent a CYCLE clause, when will Oracle detect a cycle? The theoretical answer is that the set of columns considered for CYCLE is the empty set; and there is exactly one 0-tuple (the tuple with no members), so cycles will be detected immediately. Rows in the anchor do not form a cycle, but all rows in the first iteration of the recursive member will form cycles.
This seems to be supported by experiments. Alas, the syntax does not allow the inclusion of an explicit CYCLE clause with an empty set of column names, so we can't see exactly where the query finds the cycles... The most we can see is this:
with r(x) as (select 0 from dual union all select x+1 from r)
select * from r;
ERROR:
ORA-32044: cycle detected while executing recursive WITH query
no rows selected
If I add an always-true WHERE clause, no cycle will be detected (and the query will result in an infinite loop, with undefined outcome):
with r(x) as (select 0 from dual union all select x+1 from r where x is not null)
select * from r;
For this query, SQL Developer will show me the first 200 rows (but perhaps it is still churning in the background till it will run out of memory). If I try this in SQL*Plus, I need to hit CTRL-C to kill the query.
What about join conditions?
It stands to reason that if the recursive member involves a join, the columns from the recursive CTE that appear in the join condition will be used for cycle detection when the CYCLE clause is not used. And indeed that is the case:
with r(x) as (select 0 from dual union all select x+1 from r inner join dual on x is not null)
select * from r;
does not throw the "cycle detected" error; instead, it will generate infinitely many rows (or crash when it runs out of memory).
However, if there is an ANSI join in the recursive member, then the WHERE clause is no longer inspected for columns to be used for cycle detection!
This is the bug I was talking about. To summarize: if there is no CYCLE clause, and if the recursive member does not involve a join, but has a WHERE clause, whatever columns from the SELECT list of the recursive CTE appear in the WHERE clause will be considered for cycle detection. If the recursive member does involve a join, written with ANSI syntax, then the columns (from the recursive CTE) involved in the join condition are considered for join detection. What seems to be a bug is that if the recursive member has both a join AND a WHERE clause, then the columns in the WHERE clause are not considered for cycle detection; only the columns in the join condition are considered. With ANSI syntax, this is true even when the join is written as a CROSS JOIN (so, no join conditions) and all the conditions are put in a WHERE clause; in this case, the empty set of columns is used for cycle detection, unless there is an explicit CYCLE clause.
with r(x) as (select 0 from dual union all select r.x+1 from r cross join dual where x is not null)
select x from r;
ERROR:
ORA-32044: cycle detected while executing recursive WITH query
no rows selected
There are no such subtleties with comma syntax for joins (old Oracle syntax) - for those joins, everything is in a WHERE clause, and all the columns in the WHERE clause are included by default for cycle detection.
* * * * *
Coming to the old post, the query should be written as follows. NOTE - this will only make sense in the context of that thread; the discussion is specific to the seesaw recursive CTE method presented there.
with
inputs ( input_str ) as (
select 'Hello, World' from dual union all
select '$1 = True' from dual union all
select '$2 > $3' from dual union all
select '$1 = $4' from dual
),
mappings ( placeholder, val ) as (
select '$1', 'Example 1' from dual union all
select '$2', 'Example 2' from dual union all
select '$3', 'Example 3' from dual union all
select '$4', '$2 + $3' from dual
),
rec ( input_str, new_str, next_placeholder, flag, ct ) as (
select input_str, input_str, regexp_substr(input_str, '\$\d+'), 1, 1
from inputs
union all
select r.input_str,
case r.flag when -1 then r.new_str
else replace(r.new_str, r.next_placeholder, m.val) end,
case r.flag when 1 then r.next_placeholder
else regexp_substr(r.new_str, '\$\d+') end,
- r.flag,
ct + 1
from rec r inner join mappings m
on r.next_placeholder = m.placeholder
where r.next_placeholder is not null left over from attempts to break cycles without the CYCLE clause
)
CYCLE input_str, new_str, next_placeholder, flag SET cycle TO 1 DEFAULT 0
select * from rec
order by input_str, ct;
The issue here is that, in the absence of the CYCLE clause, only NEXT_PLACEHOLDER is considered for cycle detection. In the seesaw recursion method, the NEXT_PLACEHOLDER is changed every two steps, not at every step. There are no cycles when considering both NEXT_PLACEHOLDER and FLAG, but FLAG does not appear in the join condition. So - either we use the CYCLE clause, which only needs these two columns, not all four; or we add some trivially true condition, like FLAG IS NOT NULL, to the join condition. Not to a WHERE clause in the recursive member - that would not help. This is what actually prompted me to dig a bit deeper and to write this new post.