COALESCE - 保证短路?

2021-09-10 00:00:00 sql short-circuiting tsql sql-server coalesce

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

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

例如，在大多数语言的函数中，参数会被完全评估，然后被传递到函数中.在 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函数"的限制:

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 时评估第二种情况，我希望看到如下错误:

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.

我发现了一些提及 与 Oracle 相关.还有一些使用 SQL 服务器.当您包含用户定义的函数时，看起来短路可能会中断.

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 标准是否应该保证这种行为?

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

推荐答案

我刚刚看了链接的文章，可以确认 COALESCE 和 ISNULL 的短路都可能失败.

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

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

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

这是一些适用于基于标量的函数的证明(我在 SQL Server 2005 上运行它):

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

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

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.

相关文章