h2db: can I use parameterized WITH clauses (CTEs) in subqueries

Question

I'm using h2db 2.2.224 as Test-DB in Spring Boot 3.2.5, running a JPA native query.

I have a WITH clause (CTE) that uses a query parameter. This WITH clause does not work in a subquery against h2db. It does work against PROD-DB Oracle 19c

I need someone to confirm that the example below contains some simple error I don't see, or that parameterized WITH clauses in subqueries are not supported by h2db.

I can confirm that 'unparameterized' WITH clauses can be used in subqueries inside a view with h2db (bottom of example).

Example

Consider this parameterized query, that still works as expected against h2db:

WITH CTE_FOO AS (
    SELECT f.ID,
           f.BAR_ID,
    FROM FOO f
    WHERE f.LOCATION_ID IN ?1
),
CTE_BAR AS (
    SELECT b.ID,
           b.DESCIPTION
    FROM BAR b
    WHERE b.LOCATION_ID IN ?1
)
SELECT cf.ID           AS FOO_ID,
       cb.DESCRIPTION  AS BAR_DESCRIPTION
FROM CTE_FOO cf
LEFT JOIN CTE_BAR cb ON cf.BAR_ID = cb.ID

The result set is like

FOO_ID BAR_DESCRIPTION
====== ===============
1      babble
2      gibber
....

Now, if I change the very last line, introducing a subselect

WITH CTE_FOO AS (
    SELECT f.ID,
           f.BAR_ID,
    FROM FOO f
    WHERE f.LOCATION_ID IN ?1
),
CTE_BAR AS (
    SELECT b.ID,
           b.DESCIPTION
    FROM BAR b
    WHERE b.LOCATION_ID IN ?1
)
SELECT cf.ID           AS FOO_ID,
       cb.DESCRIPTION  AS BAR_DESCRIPTION
FROM CTE_FOO cf
LEFT JOIN ( SELECT * FROM CTE_BAR ) cb ON cf.BAR_ID = cb.ID

The result set is missing the joined fields, which comes unexpected

FOO_ID BAR_DESCRIPTION
====== ===============
1      NULL
2      NULL
...

However, using unparameterized WITH clauses in subquery inside a view works with h2db. When putting the below code into schema.sql and querying the view, the result set contains the joined fields again.

CREATE VIEW VW_FOO_BAR AS
WITH CTE_FOO AS (
    SELECT f.ID,
           f.BAR_ID,
    FROM FOO f
),
CTE_BAR AS (
    SELECT b.ID,
           b.DESCIPTION
    FROM BAR b
)
SELECT cf.ID           AS FOO_ID,
       cb.DESCRIPTION  AS BAR_DESCRIPTION
FROM CTE_FOO cf
LEFT JOIN ( SELECT * FROM CTE_BAR ) cb ON cf.BAR_ID = cb.ID

So it seems the problem only occurs when using parameter + WITH clause + subquery.

Answer 1

Upgrading H2 to version 2.3.230 fixed the issues with parameterized WITHs/CTEs.

A big 'thank you' to the H2 maintainers.