COALESCE - гарантируется короткое замыкание?

Из этот вопрос, аккуратный ответ об использовании COALESCE, чтобы упростить сложные деревья логики. Я рассмотрел проблему короткого замыкания.

Например, в функциях большинства языков аргументы полностью оцениваются и затем передаются в функцию. В C:

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

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

Это не является ограничением функции COALESCE "в 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, я ожидал бы увидеть ошибку, например:

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

Я нашел упоминает связанный с Oracle, И некоторые тесты с SQL Server. Похоже, короткое замыкание может сломаться при включении пользовательских функций.

Итак, должно ли это поведение гарантироваться стандартом ANSI?

Ответ 1

Я только что просмотрел связанную статью и могу подтвердить, что короткое замыкание может завершиться как для 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.

Ответ 2

Эффективным способом обеспечения короткого замыкания в MS SQL Server является использование CASE. Для выполнения предложения WHEN другие оценки не оцениваются.

У COALESCE могут быть проблемы

В этом случае, почему так много ветвей в конструкциях COALESCE/CASE?

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

Ответ 3

Я также был удивлен, увидев, что ответ работает! Я не уверен, что это поведение гарантировано. (Но я не смог найти пример, который не работает!)

Пять лет 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

Теперь я еще более смущен! Для случая, когда num = 0 и den = 0, как я получил testcalc как 0 (тем более, что я удалил 0 после последнего случая!)?