目的
在尝试创建自引用函数的测试示例时,一个版本失败,而另一个版本成功。
唯一的区别是添加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
给出这个结果呢?
附加信息
Build version:
14.0.3045.24
在 compatibility_levels 100 和 140 上测试
这是项目规范化中的一个错误,通过在具有非确定性函数的 case 表达式中使用子查询来暴露。
为了解释,我们需要先注意两点:
CASE
是,仅当子句返回 trueTHEN
时才应评估表达式。WHEN
因此,在有问题的情况下引入的(微不足道的)子查询会导致应用运算符(嵌套循环连接)。为了满足第二个要求,SQL Server 最初将表达式
dbo.test6(1) + dbo.test6(2)
放在 apply 的内侧:...具有连接上的传递谓词所
CASE
尊重的语义:仅当传递条件评估为false时才评估循环的内侧(意思是
@i = 3
)。到目前为止,这一切都是正确的。嵌套循环连接之后的计算标量CASE
也正确遵守语义:问题是查询编译的项目规范化阶段认为这
Expr1000
是不相关的,并确定将其移出循环是安全的(旁白:它不是):这破坏了传递谓词实现的语义,因此函数在不应该被评估时被评估,并导致无限循环。
你应该报告这个错误。一种解决方法是通过使表达式相关(即包括在表达式中)来防止表达式被移出应用程序,
@i
但这当然是一种技巧。有一种方法可以禁用项目规范化,但之前有人要求我不要公开分享,所以我不会。在 SQL Server 2019中内联标量函数时不会出现此问题,因为内联逻辑直接在解析树上运行(远在项目规范化之前)。问题中的简单逻辑可以通过内联逻辑简化为非递归:
...返回 3。
说明核心问题的另一种方法是:
再现了从 2008 R2 到 2019 CTP 3.0 的所有版本的最新版本。
Martin Smith提供的另一个示例(没有标量函数):
这具有所需的所有关键要素:
CASE
(内部实现为ScaOp_IIF
)CRYPT_GEN_RANDOM
)(SELECT ...)
)*严格来说,如果正确推迟了对的评估,上述转换仍然是正确的
Expr1000
,因为它仅被安全构造引用:...但这需要一个内部ForceOrder标志(不是查询提示),该标志也未设置。无论如何,项目规范化所应用的逻辑的实现是不正确或不完整的。
SQL Server 的 Azure 反馈站点上的错误报告。