34

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?
42

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.

| improve this answer | |
  • 21
    FYI Microsoft is aware and a fix should be available in the next Cumulative Update. Thanks for reporting it, and sorry you hit this issue. – Conor Cunningham MSFT Apr 27 at 14:31
  • @ConorCunninghamMSFT: That's great, thanks for the feedback! – Heinzi Apr 28 at 8:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.