添加选择时，自引用标量函数的嵌套级别超过了限制。

Question

目的

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

唯一的区别是在函数体中添加了一个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">

And expr1000 being

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

这可能解释了递归引用超过32的原因。

实际问题

添加SELECT会使函数不断地调用自身，导致无限循环，但为什么添加SELECT会产生这样的结果呢？

---

附加信息

估计执行计划

DB<>沙盒

Build version:
14.0.3045.24

在 compatibility_levels 100 和 140 上进行了测试

- Randi Vertongen

Answer 1

这是一个在“项目归一化”中的错误，由于在CASE表达式内部使用了非确定性函数的子查询而暴露出来。

为了解释清楚，我们需要首先注意两件事：

1. SQL Server不能直接执行子查询，因此它们总是展开或转换为应用（apply）。
2. CASE的语义要求只有当WHEN子句返回true时，才应该评估THEN表达式。

因此，在存在问题的情况下引入的（平凡的）子查询导致应用操作符（嵌套循环连接）。为了满足第二个要求，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）时才会被评估。到目前为止，一切都是正确的。紧随嵌套循环连接之后的“计算标量”也正确地遵守了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反馈网站上的错误报告。

- Paul White