SQL Server优化器移除ISNULL调用引发geography方法空参数报错

以下查询在SQL Server 2017中运行时报错：

;with locations(RowNum, Latitude, Longitude) as (
    select 1, 12.3456, 45.6789
),
locationsWithPrevious as (
    select  *,
            PreviousLatitude = lag(l.Latitude) over(order by l.RowNum),
            PreviousLongitude = lag(l.Longitude) over(order by l.RowNum)
    from    locations as l
),
locationsWithDistance as (
    select  *,
            Distance = geography::Point(l.Latitude, l.Longitude, 4326).STDistance(geography::Point(l.PreviousLatitude, l.PreviousLongitude, 4326))
    from    locationsWithPrevious as l
    where   PreviousLatitude is not null
        and PreviousLongitude is not null
)
select  *
from    locationsWithDistance as l
where   Distance > 0

报错信息：

Msg 6569, Level 16, State 1, Line 1
'geography::Point' failed because parameter 1 is not allowed to be null.

已知报错根因是Distance > 0谓词的执行顺序早于PreviousLatitude/PreviousLongitude的非空过滤逻辑，属于SQL Server作为声明式语言的正常行为，执行顺序由优化器自行决定。
为了避免空值传入，将Distance计算逻辑修改为：

Distance = geography::Point(l.Latitude, l.Longitude, 4326).STDistance(geography::Point(isnull(l.PreviousLatitude, 0), isnull(l.PreviousLongitude, 0), 4326))

但修改后仍然报同样的错误，执行计划中完全没有ISNULL的处理逻辑，看起来优化器基于非空过滤条件错误移除了ISNULL调用，请问该行为是否符合预期？

补充测试现象：

• 移除非空过滤条件后报错消失，ISNULL函数会正常出现在执行计划中，不过查询语义也发生了变化
• 将ISNULL替换为等价的CASE WHEN逻辑后查询可以正常运行
• 提前实例化geography Point对象的写法也可以正常运行，且性能更优

这个行为完全符合SQL Server当前的设计预期，属于查询优化器的逻辑推导优化与谓词下推逻辑交互的典型结果，核心原因如下：

• 优化器内置的语义推导逻辑会判定：你已经在CTE的WHERE条件中明确过滤了所有PreviousLatitude和PreviousLongitude为NULL的行，那么ISNULL(PreviousLatitude, 0)的默认值分支永远不会触发，等价于直接引用PreviousLatitude，所以会直接将ISNULL调用从执行计划中移除，以此减少冗余计算。
• 但优化器做上述推导时，没有和谓词下推的执行顺序逻辑做联动校验：它会将外层的Distance > 0过滤谓词下推到和CTE内部的非空判断同层执行，甚至可能早于非空判断执行，此时还没有过滤掉NULL行，而ISNULL已经被移除，自然就会出现传入NULL值给geography::Point的报错。
• 替换为CASE WHEN可以正常运行的原因是：SQL Server对CASE WHEN的短路执行逻辑有更严格的语义保护，不会随意移除其分支判断逻辑，所以即使存在非空过滤条件，NULL值判断的分支仍然会保留，避免了空值传入的问题。

最优解决方案 #

最推荐使用提前实例化geography对象的写法：

;with locations(RowNum, GeoPosition) as (
    select 1, geography::Point(12.3456, 45.6789, 4326)
),
locationsWithPrevious as (
    select  *,
            PreviousGeoPosition = lag(l.GeoPosition) over(order by l.RowNum)
    from    locations as l
),
locationsWithDistance as (
    select  *,
            Distance = l.GeoPosition.STDistance(l.PreviousGeoPosition)
    from    locationsWithPrevious as l
    where   PreviousGeoPosition is not null
)
select  *
from    locationsWithDistance as l
where   Distance > 0

该写法既规避了优化器的逻辑陷阱，也不需要重复构造Point对象，执行效率更高，语义也更清晰。

内容的提问来源于stack exchange，提问作者E. T.