KB5022920 - FIX: Table-valued function that uses a parameter and the OPTION (OPTIMIZE FOR) clause gives incorrect results on the first run

Symptoms

When the SQL plan has interleaved execution enabled and uses the OPTION (OPTIMIZE FOR) clause, the table-valued function (TVF) that uses a parameter passed in the same SQL statement gives incorrect results on the first run.

Resolution

This problem is fixed in the following cumulative update for SQL Server:

Cumulative Update 19 for SQL Server 2019

Note Here is an example of not using interleaved execution after you install this cumulative update:

DECLARE @start smalldatetime
SELECT count(*) FROM dbo.GetData(@start)
OPTION (OPTIMIZE FOR (@start = '2022-05-23T06:00:00'))

CREATE FUNCTION [dbo].[GetData]
(
  @start datetime
)
RETURNS TABLE AS RETURN
SELECT t.id FROM dbo.TestTable t
WHERE t.testdate >= @start
GO

CREATE TABLE [dbo].[TestTable](
       [id] [int] IDENTITY(1,1) NOT NULL,
       [testdate] [datetime] NULL
) ON [PRIMARY]
GO

Status

Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the "Applies to" section.

References

Learn about the terminology that Microsoft uses to describe software updates.