Início / dba / Perguntas / 325814
Accepted
Charlieface
Asked: 2023-04-10 05:05:02 +0800 CST

Ligação de esquema com UDF escalar recursiva

  • 772

TL;DR; É um bug que o SQL Server permite que uma UDF escalar chame a si mesma recursivamente quando vinculada ao esquema, mas somente quando alterada para fazer isso usando a CREATE OR ALTERsintaxe? Ou é um bug que outras sintaxes não são permitidas?

Uma UDF escalar recursiva trivial pode ser construída da seguinte maneira

CREATE FUNCTION dbo.Try1 (@i int)
RETURNS int
AS BEGIN
  RETURN IIF(@i = 0, 0, @i + dbo.Try1(@i - 1));
END;

Contanto que isso não seja vinculado ao esquema, isso é permitido.

Vamos tentar vinculá-lo ao esquema, faremos

CREATE FUNCTION dbo.Try2 (@i int)
RETURNS int
WITH SCHEMABINDING
AS BEGIN
  RETURN IIF(@i = 0, 0, @i + dbo.Try2(@i - 1));
END;

Não.

Cannot find either column "dbo" or the user-defined function
or aggregate "dbo.Try2", or the name is ambiguous

Crie-o sem recursão e altere-o

CREATE OR ALTER FUNCTION dbo.Try3 (@i int)
RETURNS int
WITH SCHEMABINDING
AS BEGIN RETURN NULL; END;

ALTER FUNCTION dbo.Try3 (@i int)
RETURNS int
WITH SCHEMABINDING
AS BEGIN
  RETURN IIF(@i = 0, 0, @i + dbo.Try3(@i - 1));
END;

Um erro diferente desta vez:

Cannot schema bind function 'dbo.Try3' because name 'dbo.Try3' is invalid
for schema binding. Names must be in two-part format
and an object cannot reference itself.

Hmmm, an object cannot reference itselfquem inventou essa regra? Não está nos documentos.

Vamos tentar comCREATE OR ALTER

CREATE OR ALTER FUNCTION dbo.Try4 (@i int)
RETURNS int
WITH SCHEMABINDING
AS BEGIN
  RETURN IIF(@i = 0, 0, @i + dbo.Try4(@i - 1));
END;

Ainda não.

Mas se primeiro fizermos CREATE sem recursão, depois CREATE OR ALTER( não ALTER ) com recursão, funcionará

CREATE OR ALTER FUNCTION dbo.Try5 (@i int)
RETURNS int
WITH SCHEMABINDING
AS BEGIN RETURN NULL; END;

CREATE OR ALTER FUNCTION dbo.Try5 (@i int)
RETURNS int
WITH SCHEMABINDING
AS BEGIN
  RETURN IIF(@i = 0, 0, @i + dbo.Try5(@i - 1));
END;

O estranho é: isso não faz sentido, seja qual for a maneira que CREATE OR ALTERfuncione por baixo. Se ele realmente cair e recriar, por que não recebemos o primeiro erro? E se alterar, então por que esta última opção funciona?

A parte mais engraçada: se o criarmos sem vinculação de esquema primeiro, então CREATE OR ALTER WITH SCHEMABINDINGfaremos

Cannot schema bind function 'dbo.Try6'. 'dbo.Try6' is not schema bound.

O que é um absurdo total.

db<>violino

sql-server

1 respostas

  1. Best Answer

    As funções de auto-referência não são bem documentadas em geral, mas a restrição à ligação de esquema de tais funções existe desde sempre. Nem toda condição de erro é mencionada nas páginas de sintaxe pai.

    A sequência específica de eventos descrita na pergunta que resulta em uma função de auto-referência vinculada ao esquema não deve ser permitida. (Funciona também para funções com valor de tabela de várias instruções).

    Permitir a vinculação de esquemas para funções de auto-referência introduz muitas novas possibilidades que teriam de ser consideradas e testadas. Por exemplo, o seguinte uso de sua função resulta em uma DBCC CHECKTABLEfalha:

    CREATE TABLE dbo.Test 
(
    n integer NOT NULL,
    -- Can be persisted because the function is deterministic
    -- Checks for determinism require schemabinding
    sn AS dbo.Try5(n) PERSISTED, 
        CHECK (sn > 0)
);

-- Succeeds
INSERT dbo.Test (n) 
VALUES (31);

DBCC CHECKTABLE (N'dbo.Test') 
    WITH EXTENDED_LOGICAL_CHECKS;

    Msg 2537, Level 16, State 0, Line 76
Table error: object ID 1137439126, index ID 0, partition ID 72057594050772992,
alloc unit ID 72057594110541824 (type In-row data), page (1:568), row 0.
The record check (valid computed column) failed. The values are 2 and 0.
DBCC results for 'Test'.
There are 1 rows in 1 pages for object "Test".
CHECKTABLE found 0 allocation errors and 1 consistency errors in table 'Test'
 (object ID 1137439126).
repair_allow_data_loss is the minimum repair level for the errors found by
 DBCC CHECKTABLE (Sandpit.dbo.Test).
DBCC execution completed. 
If DBCC printed error messages, contact your system administrator.

    Não pesquisei os motivos dessa falha, mas suponho que a CHECKTABLEatividade para verificar o valor da coluna computada acaba excedendo o nível máximo de aninhamento de 32.

    Muitas coisas precisariam de testes e/ou ajustes para suportar funções de auto-referência de ligação de esquema. Esse uso não é popular o suficiente para justificar o investimento. Existem alternativas para recursão no SQL Server.

    • 7

relate perguntas