添加選擇時超出自引用標量函式嵌套級別

目的

在嘗試創建自引用函式的測試範例時，一個版本失敗，而另一個版本成功。

唯一的區別是添加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 上測試

這是項目規範化中的一個錯誤，通過在具有非確定性函式的 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 回饋站點上的**錯誤報告。**

引用自：https://dba.stackexchange.com/questions/239324