尝试创建自引用函数的测试示例时,一个版本失败,而另一个版本成功。
唯一的区别是在函数主体中添加了
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。
实际问题
添加的
SELECT
使函数反复调用自身,导致无限循环,但是为什么要添加SELECT
给出此结果?其他信息
估计的执行计划
DB <>小提琴
Build version:
14.0.3045.24
在兼容性级别100和140上进行了测试
#1 楼
这是项目规范化中的错误,可以通过在具有非确定性函数的case表达式中使用子查询来暴露。要说明,我们需要提前注意两点:
SQL Server无法直接执行子查询,因此它们总是被展开或转换为应用。
CASE
的语义是,仅当THEN
子句返回true时,才应评估WHEN
表达式。 br /> 在有问题的情况下引入的(平凡的)子查询因此导致了apply运算符(嵌套循环联接)。为了满足第二个要求,SQL Server首先将表达式
dbo.test6(1) + dbo.test6(2)
放置在应用的内侧:[Expr1000] = Scalar Operator([dbo].[test6]((1))+[dbo].[test6]((2)))
...通过联接上的传递谓词来尊重
CASE
语义:[@i]=(1) OR [@i]=(2) OR IsFalseOrNull [@i]=(3)
仅当传递条件评估为false时,才评估循环的内侧(表示
@i = 3
)。到目前为止,这都是正确的。嵌套循环联接之后的Compute Scalar也正确地尊重了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))
说明核心问题的另一种方法是:
-- 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标志(不是查询提示),该标志也未设置。无论如何,由项目规范化应用的逻辑的实现是不正确或不完整的。
Azure反馈站点上的SQL Server错误报告。