COALESCE - 保证短路？

从这个问题,关于使用COALESCE一个整洁的答案来简化复杂的逻辑树.我考虑过短路问题.

例如,在大多数语言的函数中,参数被完全评估,然后传递给函数.在C:

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

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

这似乎不是COALESCESQL 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

如果它正在评估第二个情况下,当分母= 0,我希望看到这样的错误:

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

我发现了一些与 Oracle 有关的提及 .还有一些SQL Server测试.当您包含用户定义的函数时,看起来短路可能会崩溃.

那么,这种行为是否应该由ANSI标准保证？

我刚看了一下链接的文章,可以确认COALESCE和ISNULL的短路都会失败.

如果您涉及任何子查询,它似乎失败,但它适用于标量函数和硬编码值.

例如,

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根据ANSI标准实施.它只是CASE语句的简写.ISNULL不是ANSI标准的一部分.第6.9节似乎没有明确要求短路,但它确实意味着when应该返回语句中的第一个真正的子句.

以下是一些适用于基于标量的函数的证明(我在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

这里有一些证明,CASE的底层实现将执行子查询.

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.