I have recursive query on table with undirected graph data. Data is represented as table of edges (Oracle 11g). The recursive query traverses all edges starting from given edge. The idea of query is: given edge is at the 0th level and for n>0, edge is on n-th level if it incides with node of some so-far-unseen edge on (n-1)-th level.
with edges (id, a, b) as (
select 1, 'X', 'Y' from dual union
select 2, 'X', 'Y' from dual
), r (l, id, parent_a, parent_b, child_a, child_b, edge_seen) as (
select 0, id, null, null, a, b, cast(collect(id) over () as sys.ku$_objnumset)
from edges
where id = 1
union all
select r.l + 1, e.id, r.child_a, r.child_b, e.a, e.b
, r.edge_seen multiset union distinct (cast(collect(e.id) over () as sys.ku$_objnumset))
from r
join edges e on (r.child_a in (e.a, e.b) or r.child_b in (e.a, e.b))
and e.id not member of (select r.edge_seen from dual)
)
select * from r;
The query worked well until this situation with two edges between nodes occured:
+---+ 1 +---+
| |<----->| |
| X | 2 | Y |
| |<----->| |
+---+ +---+
In this case, there is edge 1 on 0th level of recursion (initial row). I expected edge 2 would be added to result on 1st level of recursion since join condition holds. Instead I get "ORA-32044: cycle detected while executing recursive with query".
I know this error is reported when the row newly joined to recursive query result would be same as some existing row. What I don't understand is why Oracle treats row with same node ids but different edge id as duplicate.
Adding cycle child_a, child_b set iscycle to 1 default 0 clause gives iscycle=1 for new row, adding cycle id, child_a, child_b set iscycle to 1 default 0 gives iscycle=0, which is both correct.
Why the error is produced and how to avoid it? (Note in Postgres (see fiddle) equivalent query works correctly.)
