Self referencing scalar function nesting level exceeded when adding a select
Bug report on Self referencing scalar functions following this question, answered by Paul White here:
In short a self referencing scalar function's references are evaluated when it should not be, resulting in an infinite loop in certain circumstances.
Reproducing the issue:
CREATE FUNCTION dbo.test6(@i int)
SELECT TOP 1
WHEN @i = 1 THEN 1
WHEN @i = 2 THEN 2
WHEN @i = 3 THEN (SELECT dbo.test6(1) + dbo.test6(2))
Resulting in the error:
Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32).
Part of the answer by Paul White:
Most important part of the
"This is a bug in project normalization, exposed by using a subquery inside a case expression with a non-deterministic function.
To explain, we need to note two things up front:
SQL Server cannot execute subqueries directly, so they are always unrolled or converted to an apply.
The semantics of CASE are such that a THEN expression should only be evaluated if the WHEN clause returns true.
The (trivial) subquery introduced in the problematic case therefore results in an apply operator (nested loops join). To meet the second requirement, SQL Server initially places the expression dbo.test6(1) + dbo.test6(2) on the inner side of the apply:
highlighted compute scalar
[@i]=(1) OR [@i]=(2) OR IsFalseOrNull [@i]=(3)
The inner side of the loop is only evaluated if the pass-through condition evaluates to false (meaning @i = 3). This is all correct so far. The Compute Scalar following the nested loops join also honours the CASE semantics correctly:
[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)
The problem is that the project normalization stage of query compilation sees that Expr1000 is uncorrelated and determines that it would be safe (narrator: it isn't) to move it outside the loop:
[Expr1000] = Scalar Operator([dbo].test6)+[dbo].test6))
This breaks* the semantics implemented by the pass-through predicate, so the function is evaluated when it should not be, and an infinite loop results.
You should report this bug. A workaround is to prevent the expression being moved outside the apply by making it correlated (i.e. including @i in the expression) but this is a hack of course. There is a way to disable project normalization, but I have been asked before not to share it publicly, so I won't."