SQL Server dzieli A <> B na A <B LUB A> B, dając dziwne wyniki, jeśli B jest niedeterministyczny

Napotkaliśmy ciekawy problem z programem SQL Server. Rozważ następujący przykład repro:

CREATE TABLE #test (s_guid uniqueidentifier PRIMARY KEY);
INSERT INTO #test (s_guid) VALUES ('7E28EFF8-A80A-45E4-BFE0-C13989D69618');

SELECT s_guid FROM #test
WHERE s_guid = '7E28EFF8-A80A-45E4-BFE0-C13989D69618'
  AND s_guid <> NEWID();

DROP TABLE #test;

skrzypce

Proszę na chwilę zapomnieć, że s_guid <> NEWID()warunek wydaje się całkowicie bezużyteczny - to tylko minimalny przykład repro. Ponieważ prawdopodobieństwo NEWID()dopasowania określonej stałej wartości jest bardzo małe, za każdym razem należy oceniać na PRAWDA.

Ale tak nie jest. Uruchomienie tego zapytania zwykle zwraca 1 wiersz, ale czasami (dość często, więcej niż 1 raz na 10) zwraca 0 wierszy. Reprodukowałem go z SQL Server 2008 w moim systemie i możesz go odtworzyć on-line ze skrzypkiem połączonym powyżej (SQL Server 2014).

Analiza planu wykonania ujawnia, że ​​analizator zapytań najwyraźniej dzieli warunek na s_guid < NEWID() OR s_guid > NEWID():

... co całkowicie wyjaśnia, dlaczego czasami zawodzi (jeśli pierwszy wygenerowany identyfikator jest mniejszy, a drugi większy niż podany identyfikator).

Czy SQL Server może oceniać A <> Bjako A < B OR A > B, nawet jeśli jedno z wyrażeń jest niedeterministyczne? Jeśli tak, gdzie to jest udokumentowane? Czy znaleźliśmy błąd?

Co ciekawe, AND NOT (s_guid = NEWID())daje ten sam plan wykonania (i ten sam losowy wynik).

Znaleźliśmy ten problem, gdy programista chciał opcjonalnie wykluczyć określony wiersz i użył:

s_guid <> ISNULL(@someParameter, NEWID())

jako „skrót” dla:

(@someParameter IS NULL OR s_guid <> @someParameter)

Szukam dokumentacji i / lub potwierdzenia błędu. Kod nie jest tak istotny, więc obejścia nie są wymagane.

Czy SQL Server może oceniać A <> Bjako A < B OR A > B, nawet jeśli jedno z wyrażeń jest niedeterministyczne?

Jest to nieco kontrowersyjny punkt, a odpowiedź brzmi „tak”.

Najlepsza znana mi dyskusja została udzielona w odpowiedzi na raport o błędzie Connect Itzika Ben-Gana Błąd z NEWID i wyrażeniami tabelowymi , który został zamknięty, ponieważ nie zostanie naprawiony. Connect został wycofany, więc znajduje się tam link do archiwum internetowego. Niestety, wiele przydatnych materiałów zostało utraconych (lub utrudnionych do znalezienia) przez upadek Connect. W każdym razie najbardziej przydatne cytaty z Jim Hogg z Microsoft są:

To uderza w sedno problemu - czy optymalizacja może zmienić semantykę programu? Tj .: jeśli program daje pewne odpowiedzi, ale działa wolno, czy jest to uzasadnione, aby Optymalizator zapytań przyspieszył działanie tego programu, a jednocześnie zmienił podane wyniki?

Zanim krzykniesz „NIE!” (moja osobista skłonność też :-), zastanów się: dobrą wiadomością jest to, że w 99% przypadków odpowiedzi są takie same. Optymalizacja zapytań jest więc wyraźną wygraną. Zła wiadomość jest taka, że ​​jeśli zapytanie zawiera kod wywołujący skutki uboczne, wówczas różne plany MOGĄ rzeczywiście dać różne wyniki. NEWID () to jedna z takich efektów ubocznych (niedeterministyczna), która ujawnia różnicę. [W rzeczywistości, jeśli eksperymentujesz, możesz opracować inne - na przykład, ocenę zwarcia klauzul AND: spraw, aby druga klauzula generowała arytmetyczny podział przez zero - różne optymalizacje mogą wykonać tę drugą klauzulę PRZED pierwszą klauzulą] To odzwierciedla Wyjaśnienie Craiga, gdzie indziej w tym wątku, że SqlServer nie gwarantuje wykonania operatorów skalarnych.

Mamy więc wybór: jeśli chcemy zagwarantować pewne zachowanie w obecności niedeterministycznego (powodującego skutki uboczne) kodu - tak aby wyniki JOIN na przykład podążały za semantyką wykonania zagnieżdżonej pętli - wtedy może użyć odpowiednich OPCJI, aby wymusić takie zachowanie - jak wskazuje UC. Ale wynikowy kod będzie działał wolno - taki jest koszt spowolnienia Optymalizatora zapytań.

To powiedziawszy, przesuwamy Optymalizator zapytań w kierunku zachowania „zgodnie z oczekiwaniami” dla NEWID () - zmniejszając wydajność dla „wyników zgodnie z oczekiwaniami”.

Jednym z przykładów zmiany zachowania w tym zakresie w czasie jest to, że NULLIF działa niepoprawnie z niedeterministycznymi funkcjami, takimi jak RAND () . Istnieją również inne podobne przypadki, w których stosuje się np. COALESCEPodzapytanie, które może dawać nieoczekiwane wyniki i które są również rozwiązywane stopniowo.

Jim kontynuuje:

Zamykanie pętli. . . Omówiłem to pytanie z zespołem deweloperów. I ostatecznie postanowiliśmy nie zmieniać obecnego zachowania z następujących powodów:

1) Optymalizator nie gwarantuje synchronizacji ani liczby realizacji funkcji skalarnych. To długo uznana zasada. Jest to podstawowa „swoboda”, która pozwala optymalizatorowi wystarczająco dużo swobody, aby uzyskać znaczną poprawę w realizacji planu zapytań.

2) To „zachowanie w jednym rzędzie” nie jest nowym zagadnieniem, chociaż nie jest szeroko omawiane. Zaczęliśmy poprawiać jego zachowanie z powrotem w wydaniu Yukon. Ale bardzo trudno jest dokładnie określić, we wszystkich przypadkach, dokładnie to, co to znaczy! Na przykład, czy dotyczy to wierszy tymczasowych obliczanych „w drodze” do wyniku końcowego? - w takim przypadku zależy to wyraźnie od wybranego planu. Czy może dotyczy to tylko wierszy, które ostatecznie pojawią się w ukończonym wyniku? - dzieje się tutaj paskudna rekurencja, jestem pewien, że się zgodzisz!

3) Jak wspomniałem wcześniej, domyślnie „optymalizujemy wydajność” - co jest dobre w 99% przypadków. 1% przypadków, w których może to zmienić wyniki, jest dość łatwy do wykrycia - powodujące skutki uboczne „funkcje”, takie jak NEWID, i łatwe do „naprawienia” (w konsekwencji handel perf). Ta domyślna „optymalizacja wydajności” ponownie ma długą tradycję i jest akceptowana. (Tak, nie jest to postawa wybierana przez kompilatory dla konwencjonalnych języków programowania, ale niech tak będzie).

Nasze rekomendacje to:

a) Unikaj polegania na nie gwarantowanym semantyce czasowej i liczbie wykonań. b) Unikaj używania NEWID () głęboko w wyrażeniach tabelowych. c) Użyj OPCJI, aby wymusić określone zachowanie (handel perf)

Mam nadzieję, że to wyjaśnienie pomoże wyjaśnić nasze przyczyny zamknięcia tego błędu, ponieważ „nie da się naprawić”.

Co ciekawe, AND NOT (s_guid = NEWID())daje ten sam plan wykonania

Jest to konsekwencja normalizacji, która dzieje się bardzo wcześnie podczas kompilacji zapytań. Oba wyrażenia kompilują się dokładnie w tej samej znormalizowanej formie, więc tworzony jest ten sam plan wykonania.

Jest to udokumentowane (w pewnym sensie) tutaj:

Liczba faktycznych wykonań funkcji określonej w zapytaniu może się różnić w zależności od planów wykonania zbudowanych przez optymalizator. Przykładem jest funkcja wywoływana przez podzapytanie w klauzuli WHERE. Liczba wykonywanych podzapytań i ich funkcji może się różnić w zależności od różnych ścieżek dostępu wybranych przez optymalizator.

Funkcje zdefiniowane przez użytkownika

Nie jest to jedyna forma zapytania, w której plan zapytań wykona wielokrotnie NEWID () i zmieni wynik. Jest to mylące, ale w rzeczywistości ma kluczowe znaczenie, aby NEWID () był przydatny do generowania kluczy i losowego sortowania.

Najbardziej mylące jest to, że nie wszystkie funkcje niedeterministyczne faktycznie zachowują się w ten sposób. Na przykład RAND () i GETDATE () będą wykonywane tylko raz na zapytanie.

Jeśli warto spojrzeć na ten stary standardowy dokument SQL 92 , wymagania dotyczące nierówności opisano w sekcji „ 8.2 <comparison predicate>” w następujący sposób:

1) Niech X i Y będą dowolnymi dwoma odpowiadającymi <elementem konstruktora wartości wiersza> s. Niech XV i YV będą wartościami reprezentowanymi odpowiednio przez X i Y.

[...]

ii) „X <> Y” jest prawdziwe, tylko wtedy, gdy XV i YV nie są równe.

[...]

7) Niech Rx i Ry będą dwoma <konstruktorem wartości wiersza> s <predykatu porównania> i niech RXi i RYi będą i-tym <elementem <konstruktora wartości wiersza> odpowiednio Rx i Ry. „Rx <comp op> Ry” jest prawdziwe, fałszywe lub nieznane w następujący sposób:

[...]

b) „x <> Ry” jest prawdziwe tylko wtedy, gdy RXi <> RYi dla niektórych i.

[...]

h) „x <> Ry” jest fałszem tylko i tylko wtedy, gdy „Rx = Ry” jest prawdziwe.

_{Uwaga: dla kompletności podałem 7b i 7h, ponieważ mówią one o <>porównaniu - nie sądzę, aby porównanie konstruktorów wartości wierszy z wieloma wartościami zostało zaimplementowane w T-SQL, chyba że po prostu bardzo nieporozumienie co to mówi - co jest całkiem możliwe}

To garść mylących śmieci. Ale jeśli chcesz kontynuować nurkowanie w śmietniku ...

Myślę , że 1.ii jest elementem stosowanym w tym scenariuszu, ponieważ porównujemy wartości „elementów konstruktora wartości wiersza”.

ii) „X <> Y” jest prawdziwe, tylko wtedy, gdy XV i YV nie są równe.

Zasadniczo powiedzenie X <> Yjest prawdziwe, jeśli wartości reprezentowane przez X i Y nie są równe. Ponieważ X < Y OR X > Yjest to logicznie równoważne przepisanie tego orzeczenia, optymalizator może z niego skorzystać.

Norma nie nakłada żadnych ograniczeń na tę definicję związanych z deterministyczną (lub cokolwiek, co otrzymujesz) elementami konstruktora wartości wiersza po obu stronach <>operatora porównania. Kod użytkownika odpowiada za to, że wyrażenie wartości po jednej stronie może być niedeterministyczne.