SSMS链接PostgreSQL添加WHERE子句后关联表返回NULL问题

问题现象 #

通过Linked Server将PostgreSQL 12连接到SSMS后，执行两个表的LEFT JOIN查询出现以下异常：

• 无过滤条件时，关联字段正常填充，返回3393行；
• 在WHERE子句添加a.created_at >= '2023-10-10'后，返回1551行符合时间条件的数据，但关联的disk_device表字段除首条外均为NULL；
• 将时间条件移至LEFT JOIN的ON子句时，返回3270行，未正确过滤主表数据。

解决方案 #

通过子查询/CTE先过滤主表数据，再执行关联，确保过滤逻辑在PostgreSQL端优先执行，避免Linked Server的查询优化异常。

子查询实现 #

SELECT
    filtered_a.hardware_id AS [HardwareID], 
    filtered_a.created_at AS CreatedAt,
    ISNULL(b.serial, NULL) AS [DiskSerial],
    ISNULL(b.model, NULL) AS [DiskModel],
    ISNULL(b.vendor, NULL) AS [DiskMake],
    ISNULL(b.type_display, NULL) AS [DiskType],
    ISNULL(b.transport, NULL) AS [DiskTransport],
    ISNULL(b.status_display, NULL) AS [DiskStatus],
    ISNULL(b.capacity_display, NULL) AS [DiskCapacity],
    ISNULL(CONVERT(DATETIME, b.erase_finish_time AT TIME ZONE 'UTC'), NULL) AS [DiskEraseFinishTime],
    ISNULL(b.device_id, NULL) AS [DiskEraseID],
    ISNULL(b.erase_algorithm_display, NULL) AS [DiskEraseMethod],
    ISNULL(b.erase_level, NULL) AS [DiskEraseLevel],
    ISNULL(CONVERT(DATETIME, b.erase_start_time AT TIME ZONE 'UTC'), NULL) AS [DiskEraseStartTime],
    ISNULL(b.erase_result_display, NULL) AS [DiskEraseResult]
FROM (
    SELECT hardware_id, created_at
    FROM [LinkedServerName].[DatabaseName].[public].[hardware]
    WHERE created_at >= '2023-10-10'
) AS filtered_a
LEFT JOIN [LinkedServerName].[DatabaseName].[public].[disk_device] AS b 
    ON filtered_a.hardware_id = b.hardware_id

CTE实现 #

WITH filtered_hardware AS (
    SELECT hardware_id, created_at
    FROM [LinkedServerName].[DatabaseName].[public].[hardware]
    WHERE created_at >= '2023-10-10'
)
SELECT
    f.hardware_id AS [HardwareID], 
    f.created_at AS CreatedAt,
    ISNULL(b.serial, NULL) AS [DiskSerial],
    ISNULL(b.model, NULL) AS [DiskModel],
    ISNULL(b.vendor, NULL) AS [DiskMake],
    ISNULL(b.type_display, NULL) AS [DiskType],
    ISNULL(b.transport, NULL) AS [DiskTransport],
    ISNULL(b.status_display, NULL) AS [DiskStatus],
    ISNULL(b.capacity_display, NULL) AS [DiskCapacity],
    ISNULL(CONVERT(DATETIME, b.erase_finish_time AT TIME ZONE 'UTC'), NULL) AS [DiskEraseFinishTime],
    ISNULL(b.device_id, NULL) AS [DiskEraseID],
    ISNULL(b.erase_algorithm_display, NULL) AS [DiskEraseMethod],
    ISNULL(b.erase_level, NULL) AS [DiskEraseLevel],
    ISNULL(CONVERT(DATETIME, b.erase_start_time AT TIME ZONE 'UTC'), NULL) AS [DiskEraseStartTime],
    ISNULL(b.erase_result_display, NULL) AS [DiskEraseResult]
FROM filtered_hardware f
LEFT JOIN [LinkedServerName].[DatabaseName].[public].[disk_device] b 
    ON f.hardware_id = b.hardware_id

原因分析 #

1. WHERE子句过滤的问题：Linked Server在处理跨库LEFT JOIN+WHERE过滤时，可能无法正确生成PostgreSQL端的执行计划，导致关联匹配逻辑异常，仅首条记录能正确关联。
2. ON子句放过滤条件的问题：LEFT JOIN的ON条件仅用于关联匹配，不会过滤主表数据，因此主表仍会返回不符合时间条件的行，只是关联的disk_device字段为空，导致总条数远超预期。

通过先过滤主表再关联，确保过滤逻辑在PostgreSQL端执行，返回的结果集再与disk_device表关联，即可同时满足时间过滤和关联字段正常填充的需求。

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