SQL Server Optimizer删除ISNULL调用

Question

以下查询在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.

原因：谓词CCD_ 1是在将PreviousLatitude/-Longitude过滤为IS NOT NULL之前执行的。到目前为止还不错，因为T-SQL是声明性的，这里的操作顺序可以由SQL Server决定。如果删除谓词Distance > 0，则查询不会出错。

但现在我希望通过使用ISNULL函数可以防止参数的NULL值，如下所示：

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

但是查询仍然返回相同的错误！ISNULL函数也没有在执行计划的筛选器谓词中的任何位置列出！

SQL Server的这种行为正确吗？在我看来，SQL Server由于IS NOT NULL筛选而错误地删除了ISNULL调用。

注：

1. 当Distance > 00条件被删除时，错误消失了，因为ISNULL函数现在按预期在过滤器谓词中使用(当然，查询在语义上发生了变化(：

   locationsWithDistance as (
   select  *,
   Distance = geography::Point(l.Latitude, l.Longitude, 4326).STDistance(geography::Point(isnull(l.PreviousLatitude, 0), isnull(l.PreviousLongitude, 0), 4326))
   from    locationsWithPrevious as l
   )
   

2. 但是，如果将ISNULL调用替换为CASE WHEN操作，则查询可以正常工作：

   Distance = geography::Point(l.Latitude, l.Longitude, 4326).STDistance(geography::Point(case when l.PreviousLatitude is not null then l.PreviousLatitude else 0 end, case when l.PreviousLongitude is not null then l.PreviousLongitude else 0 end, 4326))
   

3. 我还意识到，通过直接在基本查询中实例化Point，可以更好地将查询公式化为：

   ;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
   

Answer 1

这确实是一个bug编译器认为该值可证明不是null，并删除ISNULL。然而，COALESCE并没有受到同样的影响，它编译到CASE，编译器对它没有那么多的可见性

编译器将中间计算放入Compute Scalar运算符中。但是，表达式服务可以在不同的点计算这些值，因此ISNULL不应该被删除。

正如您所发现的，一种解决方法是删除WHERE。

另一种是使用LAG上的额外参数来添加默认

;with locations(RowNum, Latitude, Longitude) as (
select 1, 12.3456, 45.6789
),
locationsWithPrevious as (
select  *,
PreviousLatitude = lag(l.Latitude, 1, 0) over(order by l.RowNum),
PreviousLongitude = lag(l.Longitude, 1, 0) 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
)
select  *
from    locationsWithDistance as l
where   Distance > 0

db<gt；小提琴

Answer 2

我认为这是通过公共表表达式链接过多逻辑的症状。我经常发现自己通过这样的#哈希表打破了链条，因为它更容易调试，也更容易理解。

drop table if exists #locationsWithPrevious;
;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
)
select *
into #locationsWithPrevious
from locationsWithPrevious
where   PreviousLatitude is not null
and PreviousLongitude is not null;
with locationsWithDistance as (
select  *,
Distance = geography::Point(l.Latitude, l.Longitude, 4326).STDistance(geography::Point(l.PreviousLatitude, l.PreviousLongitude, 4326))
from    #locationsWithPrevious as l
)
select  *
from    locationsWithDistance as l
where   Distance > 0;