Recursive CTE in H2: No data returned

问题:

I am trying to convert a proprietary Oracle CONNECT BY query into a standard SQL query that will run on H2, and generate the same data in the same order.

This is the Oracle query, which works:

SELECT id, name, parent
        FROM myschema.mytable 
        START WITH id = 1 
        CONNECT BY PRIOR id = parent

This is what I've come up - however, it returns no rows in the ResultSet.

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

The initial row, and the related rows, both exist in the table.

I am not using H2's Oracle compatibility mode (which doesn't support CONNECT BY, by the way).

回答1:

The following works for me, for both H2 as well as PostgreSQL (this you can test online using the SQL Fiddle). I had to make a few changes and assumptions (see below):

create table mytable(id int, name varchar(255), parent int);
insert into mytable values(1, 'root', null), (2, 'first', 1), 
(3, 'second', 1), (4, '2b', 3);

WITH RECURSIVE T(id, name, parent, path) AS (
SELECT id, name, 0 AS parent, 
    cast(id as varchar) AS path 
FROM 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 mytable AS ou ON T.id = ou.parent
) SELECT id, name, parent, path FROM T ORDER BY path

Changes:

• I assumed id and parent are integers. Because of that, I had to use cast(id as varchar) in the first select.
• I replace + with || when concatenating strings.
• I used 0 AS parent.

回答2:

This seems to have been a problem with either the Anorm database access library or the JDBC driver not substituting a query parameter correctly (the query substitution was not shown in the question, because I assumed it wasn't relevant).