Consider the following repro example ([fiddle](

    CREATE FUNCTION dbo.Repro (@myYear int)
    RETURNS datetime
    	IF @myYear <> 1990
    	    RETURN NULL
    	DECLARE @firstOfYear datetime;
    	SET @firstOfYear = DATEFROMPARTS(@myYear, 1, 1);
    	IF DATEDIFF(day, @firstOfYear, @firstOfYear) <> 0
    	RETURN @firstOfYear
    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?
Top Answer
Martin Smith (imported from SE)
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][1]][1]

 - 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][2] 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 

@@@ answer 199

where an expression on the inside of a nested loops protected by a passthru predicate is moved out into an unprotected region.


Conor Cunningham MSFT [replied](
> Microsoft is aware and a fix should be available in the next Cumulative Update.  Thanks for reporting it, and sorry you hit this issue.


This room is for discussion about this question.

Once logged in you can direct comments to any contributor here.

Enter question or answer id or url (and optionally further answer ids/urls from the same question) from

Separate each id/url with a space. No need to list your own answers; they will be imported automatically.