Przekroczono poziom zagnieżdżenia funkcji skalarnej przy dodawaniu zaznaczenia

Cel, powód

Podczas próby utworzenia testowego przykładu funkcji samodzielnego odwoływania się jedna wersja zawiedzie, a inna się powiedzie.

Jedyną różnicą jest dodanie SELECTdo treści funkcji, co skutkuje innym planem wykonania dla obu.

Funkcja, która działa

CREATE FUNCTION dbo.test5(@i int)
RETURNS INT
AS 
BEGIN
RETURN(
SELECT TOP 1
CASE 
WHEN @i = 1 THEN 1
WHEN @i = 2 THEN 2
WHEN @i = 3 THEN  dbo.test5(1) + dbo.test5(2)
END
)
END;

Wywołanie funkcji

SELECT dbo.test5(3);

Zwroty

(No column name)
3

Funkcja, która nie działa

CREATE FUNCTION dbo.test6(@i int)
RETURNS INT
AS 
BEGIN
RETURN(
SELECT TOP 1
CASE 
WHEN @i = 1 THEN 1
WHEN @i = 2 THEN 2
WHEN @i = 3 THEN (SELECT dbo.test6(1) + dbo.test6(2))
END
)END;

Wywołanie funkcji

SELECT dbo.test6(3);

lub

SELECT dbo.test6(2);

Powoduje błąd

Przekroczono maksymalny poziom procedury składowanej, funkcji, wyzwalacza lub zagnieżdżenia widoku (limit 32).

Zgadywanie przyczyny

Istnieje dodatkowy skalar obliczeniowy na oszacowanym planie funkcji, która się nie powiodła, wywoływanie

<ColumnReference Column="Expr1002" />
<ScalarOperator ScalarString="CASE WHEN [@i]=(1) THEN (1) ELSE CASE WHEN [@i]=(2) THEN (2) ELSE CASE WHEN [@i]=(3) THEN [Expr1000] ELSE NULL END END END">

I expr1000 istota

<ColumnReference Column="Expr1000" />
<ScalarOperator ScalarString="[dbo].[test6]((1))+[dbo].[test6]((2))">

Co może tłumaczyć rekurencyjne odniesienia przekraczające 32.

Rzeczywiste pytanie

Dodane SELECTsprawia, że samo wywołanie funkcji ciągle, w wyniku czego powstaje nieskończona pętla, ale dlaczego dodawanie SELECTdaje ten wynik?

dodatkowe informacje

Szacowane plany wykonania

DB <> skrzypce

Build version:
14.0.3045.24

Testowany na poziomach zgodności 100 i 140

sql-server functions sql-server-2017

— Randi Vertongen
źródło

Jest to błąd w normalizacji projektu , ujawniony przez użycie podzapytania wewnątrz wyrażenia case z funkcją niedeterministyczną.

Aby to wyjaśnić, musimy zwrócić uwagę na dwie rzeczy z góry:

Program SQL Server nie może bezpośrednio wykonywać podkwerend, dlatego są one zawsze rozwijane lub konwertowane na zastosowanie .
Semantyka CASEjest taka, że THENwyrażenie powinno być oceniane tylko wtedy, gdy WHENklauzula zwraca true.

(Trywialne) podzapytanie wprowadzone w problematycznym przypadku skutkuje zatem operatorem zastosowania (łączenie zagnieżdżonych pętli). Aby spełnić drugie wymaganie, SQL Server początkowo umieszcza wyrażenie dbo.test6(1) + dbo.test6(2)po wewnętrznej stronie zastosowania:

[Expr1000] = Scalar Operator([dbo].[test6]((1))+[dbo].[test6]((2)))

... przy czym CASEsemantyka honorowane przez pass-through orzecznika na przyłączyć:

[@i]=(1) OR [@i]=(2) OR IsFalseOrNull [@i]=(3)

Wewnętrzna strona pętli jest oceniany tylko jeśli Przejścia analizuje schemat warunkowym do false (czyli @i = 3). Jak dotąd wszystko jest poprawne. Oblicz skalarne następujące zagnieżdżone pętle dołączyć także honoruje CASEsemantykę poprawnie:

[Expr1001] = Scalar Operator(CASE WHEN [@i]=(1) THEN (1) ELSE CASE WHEN [@i]=(2) THEN (2) ELSE CASE WHEN [@i]=(3) THEN [Expr1000] ELSE NULL END END END)

Problem polega na tym, że etap normalizacji projektu kompilacji zapytań widzi, że Expr1000jest on nieskorelowany i określa, że bezpieczne ( narrator: nie jest ) przeniesienie go poza pętlę:

[Expr1000] = Scalar Operator([dbo].[test6]((1))+[dbo].[test6]((2)))

To łamie * semantykę zaimplementowaną przez predykat przekazywania , więc funkcja jest oceniana, kiedy nie powinna, i powstaje nieskończona pętla.

Powinieneś zgłosić ten błąd. Obejściem tego problemu jest uniemożliwienie przeniesienia wyrażenia poza aplikację, powodując korelację (tzn. Włączenie @ido wyrażenia), ale jest to oczywiście hack. Istnieje sposób na wyłączenie normalizacji projektu, ale wcześniej proszono mnie, aby nie udostępniać go publicznie, więc nie zrobię tego.

Ten problem nie występuje w SQL Server 2019, gdy funkcja skalarna jest wbudowana, ponieważ logika wstawiania działa bezpośrednio na analizowanym drzewie (na długo przed normalizacją projektu). Prostą logikę w pytaniu można uprościć, wprowadzając logikę do nierekurencyjnej:

[Expr1019] = (Scalar Operator((1)))
[Expr1045] = Scalar Operator(CONVERT_IMPLICIT(int,CONVERT_IMPLICIT(int,[Expr1019],0)+(2),0))

... która zwraca 3.

Innym sposobem zilustrowania podstawowego problemu jest:

-- Not schema bound to make it non-det
CREATE OR ALTER FUNCTION dbo.Error() 
RETURNS integer 
-- WITH INLINE = OFF -- SQL Server 2019 only
AS
BEGIN
    RETURN 1/0;
END;
GO
DECLARE @i integer = 1;

SELECT
    CASE 
        WHEN @i = 1 THEN 1
        WHEN @i = 2 THEN 2
        WHEN @i = 3 THEN (SELECT dbo.Error()) -- 'subquery'
        ELSE NULL
    END;

Reprodukuje w najnowszych wersjach wszystkich wersji od 2008 R2 do 2019 CTP 3.0.

Kolejny przykład (bez funkcji skalarnej) podany przez Martina Smitha :

SELECT IIF(@@TRANCOUNT >= 0, 1, (SELECT CRYPT_GEN_RANDOM(4)/ 0))

Zawiera wszystkie niezbędne kluczowe elementy:

CASE(zaimplementowane wewnętrznie jako ScaOp_IIF)
Funkcja niedeterministyczna ( CRYPT_GEN_RANDOM)
Podzapytanie w gałęzi, które nie powinno być wykonywane ( (SELECT ...))

* Ściśle rzecz biorąc, powyższa transformacja może nadal być poprawna, jeśli ocena Expr1000została odroczona poprawnie, ponieważ odnosi się do niej tylko bezpieczna konstrukcja:

[Expr1002] = Scalar Operator(CASE WHEN [@i]=(1) THEN (1) ELSE CASE WHEN [@i]=(2) THEN (2) ELSE CASE WHEN [@i]=(3) THEN [Expr1000] ELSE NULL END END END)

... ale wymaga to wewnętrznej flagi ForceOrder (nie podpowiedzi do zapytania), która również nie jest ustawiona. W każdym razie implementacja logiki zastosowanej przez normalizację projektu jest niepoprawna lub niekompletna.

Raport o błędach w witrynie Azure Feedback dla programu SQL Server.

— Paul White mówi GoFundMonica
źródło