Consider the following repro example ([fiddle](https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=a7d81b741dbe78aa6fb94b1edec1d78a)): 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](https://stackoverflow.com/help/minimal-reproducible-example) 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?
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]] - 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 @@@ 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](https://dba.stackexchange.com/questions/265912/sql-server-2019-executes-unreachable-code?noredirect=1&lq=1#comment523590_265917): > Microsoft is aware and a fix should be available in the next Cumulative Update. Thanks for reporting it, and sorry you hit this issue. : https://i.stack.imgur.com/XKlAk.png : https://docs.microsoft.com/en-gb/archive/blogs/craigfr/subqueries-in-case-expressions