title: Cycle detection with recursive subquery factoring
body: <p>Oracle SQL can do hierarchical queries since v2, using their proprietary CONNECT BY syntax. In their latest 11g release 2, they added recursive subquery factoring, also known as the recursive with clause. This is the ANSI standard, and if I understand correctly, this one has been implemented by other RDBMS vendors as well.</p>
  
  <p>When comparing the connect-by with the recursive with, I noticed a difference in the result set when using cycle detection. The connect by results are more intuitive to me, so I'm wondering if Oracle's implementation contains a bug, or if this is standard ANSI and expected behaviour. Therefore my question is if you can check the recursive with query using other databases like MySQL, DB2, SQL Server and others. Provided those databases support the recursive with clause of course.</p>
  
  <p>Here is how it works on Oracle 11.2.0.1.0</p>
  
  <pre><code>SQL&gt; select *
    2    from t
    3  /
  
          ID  PARENT_ID
  ---------- ----------
           1          2
           2          1
  
  2 rows selected.
  </code></pre>
  
  <p>The query using CONNECT BY syntax:</p>
  
  <pre><code>SQL&gt;  select id
    2        , parent_id
    3        , connect_by_iscycle
    4     from t
    5  connect by nocycle parent_id = prior id
    6    start with id = 1
    7  /
  
          ID  PARENT_ID CONNECT_BY_ISCYCLE
  ---------- ---------- ------------------
           1          2                  0
           2          1                  1
  
  2 rows selected.
  </code></pre>
  
  <p>Which looks intuitive to me. However, using the new ANSI syntax it returns one more row:</p>
  
  <pre><code>SQL&gt; with tr (id,parent_id) as
    2  ( select id
    3         , parent_id
    4      from t
    5     where id = 1
    6     union all
    7    select t.id
    8         , t.parent_id
    9      from t
   10           join tr on t.parent_id = tr.id
   11  ) cycle id set is_cycle to '1' default '0'
   12  select id
   13       , parent_id
   14       , is_cycle
   15    from tr
   16  /
  
          ID  PARENT_ID I
  ---------- ---------- -
           1          2 0
           2          1 0
           1          2 1
  
  3 rows selected.
  </code></pre>
  
  <p>This is the script you can use to check:</p>
  
  <pre><code>create table t
  ( id        number
  , parent_id number
  );
  insert into t values (1, 2);
  insert into t values (2, 1);
  commit;
  with tr (id,parent_id) as
  ( select id
         , parent_id
      from t
     where id = 1
     union all
    select t.id
         , t.parent_id
      from t
           join tr on t.parent_id = tr.id
  ) cycle id set is_cycle to '1' default '0'
  select id
       , parent_id
       , is_cycle
    from tr;
  </code></pre>