Oracle recursive WITH...UNION ALL requires column alias list

[A Thomas]

First, thanks a lot for QueryDSL - it's been a huge help!

My little problem is that I'm in the middle of an attempt to use a recursive WITH... UNION ALL... statement and getting what looks like valid SQL out of it, but Oracle insists on the output column names being listed up front, which QueryDSL isn't providing. It starts:

WITH nodeGraph AS
(SELECT
   rootNodeDef.ID nodeDefId,
...

whereas Oracle wants:

WITH nodeGraph(

    nodeDefId,
    nodeName,
    parentNodeDefId,
    childNodeDefId,
    nodeLevel)
AS
(SELECT
   rootNodeDef.ID nodeDefId,
...

I can see a test in SelectBase.java that is recursive WITH and this also doesn't generate the column aliases.
It does work though, so I think Oracle may only require them when the query includes UNION and so results in N-level recursion.
Here is the full query FWIW:


WITH nodeGraph(
    nodeDefId,
    nodeName,
    parentNodeDefId,
    childNodeDefId,
    nodeLevel)
AS
(SELECT
   rootNodeDef.ID nodeDefId,
   rootNodeDef.NODE_NAME nodeName,
   NULL parentNodeDefId,
   nodeHierarchy.CHILD_NODE_DEF_ID childNodeDefId,
   1          nodeLevel
 FROM REPORT report
   JOIN NODE_DEF rootNodeDef
     ON rootNodeDef.ID = report.NODE_DEF_ID
   JOIN NODE_HIERARCHY nodeHierarchy
     ON nodeHierarchy.NODE_DEF_ID =
        rootNodeDef.ID
 WHERE report.REPORT_ID = 1
 UNION ALL
 (SELECT
    nodeDef.ID nodeDefId,
    nodeDef.NODE_NAME nodeName,
    nodeGraph.nodeDefId parentNodeDefId,
    nodeHierarchy.CHILD_NODE_DEF_ID childNodeDefId,
    nodeGraph.nodeLevel + 1
  FROM NODE_DEF nodeDef
    LEFT JOIN NODE_HIERARCHY nodeHierarchy
      ON nodeHierarchy.NODE_DEF_ID =
         nodeDef.ID
    JOIN nodeGraph
      ON nodeGraph.childNodeDefId = nodeDef.ID))
SELECT *
FROM nodeGraph
  LEFT JOIN NODE node ON node.NODE_DEF_ID = nodeGraph.nodeDefId
ORDER BY nodeLevel, nodeDefId, childNodeDefId