Hi,
I don't understand exactly how CONNECT_BY_ISCYCLE and CONNECT BY NOCYCLE work.
When I run this query:
WITH data AS
( SELECT 'Rock' AS parent, 'Scissors' AS child FROM dual
UNION ALL SELECT 'Paper', 'Rock' FROM dual
UNION ALL SELECT 'Scissors', 'Paper' FROM dual
UNION ALL SELECT 'Dynamite', 'Rock' FROM dual
)
SELECT SYS_CONNECT_BY_PATH (parent, '/') AS path
, CONNECT_BY_ISCYCLE AS iscycle
FROM data
START WITH parent IN ('Rock', 'Dynamite')
CONNECT BY NOCYCLE parent = PRIOR child
;
I get these results:
PATH ISCYCLE
----------------------------------- -------
/Dynamite 0
/Dynamite/Rock 0
/Dynamite/Rock/Scissors 1
/Rock 0
/Rock/Scissors 0
/Rock/Scissors/Paper 1
The rows starting with 'Rock' are exactly what I expect, but for the rows starting from 'Dynamite', I expected these results:
PATH ISCYCLE
----------------------------------- -------
/Dynamite 0
/Dynamite/Rock 0
/Dynamite/Rock/Scissors 0
/Dynamite/Rock/Scissors/Paper 1
I don't see why a cycle is detected at 'Scissors' when I start from 'Dynamite', but not when I start from 'Rock'.
The SQL Language manual ( https://docs.oracle.com/database/121/SQLRF/pseudocolumns001.htm#SQLRF50939 ) says "The CONNECT_BY_ISCYCLE pseudocolumn returns 1 if the current row has a child which is also its ancestor. Otherwise it returns 0." In the case of "/Dynamite/Rock/Scissors ", the only child of 'Scissors' is 'Paper', but the only ancestors (so far) are 'Dynamite' and 'Rock'.
Why do I get all the rows in the loop when I start from within the loop, but not when I start from outside the loop?
Is there a work-around, so I can get the '/Dynamite/Rock/Scissors/Paper' row included in the output?
I'm using Oracle 12.1.0.2.0, and I get the same results in 10.2.0.1.0.
This problem came up in another thread: Hierarchy query help