Bug in WITH clause (Common Table Expression) when using SQL parameters

[GoogleCodeExporter]

The following hierarchical query works as expected (returns 1 row) as long as I 
don't use parameters inside the WITH clause:

WITH portfolio_hier(parent_node,child_node,id)
    AS
    ( SELECT NULL,
        portfolio.id,
        NULL    
      FROM portfolio
    WHERE (portfolio.id = 239)  

      UNION ALL
      SELECT er.parent_node,
        er.child_node,
        er.id       
      FROM portfolio_adj er
          INNER JOIN portfolio_hier ON (er.parent_node=portfolio_hier.child_node)
          INNER JOIN portfolio ON (er.child_node=portfolio.id)
      )
    SELECT * from portfolio_hier

2.

Now, when I substitute the clause (portfolio.id = 239) with (portfolio.id = ?), 
the query returns no results, which is wrong.

WITH portfolio_hier(parent_node,child_node,id)
    AS
    ( SELECT NULL,
        portfolio.id,
        NULL    
      FROM portfolio
    WHERE (portfolio.id = ?)    

      UNION ALL
      SELECT er.parent_node,
        er.child_node,
        er.id       
      FROM portfolio_adj er
          INNER JOIN portfolio_hier ON (er.parent_node=portfolio_hier.child_node)
          INNER JOIN portfolio ON (er.child_node=portfolio.id)
      )
    SELECT * from portfolio_hier

Versions tried:
h2-1.3.164
h2-1.3.169

OS:
Windows 7 x64
Java: jdk1.7.0_07

Original issue reported on code.google.com by peter.pe...@gmail.com on 29 Oct 2012 at 4:52

[GoogleCodeExporter]

Hi,

I'm sorry but parameters are currently not supported in this case, see also 
http://h2database.com/html/advanced.html#recursive_queries

"Parameters are only supported within the last SELECT statement (a workaround 
is to use session variables like @start within the table expression)."

Original comment by thomas.t...@gmail.com on 12 Feb 2013 at 7:15

• Changed state: WontFix