Is this SQL Server function compiling incorrectly?

Question

We have a SQL Server user defined function that appears to be compiling incorrectly with SQL Server 2022. We have many customers on different versions of SQL Server going back to 2008 R2. The function has been good as is until SQL Server 2022.

In my case it's occurring with: Product: Microsoft SQL Server Developer (64-bit) OS: Windows 10 Pro (10.0) Version: 16.0.1105.1

The function behavior doesn't seem to match the function statements and its inputs.

If I alter the function to add two nonsense lines near the top:

   declare @dummy int = 0
   if @dummy = 1 return 'oops'

The function behaves as expected again.

The full function follows with input value details in comments:

ALTER function [dbo].[fnSchedRecurGetNextDate] (@CustomerId uniqueidentifier) returns varchar(10)
as begin

--@CustomerId is a valid PK value that results in one record in the select statement further below.
declare @SchedRecur integer, @SchedRecurOpt1 integer, @SchedDate datetime
declare @NextDate datetime
declare @returnval varchar(10)

--In problem scenario, SchedRecur = 9, SchedRecurOpt1 = 0, SchedDate = '2023-12-28'
--If I comment the next two lines, the code branches to calling fnNextRecurDateEx with @SchedDate = null.
declare @dummy int = 0
if @dummy = 1 return 'oops'

select @SchedRecur = SchedRecur, @SchedRecurOpt1 = SchedRecurOpt1, @SchedDate = SchedDate from tblCustomer where CustomerId = @CustomerId

if @SchedRecur = 0  --Non Recurring
	set @returnval = 'None'
else if @SchedRecur = 1 begin --Daily
	if @SchedRecurOpt1 = 3 begin 
		--NextRecurDateEx may potentially return #2/3/1901# to indicate no next date.
        set @NextDate = dbo.fnNextRecurDateEx(@CustomerId, @SchedDate)
        if @NextDate = '1901-02-03' 
			set @returnval = 'None' 
		else 
			set @returnval = convert(varchar(10), @NextDate, 101)
	end else begin
		select top 1 @NextDate = CustomDate from tblJobCustomDates where CustomerId = @CustomerId ORDER BY CustomDate
        if @NextDate is null 
			set @returnval = 'None' 
		else
			set @returnval = convert(varchar(10), @NextDate, 101)
	end 
end
else if @SchedRecur = 9 --Custom
	set @returnval = 'Custom' 
else begin
	select top 1 @NextDate = CustomDate from tblJobCustomDates where CustomerId = @CustomerId ORDER BY CustomDate
    if @NextDate is null 
		set @returnval = 'None' 
	else
		set @returnval = convert(varchar(10), @NextDate, 101)
end

return @returnval
end

Answer 1

In my case it's occurring with: Product: Microsoft SQL Server Developer (64-bit) OS: Windows 10 Pro (10.0) Version: 16.0.1105.1

This is the release version (more or less) of SQL 2022. The more recent cumulative update is CU which you should download and install. You may be running into a bug that has been fixed.

There was a major change with regards to scalar user-defined functions in SQL 2019: SQL Server now attempts to inline them. Initially, there were not that many features that blocked inlining, but the list has grown over time of the simple reason that the inlining did not always produce correct results. I believe that there has been fixes to inlining even after SQL 2022 has come out, so that's why I want you to try CU10.

To verify that this is an issue with inlining, you can add WITH INLINE = OFF to the header of you procedure:

ALTER function [dbo].[fnSchedRecurGetNextDate] (@CustomerId uniqueidentifier) returns varchar(10) WITH INLINE = OFF
as begin

I did not quite follow your StackOverflow post, but it seems that you have a couple of procedures that nest. You may have to disable inlining with more than one of them.