H2DB带参数WITH子句（CTE）在子查询中失效问题咨询

你的代码没有语法错误，这个问题是H2DB对带参数的公共表表达式（CTE）在子查询中的支持存在兼容性bug，Oracle 19c能正常执行是因为其对CTE的实现完全符合SQL标准，不存在这个限制。

问题触发条件验证 #

从你的三个测试场景可以明确：

• 带参数的CTE直接关联查询：H2DB正常返回结果
• 带参数的CTE通过子查询引用：关联字段全为NULL
• 无参数的CTE通过子查询引用（视图中）：H2DB正常返回结果

仅当「参数绑定 + CTE定义 + 子查询引用CTE」三者组合时，才会触发此问题，说明不是SQL语法错误，而是H2DB的参数绑定与CTE子查询解析的兼容性问题。

解决方法 #

1. 调整SQL写法：避免在子查询中引用带参数的CTE，直接关联CTE表，如你第一个示例的写法即可正常工作。
2. 升级H2DB版本：尝试更新到H2DB的最新稳定版，官方后续版本可能修复了该兼容性bug。
3. 测试环境替换：如果升级后问题仍存在，测试环境可改用Oracle XE等更贴近生产的数据库，减少跨数据库的兼容性差异。

你的测试示例 #

1. 正常工作的带参数CTE直接关联查询

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.DESCRIPTION
    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

2. H2DB中失效的带参数CTE子查询关联

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.DESCRIPTION
    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

3. 正常工作的无参数CTE子查询关联（视图）

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.DESCRIPTION
    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

内容的提问来源于stack exchange，提问作者bahnson