Início / dba / Perguntas / 239324
Accepted
Randi Vertongen
Asked: 2019-05-30 03:52:45 +0800 CST

Nível de aninhamento de função escalar de auto-referência excedido ao adicionar uma seleção

  • 772

Propósito

Ao tentar criar um exemplo de teste de uma função de auto-referência, uma versão falha enquanto outra é bem-sucedida.

A única diferença é uma adição SELECTao corpo da função, resultando em um plano de execução diferente para ambos.

A função que funciona 

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;

Chamando a função

SELECT dbo.test5(3);

Devoluções

(No column name)
3

A função que não funciona

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;

Chamando a função

SELECT dbo.test6(3);

ou

SELECT dbo.test6(2);

Resultados no erro

Nível máximo de procedimento armazenado, função, gatilho ou exibição de aninhamento excedido (limite 32).

Adivinhando a causa

Há um escalar de computação adicional no plano estimado da função com falha, chamando

<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">

E expr1000 sendo

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

O que poderia explicar as referências recursivas superiores a 32.

A pergunta real

O adicionado SELECTfaz com que a função se chame repetidamente, resultando em um loop infinito, mas por que adicionar um está SELECTdando esse resultado?

informação adicional

Planos de execução estimados

DB<>Fiddle

Build version:
14.0.3045.24

Testado nos níveis de compatibilidade 100 e 140

sql-server functions

1 respostas

  1. Best Answer

    Este é um bug na normalização do projeto , exposto usando uma subconsulta dentro de uma expressão case com uma função não determinística.

    Para explicar, precisamos observar duas coisas antecipadamente:

    1. O SQL Server não pode executar subconsultas diretamente, portanto, elas sempre são desenroladas ou convertidas em um arquivo apply .
    2. A semântica de CASEé tal que uma THENexpressão só deve ser avaliada se a WHENcláusula retornar true.

    A subconsulta (trivial) introduzida no caso problemático, portanto, resulta em um operador de aplicação (junção de loops aninhados). Para atender ao segundo requisito, o SQL Server inicialmente coloca a expressão dbo.test6(1) + dbo.test6(2)no lado interno da aplicação:

    escalar de computação realçado

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

    ...com a CASEsemântica honrada por um predicado de passagem na junção:

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

    O lado interno do loop só é avaliado se a condição de passagem for avaliada como false (significando @i = 3). Tudo isso está correto até agora. O Compute Scalar após a junção de loops aninhados também respeita a CASEsemântica corretamente:

    [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)

    O problema é que o estágio de normalização do projeto de compilação da consulta vê que Expr1000não está correlacionado e determina que seria seguro ( narrador: não é ) movê-lo para fora do loop:

    projeto movido

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

    Isso quebra* a semântica implementada pelo predicado de passagem , de modo que a função é avaliada quando não deveria e resulta em um loop infinito.

    Você deve relatar este bug. Uma solução é evitar que a expressão seja movida para fora da aplicação, tornando-a correlacionada (ou seja, incluindo @ina expressão), mas isso é um hack, é claro. Existe uma maneira de desabilitar a normalização do projeto, mas me pediram antes para não compartilhá-la publicamente, então não vou.

    Esse problema não surge no SQL Server 2019 quando a função escalar é embutida , pois a lógica embutida opera diretamente na árvore analisada (bem antes da normalização do projeto). A lógica simples na questão pode ser simplificada pela lógica inlining para a não recursiva:

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

    ...que retorna 3.

    Outra maneira de ilustrar a questão central é:

    -- 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;

    Reproduz nas compilações mais recentes de todas as versões de 2008 R2 a 2019 CTP 3.0.

    Um outro exemplo (sem uma função escalar) fornecido por Martin Smith :

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

    Isso tem todos os elementos-chave necessários:

    • CASE(implementado internamente como ScaOp_IIF)
    • Uma função não determinística ( CRYPT_GEN_RANDOM)
    • Uma subconsulta na ramificação que não deve ser executada ( (SELECT ...))

    *Estritamente, a transformação acima ainda poderia estar correta se a avaliação de Expr1000fosse adiada corretamente, uma vez que é referenciada apenas pela construção segura:

    [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)

    ...mas isso requer um sinalizador ForceOrder interno (não uma dica de consulta), que também não está definido. Em qualquer caso, a implementação da lógica aplicada pela normalização do projeto está incorreta ou incompleta.

    Relatório de bugs no site de comentários do Azure para SQL Server.

    • 31

relate perguntas