[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
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.
Actual result on SQL Server 2017:
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.
- 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?