title: Recursive CTE in H2: No data returned
body: <p>I am trying to convert a proprietary Oracle <code>CONNECT BY</code> query into a standard SQL query that will run on H2, and generate the same data <em>in the same order</em>.</p>
  
  <p>This is the Oracle query, which works:</p>
  
  <pre><code>SELECT id, name, parent
          FROM myschema.mytable 
          START WITH id = 1 
          CONNECT BY PRIOR id = parent
  </code></pre>
  
  <p>This is what I've come up - however, it returns no rows in the <code>ResultSet</code>.</p>
  
  <pre><code>WITH RECURSIVE T(id, name, parent, path) AS (
      SELECT id, name, '' AS parent, id AS path 
      FROM myschema.mytable WHERE id = 1
      UNION ALL
      SELECT ou.id, ou.name, ou.parent, 
          (T.path + '.' + CAST (ou.id AS VARCHAR)) AS path 
      FROM T INNER JOIN myschema.mytable AS ou ON T.id = ou.parent
  ) SELECT id, name, parent FROM T ORDER BY path
  </code></pre>
  
  <p>The initial row, and the related rows, both exist in the table.</p>
  
  <p>I am not using H2's Oracle compatibility mode (which doesn't support CONNECT BY, by the way).</p>