sqlserver - COALESCE: ¿está garantizado un cortocircuito?

sql server case when multiple

Question

A partir de esta pregunta , una respuesta clara sobre el uso de COALESCE para simplificar los árboles de lógica complejos. Consideré el problema del cortocircuito.

Por ejemplo, en funciones en la mayoría de los lenguajes, los argumentos se evalúan completamente y luego se pasan a la función. Cª:

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

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

Eso no parece ser una limitación de la "función" COALESCE en 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

Si estuviera evaluando el segundo caso cuando Denominator = 0, esperaría ver un error como:

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

Encontré algunas menciones relacionadas con Oracle. Y algunas pruebas con SQL Server . Parece que el cortocircuito podría romperse cuando incluye funciones definidas por el usuario.

Entonces, ¿se supone que este comportamiento está garantizado por el estándar ANSI?

Answer 1

La forma eficiente de garantizar el cortocircuito en MS SQL Server es usar CASE. Para la cláusula WHEN de éxito, no se evalúan otros.

COALESCE puede tener problemas

En este caso, ¿por qué tener tantas ramas en los constructivos COALESCE / CASE?

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

Answer 2

¡También me sorprendió ver que la respuesta funciona! No estoy seguro de que este comportamiento esté garantizado. (¡Pero no he podido encontrar un ejemplo que no funcione!)

Cinco años de SQL , y todavía estoy sorprendido.

También seguí adelante e hice un cambio más:

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

El resultado que obtuve fue:

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

¡Ahora estoy aún más confundido! Para el caso en que num = 0 y den = 0, ¿cómo obtuve testcalc como 0 (especialmente porque eliminé el 0 después del último caso)?

Answer 3

Acabo de echar un vistazo al artículo vinculado y puedo confirmar que el cortocircuito puede fallar tanto para COALESCE como para ISNULL.

Parece que falla si tiene alguna sub consulta involucrada, pero funciona bien para funciones escalares y valores codificados.

Por ejemplo,

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 se implementa de acuerdo con el estándar ANSI . Es simplemente una abreviatura de una declaración CASE. ISNULL no es parte del estándar ANSI. La Sección 6.9 no parece requerir un cortocircuito explícitamente, pero sí implica que se debe devolver la primera cláusula verdadera en la instrucción when .

Aquí hay algunas pruebas de que funciona para funciones basadas en escalares (lo ejecuté en 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

Aquí hay alguna prueba de que la implementación subyacente con CASE ejecutará sub consultas.

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.