Czy SQL Server odczytuje wszystkie funkcje COALESCE, nawet jeśli pierwszy argument nie ma wartości NULL?

Korzystam z funkcji T-SQL, w COALESCEktórej pierwszy argument nie będzie miał wartości null w około 95% razy, gdy jest uruchamiany. Jeśli pierwszym argumentem jest NULL, drugi argument jest dość długim procesem:

SELECT COALESCE(c.FirstName
                ,(SELECT TOP 1 b.FirstName
                  FROM TableA a 
                  JOIN TableB b ON .....)
                )

Jeśli, na przykład, c.FirstName = 'John'czy SQL Server nadal uruchamia pod-zapytanie?

Wiem z IIF()funkcją VB.NET , jeśli drugi argument ma wartość True, kod nadal czyta trzeci argument (nawet jeśli nie zostanie użyty).

Nope . Oto prosty test:

SELECT COALESCE(1, (SELECT 1/0)) -- runs fine
SELECT COALESCE(NULL, (SELECT 1/0)) -- throws error

Jeśli drugi warunek jest oceniany, generowany jest wyjątek dla dzielenia przez zero.

Zgodnie z dokumentacją MSDN jest to związane z tym, jak COALESCEinterpreter jest widziany - to tylko prosty sposób na napisanie CASEinstrukcji.

CASE jest dobrze znana jako jedna z niewielu funkcji w SQL Server, która (głównie) niezawodnie zwiera.

Istnieją pewne wyjątki w porównaniu ze zmiennymi skalarnymi i agregacjami, jak pokazał Aaron Bertrand w innej odpowiedzi tutaj (dotyczy to zarówno CASEi COALESCE):

DECLARE @i INT = 1;
SELECT CASE WHEN @i = 1 THEN 1 ELSE MIN(1/0) END;

wygeneruje podział przez błąd zero.

Powinno to być traktowane jako błąd i z reguły COALESCEbędzie analizowane od lewej do prawej.

Co powiesz na ten - jak mi to powiedział Itzik Ben-Gan, któremu opowiedział o tym Jaime Lafargue ?

DECLARE @i INT = 1;
SELECT CASE WHEN @i = 1 THEN 1 ELSE MIN(1/0) END;

Wynik:

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

Istnieją oczywiście trywialne obejścia, ale nadal chodzi o to, że CASEnie zawsze gwarantuje to ocenę / zwarcie od lewej do prawej. Zgłosiłem błąd tutaj i został on zaprojektowany jako „z założenia”. Następnie Paul White złożył przedmiot Connect , który został zamknięty jako Naprawiony. Nie dlatego, że został naprawiony per se, ale dlatego, że zaktualizowali Books Online o dokładniejszy opis scenariusza, w którym agregaty mogą zmienić kolejność oceny CASEwyrażenia. Ostatnio pisałem o tym więcej na blogu .

EDYTUJ tylko dodatek, podczas gdy zgadzam się, że są to przypadki skrajne, że przez większość czasu możesz polegać na ocenie od lewej do prawej i zwarciach oraz że są to błędy, które są sprzeczne z dokumentacją i prawdopodobnie zostaną ostatecznie naprawione ( to nie jest jednoznaczne - zobacz dalszą rozmowę na blogu Barta Duncana, aby dowiedzieć się, dlaczego), muszę się nie zgodzić, gdy ludzie mówią, że coś jest zawsze prawdziwe, nawet jeśli istnieje jeden przypadek, który to obala. Jeśli Itzik i inni mogą znaleźć takie pojedyncze błędy, to przynajmniej w sferze prawdopodobieństwa, że ​​są też inne błędy. A ponieważ nie znamy reszty zapytania OP, nie możemy z całą pewnością stwierdzić, że będzie polegał na tym zwarciu, ale w końcu zostanie ugryziony. Więc dla mnie bezpieczniejsza odpowiedź to:

Chociaż zwykle można polegać na CASEocenie zwarcia od lewej do prawej i zwarcia, jak opisano w dokumentacji, nie jest właściwe stwierdzenie, że zawsze można to zrobić. Na tej stronie pokazano dwa przypadki, w których nie jest to prawda, i żaden błąd nie został naprawiony w żadnej publicznie dostępnej wersji programu SQL Server.

EDYCJA tutaj to kolejny przypadek (muszę przestać to robić), w którym CASEwyrażenie nie ocenia się w oczekiwanej kolejności, nawet jeśli nie są w to zaangażowane agregacje.

Uważam, że w dokumentacji jest dość jasne, że zamiarem CASE powinno być zwarcie. Jak wspomina Aaron, w kilku przypadkach (ha!) Okazało się, że nie zawsze jest to prawdą.

Jak dotąd wszystkie te zostały uznane za błędy i naprawione - choć niekoniecznie w wersji SQL Server, którą można dziś kupić i załatać (błąd ciągłego składania nie dotarł jeszcze do aktualizacji zbiorczej AFAIK). Najnowszy potencjalny błąd - pierwotnie zgłoszony przez Itzika Ben-Gana - nie został jeszcze zbadany (albo Aaron, albo ja wkrótce go dodam do Connect).

Związane z pierwotnym pytaniem, istnieją inne problemy z CASE (a zatem COALESCE), w których używane są funkcje uboczne lub podzapytania. Rozważać:

SELECT COALESCE((SELECT CASE WHEN RAND() <= 0.5 THEN 999 END), 999);
SELECT ISNULL((SELECT CASE WHEN RAND() <= 0.5 THEN 999 END), 999);

Formularz COALESCE często zwraca NULL, więcej szczegółów na https://connect.microsoft.com/SQLServer/feedback/details/546437/coalesce-subquery-1-may-return-null

Wykazane problemy z transformatorami optymalizacyjnymi i śledzeniem wyrażeń pospolitych oznaczają, że nie można zagwarantować, że CASE będzie zwierać w każdych okolicznościach. Potrafię sobie wyobrazić przypadki, w których może nie być nawet możliwe przewidzenie zachowania poprzez sprawdzenie wyników publicznego planu koncertów, choć nie mam dziś na to repozytorium.

Podsumowując, myślę, że można mieć całkowitą pewność, że CASE spowoduje zwarcie w ogóle (szczególnie jeśli osoba posiadająca odpowiednie kwalifikacje sprawdzi plan wykonania i że plan wykonania jest „egzekwowany” za pomocą przewodnika planu lub wskazówek), ale jeśli potrzebujesz absolutna gwarancja, że ​​musisz napisać SQL, który w ogóle nie zawiera wyrażenia.

Chyba niezbyt zadowalający stan rzeczy.

Natknąłem się na inny przypadek, w którym CASE/ COALESCEnie zwierać. Następujące TVF podniesie naruszenie PK, jeśli zostanie przekazane 1jako parametr.

CREATE FUNCTION F (@P INT)
RETURNS @T TABLE (
  C INT PRIMARY KEY)
AS
  BEGIN
      INSERT INTO @T
      VALUES      (1),
                  (@P)

      RETURN
  END

Jeśli zostanie wywołany w następujący sposób

DECLARE @Number INT = 1

SELECT COALESCE(@Number, (SELECT number
                          FROM   master..spt_values
                          WHERE  type = 'P'
                                 AND number = @Number), 
                         (SELECT TOP (1)  C
                          FROM   F(@Number))) 

Lub jak

DECLARE @Number INT = 1

SELECT CASE
         WHEN @Number = 1 THEN @Number
         ELSE (SELECT TOP (1) C
               FROM   F(@Number))
       END 

Oba dają wynik

Naruszenie podstawowego KLUCZOWEGO ograniczenia „PK__F__3BD019A800551192”. Nie można wstawić duplikatu klucza w obiekcie „dbo. @ T”. Zduplikowana wartość klucza to (1).

pokazując, że SELECT(lub przynajmniej populacja zmiennej tabeli) jest nadal wykonywana i powoduje błąd, nawet jeśli ta gałąź instrukcji nigdy nie powinna zostać osiągnięta. Plan COALESCEwersji znajduje się poniżej.

To przepisanie zapytania wydaje się unikać problemu

SELECT COALESCE(Number, (SELECT number
                          FROM   master..spt_values
                          WHERE  type = 'P'
                                 AND number = Number), 
                         (SELECT TOP (1)  C
                          FROM   F(Number))) 
FROM (VALUES(1)) V(Number)   

Co daje plan

Inny przykład

CREATE TABLE T1 (C INT PRIMARY KEY)

CREATE TABLE T2 (C INT PRIMARY KEY)

INSERT INTO T1 
OUTPUT inserted.* INTO T2
VALUES (1),(2),(3);

Zapytanie

SET STATISTICS IO ON;

SELECT T1.C,
       COALESCE(T1.C , CASE WHEN EXISTS (SELECT * FROM T2 WHERE T2.C = T1.C)  THEN -1 END)
FROM T1
OPTION (LOOP JOIN)

W ogóle nie pokazuje odczytów T2.

Wyszukiwanie T2odbywa się w oparciu o predykat, a operator nigdy nie jest wykonywany. Ale

SELECT T1.C,
       COALESCE(T1.C , CASE WHEN EXISTS (SELECT * FROM T2 WHERE T2.C = T1.C)  THEN -1 END)
FROM T1
OPTION (MERGE JOIN)

Nie wynika, że T2jest odczytywana. Mimo że nigdy nie T2jest potrzebna żadna wartość z .

Oczywiście nie jest to zaskakujące, ale pomyślałem, że warto dodać do repozytorium licznika, choćby dlatego, że podnosi on kwestię tego, co oznacza zwarcie w deklaratywnym języku opartym na zestawie.

Chciałem tylko wspomnieć o strategii, której być może nie rozważałeś. Tutaj może nie pasować, ale czasem się przydaje. Sprawdź, czy ta modyfikacja zapewnia lepszą wydajność:

SELECT COALESCE(c.FirstName
            ,(SELECT TOP 1 b.FirstName
              FROM TableA a 
              JOIN TableB b ON .....
              WHERE C.FirstName IS NULL) -- this is the changed part
            )

Innym sposobem na to może być to (w zasadzie równoważne, ale w razie potrzeby umożliwia dostęp do większej liczby kolumn z drugiego zapytania):

SELECT COALESCE(c.FirstName, x.FirstName)
FROM
   TableC c
   OUTER APPLY (
      SELECT TOP 1 b.FirstName
      FROM
         TableA a 
         JOIN TableB b ON ...
      WHERE
         c.FirstName IS NULL -- the important part
   ) x

Zasadniczo jest to technika „twardego” łączenia tabel, ale uwzględniająca warunek, kiedy w ogóle należy połączyć wszystkie wiersze. Z mojego doświadczenia wynika, że ​​czasami pomogło to w realizacji planów.

Nie zrobiłby tego. Działa tylko wtedy, gdy c.FirstNamejest NULL.

Powinieneś jednak spróbować sam. Eksperyment. Powiedziałeś, że twoje podzapytanie jest długie. Reper. Wyciągnij własne wnioski na ten temat.

@Aaron Odpowiedź na uruchamiane zapytanie jest bardziej kompletna.

Jednak nadal uważam, że powinieneś przerobić swoje zapytanie i użyć LEFT JOIN. W większości przypadków pod-zapytania można usunąć, przerabiając je pod kątem użycia LEFT JOINs.

Problem z używaniem zapytań podrzędnych polega na tym, że ogólna instrukcja będzie działać wolniej, ponieważ zapytanie podrzędne jest uruchamiane dla każdego wiersza w zestawie wyników zapytania głównego.

Rzeczywisty standard mówi, że wszystkie klauzule WHEN (jak również ELSE) muszą zostać przeanalizowane, aby określić typ danych wyrażenia jako całości. Naprawdę musiałbym wydostać się z niektórych moich starych notatek, aby ustalić, jak radzić sobie z błędem. Ale tuż pod ręką 1/0 używa liczb całkowitych, więc zakładam, że chociaż jest to błąd. Jest to błąd związany z typem danych liczb całkowitych. Gdy na liście koalescencji znajdują się tylko wartości null, określenie typu danych jest nieco trudniejsze, a to kolejny problem.