Blaze Persistence多CTE在H2数据库的兼容问题及解决

H2数据库2.1.214版本在处理CTE（公共表表达式）的UNION ALL时存在别名解析兼容性bug：

• 当UNION ALL的两个子查询使用不同表别名（s和a）时，H2无法正确编译OtherCTE，触发"视图无效：not compiled"错误。
• PostgreSQL对CTE的别名解析逻辑更灵活，允许不同子查询使用不同别名，因此能正常执行。
• 改用相同别名s后，H2的解析器可以统一识别字段引用，避免了别名冲突导致的编译失败，同时PostgreSQL也能兼容这种写法。
• 额外原因：Blaze-Persistence生成的SQL在H2环境下，因别名不一致生成了H2无法正确解析的语法结构，进一步触发了该bug。

方案1：简化CTE结构，减少嵌套层级 #

直接将两个实体的查询作为UNION ALL的子查询，跳过中间CTE，同时复用已有的Predicate：

val unifiedResults = factory.create(em, Tuple.class)
    .with(OtherCTE.class, false)
        .from(DbStory.class, "s")
            .bind("id").select("s.id")
            .bind("statusChangedAt").select("s.statusChangedAt")
            .bind("type").select("'story'")
            .where(specification.toPredicate(root, query, cb))
        .unionAll()
        .from(DbArticle.class, "a")
            .bind("id").select("a.id")
            .bind("statusChangedAt").select("a.statusChangedAt")
            .bind("type").select("'article'")
            .where(articleSpecification.toPredicate(articleRoot, articleQuery, cb))
        .endSet()
    .end()
    .from(OtherCTE.class)
    .orderByDesc("statusChangedAt")
    .orderByAsc("id")
    .page(0, 100)
    .getResultList();

方案2：配置H2兼容PostgreSQL模式 #

在H2的连接URL中添加MODE=PostgreSQL参数，让H2模拟PostgreSQL的语法解析规则，直接兼容原有的别名写法：

jdbc:h2:mem:testdb;MODE=PostgreSQL;DB_CLOSE_DELAY=-1

方案3：直接拼接Union查询，无需CTE #

利用Blaze-Persistence的unionAll直接合并两个实体查询，省略中间CTE步骤，代码更简洁：

// 构建Story查询
BlazeCriteriaQuery<Tuple> storyQuery = cb.createTupleQuery()
    .from(DbStory.class, "s")
    .select("s.id", "s.statusChangedAt", cb.literal("story").as(String.class))
    .where(specification.toPredicate(root, query, cb));

// 构建Article查询
BlazeCriteriaQuery<Tuple> articleQuery = cb.createTupleQuery()
    .from(DbArticle.class, "a")
    .select("a.id", "a.statusChangedAt", cb.literal("article").as(String.class))
    .where(articleSpecification.toPredicate(articleRoot, articleQuery, cb));

// 合并查询并分页
val unifiedResults = storyQuery.unionAll(articleQuery)
    .orderByDesc("statusChangedAt")
    .orderByAsc("id")
    .page(0, 100)
    .getResultList();

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