代码之家 › 专栏 › 技术社区 › Cade Roux

合并-保证短路?

short-circuiting coalesce tsql sql-server sql

Cade Roux · 技术社区 · 15 年前

从…起 this question a neat answer about using COALESCE 简化复杂的逻辑树。我考虑了短路问题。

例如,在大多数语言的函数中,参数是完全求值的,然后传递到函数中。在C中:

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

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

COALESCE

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.

我找到了一些 mentions related 致甲骨文。还有一些测试 SQL Server . 当包含用户定义的函数时,短路可能会中断。

3 回复 | 直到 7 年前

Peter Mortensen mkoryak 12 年前

我刚刚看了一篇链接文章,可以确认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

合并是根据 ANSI standard . 这只是一个案例陈述的简写。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.

gbn 15 年前

这个 有效率的 对于successwhen子句,不评估其他子句。

COALESCE can have issues

SELECT Numerator
    ,Denominator
    ,CASE
        WHEN Denominator = 0 THEN 0 END,
        ELSE Numerator / Denominator
     END AS TestCalc
FROM Fractions

Peter Mortensen mkoryak 12 年前

我也惊讶地发现这个答案是有效的!我不确定这种行为是否有保证。(但我没有找到一个不起作用的例子!)

五年 SQL ,我还是很惊讶。

我还做了一个改变:

INSERT INTO #Fractions VALUES (0, 0)

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

我得到的结果是:

Numerator   Denominator TestCalc
1             1           1
1             2           0.5
1             3           0.3333333333333335
1             0           0
2             0           0
3             0           0
0             0           0