問(wèn)題描述

來(lái)自這個(gè)問(wèn)題, 關(guān)于使用的簡(jiǎn)潔答案COALESCE 來(lái)簡(jiǎn)化復(fù)雜的邏輯樹(shù).我考慮過(guò)短路的問(wèn)題.

From this question, a neat answer about using COALESCE to simplify complex logic trees. I considered the problem of short circuiting.

例如，在大多數(shù)語(yǔ)言的函數(shù)中，參數(shù)會(huì)被完全評(píng)估，然后被傳遞到函數(shù)中.在 C:

For instance, in functions in most languages, arguments are fully evaluated and are then passed into the function. In C:

int f(float x, float y) {
    return x;
}

f(a, a / b) ; // This will result in an error if b == 0

這似乎不是 SQL Server 中 COALESCE函數(shù)"的限制:

That does not appear to be a limitation of the COALESCE "function" in SQL Server:

CREATE TABLE Fractions (
    Numerator float
    ,Denominator float
)

INSERT INTO Fractions VALUES (1, 1)
INSERT INTO Fractions VALUES (1, 2)
INSERT INTO Fractions VALUES (1, 3)
INSERT INTO Fractions VALUES (1, 0)
INSERT INTO Fractions VALUES (2, 0)
INSERT INTO Fractions VALUES (3, 0)

SELECT Numerator
    ,Denominator
    ,COALESCE(
        CASE WHEN Denominator = 0 THEN 0 ELSE NULL END,
        CASE WHEN Numerator <> 0 THEN Numerator / Denominator ELSE NULL END,
        0
    ) AS TestCalc
FROM Fractions

DROP TABLE Fractions

如果它在 Denominator = 0 時(shí)評(píng)估第二種情況，我希望看到如下錯(cuò)誤:

If it were evaluating the second case when Denominator = 0, I would expect to see an error like:

Msg 8134, Level 16, State 1, Line 1
Divide by zero error encountered.

我發(fā)現(xiàn)了一些提及 與 Oracle 相關(guān).還有一些使用 SQL 服務(wù)器.當(dāng)您包含用戶(hù)定義的函數(shù)時(shí)，看起來(lái)短路可能會(huì)中斷.

I found some mentions related to Oracle. And some tests with SQL Server. Looks like the short-circuiting might break down when you include user-defined functions.

那么，ANSI 標(biāo)準(zhǔn)是否應(yīng)該保證這種行為?

So, is this behavior supposed to be guaranteed by the ANSI standard?

推薦答案

我剛剛看了鏈接的文章，可以確認(rèn) COALESCE 和 ISNULL 的短路都可能失敗.

I just had a look at the linked article and can confirm short circuiting can fail for both COALESCE and ISNULL.

如果涉及任何子查詢(xún)，它似乎會(huì)失敗，但它適用于標(biāo)量函數(shù)和硬編碼值.

It seems to fail if you have any sub-query involved, but it works fine for scalar functions and hard coded values.

例如

DECLARE @test INT
SET @test = 1
PRINT 'test2'
SET @test = COALESCE(@test, (SELECT COUNT(*) FROM sysobjects))
SELECT 'test2', @test
-- OUCH, a scan through sysobjects

COALESCE 根據(jù) ANSI 標(biāo)準(zhǔn)實(shí)現(xiàn).它只是 CASE 語(yǔ)句的簡(jiǎn)寫(xiě).ISNULL 不是 ANSI 標(biāo)準(zhǔn)的一部分.第 6.9 節(jié)似乎沒(méi)有明確要求短路，但它確實(shí)暗示應(yīng)該返回 when 語(yǔ)句中的第一個(gè) true 子句.

COALESCE is implemented according to the ANSI standard. It is simply a shorthand for a CASE statement. ISNULL is not part of the ANSI standard. Section 6.9 does not seem to require short circuiting explicitly, but it does imply that the first true clause in the when statement should be returned.

這是一些適用于基于標(biāo)量的函數(shù)的證明(我在 SQL Server 2005 上運(yùn)行它):

Here is some proof that is works for scalar based functions (I ran it on SQL Server 2005):

CREATE FUNCTION dbo.evil
(
)
RETURNS int
AS
BEGIN
    -- Create an huge delay
    declare @c int
    select @c = count(*) from sysobjects a
    join sysobjects b on 1=1
    join sysobjects c on 1=1
    join sysobjects d on 1=1
    join sysobjects e on 1=1
    join sysobjects f on 1=1
    return @c / 0
END
go

select dbo.evil()
-- takes forever

select ISNULL(1,  dbo.evil())
-- very fast

select COALESCE(1,  dbo.evil())
-- very fast

這里有一些證據(jù)表明 CASE 的底層實(shí)現(xiàn)將執(zhí)行子查詢(xún).

Here is some proof that the underlying implementation with CASE will execute sub queries.

DECLARE @test INT
SET @test = 1
select
    case
        when @test is not null then @test
        when @test = 2 then (SELECT COUNT(*) FROM sysobjects)
        when 1=0 then (SELECT COUNT(*) FROM sysobjects)
        else (SELECT COUNT(*) FROM sysobjects)
    end
-- OUCH, two table scans. If 1=0, it does not result in a table scan.

這篇關(guān)于COALESCE - 保證短路?的文章就介紹到這了，希望我們推薦的答案對(duì)大家有所幫助，也希望大家多多支持html5模板網(wǎng)！