Problem:
SQL server 2022 nondeterministically throws
Cannot find either column "ext" or the user-defined function or aggregate "ext.cfn_Something", or the name is ambiguous.
in a situation, where it should not attempt to invoke that function. It used to work fine in SQL 2016, problem started to appear after upgrade to 2022.
Context:
Let's have scalar user-defined function ext.cfn_Something(@ID INT) RETURNS BIT
, and this function does NOT exist. It is designed to override default function only when it exists on the target server.
And then, we have the actual dbo.cfn_Something(@ID INT) RETURNS BIT
that exists and does simple logic:
ext
version exist on this server. And use it if it does.CREATE FUNCTION dbo.cfn_Something(@ID INT) RETURNS BIT AS
BEGIN
DECLARE @Ret BIT
IF OBJECT_ID('ext.cfn_Something') IS NOT NULL
SET @Ret=ext.cfn_Something(@ID)
ELSE BEGIN
SET @Ret=CASE WHEN @ID>0 THEN 1 ELSE 0 END
END
RETURN @Ret
END
This function is called from a stored procedure
CREATE PROCEDURE dbo.csp_Something @SomeColumn INT
AS
SELECT ID, dbo.cfn_Something(ID) AS Value FROM Table WHERE SomeColumn=@SomeColumn
Observations:
WITH RECOMPUTE
to the stored procedure.I suspect it has something to do with caching execution plan for the procedure, or the function itself. And maybe one scenario (rebuild vs. reuse) is happier than the other.
Questions:
WITH RECOMPILE
be a stable solution?WITH
option available that could solve this?dbo
wouldn't change, and instalations are free to create any objects in ext
schema?Thank you
I couldn't find similar problem online. I tried adding the WITH RECOMPILE
that seems to work for now.
This appears to be an inlining issue (the new Scalar UDF Inlining feature). You can see this in action in this fiddle.
To be honest, the real answer is that you shouldn't rely on statements not being executed. SQL Server compiles full batches before executing them, and it's only because of deferred compilation that this ever worked in the first place.
A better solution might be to always define the function, and have the override just rewrite the existing function.
Alternatively, you could use synonyms. For example, create one default function, and have a synonym which can be changed to refer to a different one.
CREATE FUNCTION dbo.cfn_Something_Default(@ID INT)
RETURNS BIT
WITH SCHEMABINDING
AS
BEGIN
RETURN IIF(@ID > 0, CAST(1 AS bit), CAST(0 AS bit));
END;
CREATE SYNONYM dbo.cfn_Something FOR dbo.cfn_Something_Default;
And change it:
DROP SYNONYM dbo.cfn_Something;
CREATE SYNONYM dbo.cfn_Something FOR ext.cfn_Something;