SQLServerOptimizer洗掉ISNULL呼叫

以下查詢在 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/-Longitude到之前執行IS NOT NULL。到目前為止一切順利，因為 T-SQL 是宣告式的，這里的操作順序可以由 SQL Server 確定。如果洗掉 predicate Distance > 0，則查詢將正常作業而不會出錯。

但是現在我希望NULL可以通過使用ISNULL如下函式來防止引數的值：

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

但是查詢仍然回傳相同的錯誤！該ISNULL函式也沒有在執行計劃的過濾謂詞中的任何地方列出！

SQL Server 的這種行為是否正確？在我看來，SQL ServerISNULL由于IS NOT NULL過濾而錯誤地洗掉了呼叫。

筆記：

1. 當IS NOT NULL條件被移除時，錯誤消失，因為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
   

uj5u.com熱心網友回復：

它確實似乎是一個錯誤。編譯器認為該值可證明不為空，并洗掉ISNULL. 但是，COALESCE不會以相同的方式受到影響，它會編譯為CASE并且編譯器對其沒有那么多的可見性。

編譯器將中間計算放入Compute Scalar運算子中。但是這些可以由 Expression Service 在不同點計算，因此ISNULL不應該被洗掉。

正如您所發現的，一種解決方法是洗掉WHERE.

另一種是使用額外的引數 onLAG添加一個默認值

;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

資料庫<>小提琴

uj5u.com熱心網友回復：

我認為這是通過公共表運算式鏈接太多邏輯的癥狀。我經常發現自己通過像這樣的 #hash 表打破了鏈，僅僅是因為它更容易除錯，也更容易理解。

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;

標籤：sql sql-server 查询语句

上一篇：重復行值

下一篇：SQLServerExpress資料庫實體連接錯誤