Sql-server – SQL Server 2019 executes unreachable code

functionssql serversql-server-2019

[Update: This question describes a bug which has been fixed in Cumulative Update 5 for SQL Server 2019.]

Consider the following repro example (fiddle):

CREATE FUNCTION dbo.Repro (@myYear int)
RETURNS datetime
AS
BEGIN
    IF @myYear <> 1990
    BEGIN
        RETURN NULL
    END

    DECLARE @firstOfYear datetime;
    SET @firstOfYear = DATEFROMPARTS(@myYear, 1, 1);
    
    IF DATEDIFF(day, @firstOfYear, @firstOfYear) <> 0
    BEGIN
        RETURN NULL
    END

    RETURN @firstOfYear
END
SELECT dbo.Repro(0);

Obviously, that function should return the first of January 1990 if the input is 1990, and NULL otherwise. Yes, I know that DATEDIFF(day, @firstOfYear, @firstOfYear) <> 0 is a nonsensical operation. This is a mcve to demonstrate a potential bug, not production code.

Now let's execute SELECT dbo.Repro(0) on SQL Server 2017 and SQL Server 2019.

Expected result: NULL.

Actual result on SQL Server 2017: NULL

Actual result on SQL Server 2019:

Msg 289 Level 16 State 1 Line 1
Cannot construct data type date, some of the arguments have values which are not valid.

Apparently, SQL Server 2019 executes some of the code below the initial guard clause (IF @myYear <> 1990) even if it shouldn't.

My questions:

  • Is this expected behavior, or did I find a bug in SQL Server 2019?
  • If this is expected behavior, how do I correctly write a guard clause validating input paramters?

Best Answer

This is a bug with the inlining of Scalar UDFs (or perhaps a bug with the query optimiser that is being exposed more by scalar UDF inlining). You can use WITH INLINE = OFF to turn off inlining for that function.

Using a variable instead of a constant shows a bit more detail

declare @myYear int = 0

SELECT dbo.Repro(@myYear);

enter image description here

  • Node 5 defines Expr1000 = CASE WHEN [@myYear]<>(1990) THEN (1) ELSE (0) END
  • Node 2 defines [Expr1003] = Scalar Operator(CONVERT_IMPLICIT(datetime,datefromparts([@myYear],(1),(1)),0))

These expressions are simplified when using the literal 0 to 1 and CONVERT_IMPLICIT(datetime,datefromparts((0),(1),(1)),0) respectively.

The datefromparts(0 will throw the error when evaluated.

  • Node 6 defines Expr1002 = CASE WHEN [Expr1000] = (1) THEN (1) ELSE (0) END

And Expr1002 is used as a passthru predicate on a nested loops join (node 3). On the inside of that nested loops the constant scan (node 7) returns no columns.

So this looks like the same basic issue as the answer here where an expression on the inside of a nested loops protected by a passthru predicate is moved out into an unprotected region.

Related Question