SQL Server 2022 evaluates statement after condition that is false

Question

Code to replicate in SQL Server 2022 v16.0.4095.4:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION [dbo].[fnInner] 
    (@id uniqueidentifier, @date datetime) 
RETURNS integer 
AS
BEGIN
    IF @id IS NULL
       RETURN CAST('null @id' AS int);

    IF @date IS NULL
       RETURN CAST('null @date' AS int);

    RETURN 1;
END
GO

CREATE FUNCTION [dbo].[fnOuter] 
    (@id uniqueidentifier) 
RETURNS varchar(10) 
AS
BEGIN
    DECLARE @case integer, @test int, @ret varchar(10);
    
    SELECT @case = 2, @test = NULL, @ret = 'None';

    IF @case = 1
        SET @test = dbo.fnInner(@id, NULL);
    ELSE IF @case = 2
        SET @ret = 'Two';
    ELSE IF @test IS NULL
        SET @ret = 'Null';

    RETURN @ret
END
GO

SELECT dbo.fnOuter(NEWID());

Expected result: 'Two'

Actual result:

Conversion failed when converting the varchar value 'null @date' to data type int

In SQL Server 2008 (v10.50.4042.0):

Expected Result: 'Two'

Actual Result: 'Two'

Answer 1

See the following article: Scalar UDF Inlining.

Near the end there is a small notice that says: "Certain warnings in statements inside the UDF (such as divide by zero etc.) which might have been hidden earlier, might show up due to inlining."

In the same article you can find several ways to disable inlining, for the entire database or only for some UDF or statement, so you can get the expected results.

However, I think you should try reporting this as a bug (see SQL Server help and feedback for several ways to do this). Maybe Microsoft will fix this behavior in a later version. See KB4538581 to find out other weird behaviors that appeared as a result of inlining in the initial versions of SQL Server 2019 and were fixed in later versions of SQL Server 2019 or SQL Server 2022.

It is interesting to note that the problem is not happening in SQL Server 2019 GDR (version 15.0.2104.1), but it appears in SQL Server 2019 CU22 (version 15.0.4326.1), so this can be categorised as a regression.