主页 / dba / 问题 / 239324
Accepted
Randi Vertongen
Asked: 2019-05-30 03:52:45 +0800 CST

添加选择时超出自引用标量函数嵌套级别

  • 772

目的

在尝试创建自引用函数的测试示例时，一个版本失败，而另一个版本成功。

唯一的区别是添加SELECT到函数体中导致两者的执行计划不同。

有效的功能 

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;

调用函数

SELECT dbo.test5(3);

退货

(No column name)
3

不起作用的功能

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;

调用函数

SELECT dbo.test6(3);

或者

SELECT dbo.test6(2);

导致错误

超出最大存储过程、函数、触发器或视图嵌套级别（限制 32）。

猜测原因

失败函数的估计计划上有一个额外的计算标量，调用

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

而 expr1000 是

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

这可以解释超过 32 的递归引用。

实际问题

addSELECT使函数一遍又一遍地调用自己，导致无限循环，但是为什么添加 a 会SELECT给出这个结果呢？

附加信息

预计执行计划

DB<>小提琴

Build version:
14.0.3045.24

在 compatibility_levels 100 和 140 上测试

sql-server functions

1 个回答

  1. Best Answer

    这是项目规范化中的一个错误，通过在具有非确定性函数的 case 表达式中使用子查询来暴露。

    为了解释，我们需要先注意两点：

    1. SQL Server 不能直接执行子查询，因此它们总是展开或转换为apply。
    2. 的语义CASE是，仅当子句返回 trueTHEN时才应评估表达式。WHEN

    因此，在有问题的情况下引入的（微不足道的）子查询会导致应用运算符（嵌套循环连接）。为了满足第二个要求，SQL Server 最初将表达式dbo.test6(1) + dbo.test6(2)放在 apply 的内侧：

    突出显示的计算标量

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

    ...具有连接上的传递谓词所CASE尊重的语义：

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

    仅当传递条件评估为false时才评估循环的内侧（意思是@i = 3）。到目前为止，这一切都是正确的。嵌套循环连接之后的计算标量CASE也正确遵守语义：

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

    问题是查询编译的项目规范化阶段认为这Expr1000是不相关的，并确定将其移出循环是安全的（旁白：它不是）：

    移动项目

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

    这破坏了传递谓词实现的语义，因此函数在不应该被评估时被评估，并导致无限循环。

    你应该报告这个错误。一种解决方法是通过使表达式相关（即包括在表达式中）来防止表达式被移出应用程序，@i但这当然是一种技巧。有一种方法可以禁用项目规范化，但之前有人要求我不要公开分享，所以我不会。

    在 SQL Server 2019中内联标量函数时不会出现此问题，因为内联逻辑直接在解析树上运行（远在项目规范化之前）。问题中的简单逻辑可以通过内联逻辑简化为非递归：

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

    ...返回 3。

    说明核心问题的另一种方法是：

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

    再现了从 2008 R2 到 2019 CTP 3.0 的所有版本的最新版本。

    Martin Smith提供的另一个示例（没有标量函数）：

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

    这具有所需的所有关键要素：

    • CASE（内部实现为ScaOp_IIF）
    • 非确定性函数 ( CRYPT_GEN_RANDOM)
    • 不应执行的分支上的子查询 ( (SELECT ...))

    *严格来说，如果正确推迟了对的评估，上述转换仍然是正确的Expr1000，因为它仅被安全构造引用：

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

    ...但这需要一个内部ForceOrder标志（不是查询提示），该标志也未设置。无论如何，项目规范化所应用的逻辑的实现是不正确或不完整的。

    SQL Server 的 Azure 反馈站点上的错误报告。

    • 31

相关问题