AWS Babelfish关联子查询执行报错，无法复现原结果求助

我们正在将SQL Server数据库迁移至启用Babelfish的AWS Aurora Postgres，已通过1433端口使用SSMS连接该Babelfish数据库。运行Babelfish Compass报告并修复不支持的功能后，在修改查询/存储过程（SP）时遇到若干问题：其中一个存储过程中的关联子查询可正常创建，但执行时触发错误。尝试用JOIN、EXISTS、CTE等方式替换该子查询，均无法复现原查询结果。

示例查询 #

SELECT t.POLICY_DOCUMENT_ID,
        convert(xml, (select rd.Id as TagId, rd.def_id as RelatedDataDefId, rdd.name_tx as [Name], rd.VALUE_TX as TagCode, rdd.DATA_TYPE_CD as TagDataType, rdd.DOMAIN_CD as TagDomainName
                          from 
                                TEST_DATA rd 
                                join TEST_DATA_DEF rdd on rdd.relate_class_nm = 'PolicyDocument' and rd.def_id = rdd.id and rdd.ACTIVE_IN = 'Y'
                                --join #tempPDIDs th  on rd.relate_id = th.POLICY_DOCUMENT_ID 
                         where      rd.relate_id = t.POLICY_DOCUMENT_ID
                                for xml PATH('PolicyDocumentTag'), ROOT('PolicyDocumentTags'))) AS POLICY_DOCUMENT_TAGS_XML
        
        from
            #tempPDIDs t
            join TEST_DOCUMENT pd on pd.id = t.POLICY_DOCUMENT_ID and pd.PURGE_DT is null
            left join #tempPDIDFTSTX tmp on pd.id = tmp.POLICY_DOCUMENT_ID

错误信息 #

Msg 33557097, Level 16, State 1, Line 72
missing FROM-clause entry for table "t"

这个错误源于Babelfish处理SQL Server风格XML子查询时的兼容性限制——底层Postgres的XML生成逻辑无法直接识别子查询中引用的外部表别名t。以下两种方法可修复问题且保留原查询结果：

方法一：用LATERAL JOIN重构查询 #

将XML生成子查询改为LATERAL JOIN，明确关联外部表，确保Babelfish能正确解析关联逻辑：

SELECT t.POLICY_DOCUMENT_ID,
       convert(xml, xml_result) AS POLICY_DOCUMENT_TAGS_XML
FROM #tempPDIDs t
JOIN TEST_DOCUMENT pd ON pd.id = t.POLICY_DOCUMENT_ID AND pd.PURGE_DT IS NULL
LEFT JOIN #tempPDIDFTSTX tmp ON pd.id = tmp.POLICY_DOCUMENT_ID
LEFT JOIN LATERAL (
    SELECT rd.Id as TagId, 
           rd.def_id as RelatedDataDefId, 
           rdd.name_tx as [Name], 
           rd.VALUE_TX as TagCode, 
           rdd.DATA_TYPE_CD as TagDataType, 
           rdd.DOMAIN_CD as TagDomainName
    FROM TEST_DATA rd 
    JOIN TEST_DATA_DEF rdd ON rdd.relate_class_nm = 'PolicyDocument' 
                          AND rd.def_id = rdd.id 
                          AND rdd.ACTIVE_IN = 'Y'
    WHERE rd.relate_id = t.POLICY_DOCUMENT_ID
    FOR XML PATH('PolicyDocumentTag'), ROOT('PolicyDocumentTags')
) AS xml_subquery(xml_result) ON 1=1

方法二：将外部字段作为参数传入子查询 #

通过CROSS APPLY把外部表的字段转为参数，避免直接在子查询中引用外部表别名：

SELECT t.POLICY_DOCUMENT_ID,
       convert(xml, (
           SELECT rd.Id as TagId, 
                  rd.def_id as RelatedDataDefId, 
                  rdd.name_tx as [Name], 
                  rd.VALUE_TX as TagCode, 
                  rdd.DATA_TYPE_CD as TagDataType, 
                  rdd.DOMAIN_CD as TagDomainName
           FROM TEST_DATA rd 
           JOIN TEST_DATA_DEF rdd ON rdd.relate_class_nm = 'PolicyDocument' 
                                 AND rd.def_id = rdd.id 
                                 AND rdd.ACTIVE_IN = 'Y'
           WHERE rd.relate_id = @policy_doc_id
           FOR XML PATH('PolicyDocumentTag'), ROOT('PolicyDocumentTags')
       )) AS POLICY_DOCUMENT_TAGS_XML
FROM #tempPDIDs t
JOIN TEST_DOCUMENT pd ON pd.id = t.POLICY_DOCUMENT_ID AND pd.PURGE_DT IS NULL
LEFT JOIN #tempPDIDFTSTX tmp ON pd.id = tmp.POLICY_DOCUMENT_ID
CROSS APPLY (SELECT t.POLICY_DOCUMENT_ID AS @policy_doc_id) AS params

优先测试方法一，LATERAL JOIN在Babelfish中的兼容性更稳定，能完全匹配原查询的输出逻辑。

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