PostgreSQL正常创建的视图在Spring Boot测试H2库中报错：找不到prov.block_id

问题根源 #

你的SQL语句在PostgreSQL中正常，但H2报错，核心原因是H2与PostgreSQL对子查询中外层表字段的解析规则不一致：

• PostgreSQL允许在子查询的JOIN ON子句中直接引用外层查询的表别名（比如这里的prov）；
• 但H2会将JOIN ON子句中的字段视为当前子查询范围内的表字段，无法识别外层的prov.block_id、prov.slot_id等引用，因此报错找不到字段。

另外原SQL的子查询JOIN逻辑存在歧义，同时关联signal和外层prov的字段，进一步加剧了H2的解析冲突。

修正方案（两种可选） #

方案1：拆分逻辑为嵌套EXISTS（推荐，逻辑更清晰） #

将原有的多表JOIN拆分为独立的EXISTS子查询，每个条件单独校验，避免跨层字段引用：

CREATE VIEW my_view AS
SELECT 
    CASE
        WHEN EXISTS (
            SELECT 1
            FROM signal si
            WHERE si.status = 'VALID'
              AND (
                -- 校验BLOCK类型信号与当前prov的关联
                EXISTS (
                    SELECT 1 FROM block b 
                    WHERE b.id = si.signal_id AND si.type = 'BLOCK' AND b.id = prov.block_id
                )
                -- 校验PROV类型信号与当前prov的关联
                OR EXISTS (
                    SELECT 1 FROM prov p_direct 
                    WHERE p_direct.id = si.signal_id AND si.type = 'PROV' AND p_direct.id = prov.id
                )
                -- 校验SLOT类型信号与当前prov的关联
                OR EXISTS (
                    SELECT 1 FROM slot s 
                    WHERE s.id = si.signal_id AND si.type = 'SLOT' AND s.id = prov.slot_id
                )
                -- 校验BEAM类型信号与当前prov的关联（通过SLOT间接关联）
                OR EXISTS (
                    SELECT 1 FROM slot s 
                    JOIN beam be ON s.beam_id = be.id
                    WHERE s.id = si.signal_id AND si.type = 'BEAM' AND s.id = prov.slot_id
                )
              )
        ) THEN TRUE
        ELSE FALSE
    END AS is_ok,
    slot.beam_id AS b_id,
    prov.name
FROM public.prov prov
LEFT JOIN public.slot slot ON prov.slot_id = slot.id;

方案2：调整关联条件到WHERE子句 #

保留原有的多表JOIN结构，将外层prov的关联条件从JOIN ON移到WHERE子句，让H2能识别外层表别名：

CREATE VIEW my_view AS
SELECT 
    CASE
        WHEN EXISTS (
            SELECT 1
            FROM signal si
            LEFT JOIN block b ON b.id = si.signal_id AND si.type = 'BLOCK'
            LEFT JOIN prov p_direct ON p_direct.id = si.signal_id AND si.type = 'PROV'
            LEFT JOIN slot s ON s.id = si.signal_id AND si.type = 'SLOT'
            LEFT JOIN beam be ON s.beam_id = be.id AND si.type = 'BEAM'
            WHERE si.status = 'VALID'
              AND (
                b.id = prov.block_id
                OR p_direct.id = prov.id
                OR s.id = prov.slot_id
                OR (s.id = prov.slot_id AND be.id IS NOT NULL)
              )
        ) THEN TRUE
        ELSE FALSE
    END AS is_ok,
    slot.beam_id AS b_id,
    prov.name
FROM public.prov prov
LEFT JOIN public.slot slot ON prov.slot_id = slot.id;

验证说明 #

两种方案都能在H2中正常创建视图，同时保持与PostgreSQL的兼容性。方案1的逻辑拆分更直观，便于后续维护和调试；方案2更贴近原SQL结构，改动量更小。

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