After SQL restart, CTE causes invalid object name error for a short period in SQL Server 2019

Question

We have some SQL code that has been in production for some time, in a SQL Server 2016 database; but it is raising an error in a SQL Server 2019 database for the first hour or so after restarting SQL Server (anywhere from 5-10 minutes to an hour or more, probably depending on the level of activity in SQL Server). The error is "invalid object name" for a CTE (Common Table Expression).

We have a production environment with several databases in SQL Server 2016. We have now set up a new development/test environment with SQL Server 2019 (on a Windows Server 2016 machine, with 24GB of RAM and 4 CPU cores) so that we can test with SQL Server 2019. The databases on this test server are restored copies of the production databases from production backups. All the databases in the test environment have the compatibility level set to 150 (SQL Server 2019).

Early each morning, we started to see some issues with a couple of functions that use CTEs, where the function would raise errors like this:

SqlException (0x80131904): Invalid object name 'CTEuniqueName'.]

Msg 208, Level 16, State 1, Procedure ufn_FunctionName, Line 28 [Batch Start Line 0]
Invalid object name 'CTEuniqueName'.

The errors stopped happening after a short period of time and didn't happen again until the next morning.

The error was happening in a pair of stored procedures that were called one after the other, both of which called the same (user-defined SQL) function. With some testing, I learned that I could sometimes cause the same error by just calling the function, and then by just executing a block of code from the function.

I also discovered that I could consistently cause the error by restarting the SQL Server instance and calling the function or the code block. This is probably also why it was only failing early in the morning - there had been no activity on the SQL Server instance for several hours before that, so it had gone into idle mode or shut down its processes.

After repeatedly calling the function or code, at some point it would succeed without raising the error, and after that, it seemed to keep working (until I restarted the SQL Server instance again).

If I change the database that contains this function to "SQL Server 2016" compatibility mode, then the function always succeeds, even immediately after restarting the SQL Server instance. So, it seems to be an issue specific to SQL Server 2019.

The code in the function looks like the following (with anonymized names), and the function contains 2 separate "IF" blocks similar to the following; however I can cause the error with just the following block of code. The 2 views in this code are views to other databases in the same SQL Server instance.

DECLARE @MyID INT = 150589;
DECLARE @MyType VARCHAR(25) = 'Test';
DECLARE @ExpirationDate DATE;
BEGIN
 IF @MyType = 'Test'
     BEGIN
         DECLARE @Test1 INT;
         WITH CTEuniqueName(PersonID, DateComplete)
              AS (SELECT T1.PersonID, MAX(T2.DateComplete) AS DateComplete
                  FROM dbo.TABLE1 AS T1 WITH(NOLOCK)
                       LEFT JOIN dbo.VIEW2 AS T2 WITH(NOLOCK) ON T2.ID = T1.ID
                  WHERE T1.ID = @MyID
                        AND T2.SecondID IN(SELECT SecondID
                                        FROM dbo.TABLE3 WITH(NOLOCK)
                                        WHERE Name = 'TEST')
                  GROUP BY T1.PersonID)
              SELECT @ExpirationDate = CASE
                                           WHEN V3.TimeFrame > 0 THEN DATEADD(DAY, TimeFrame, CONVERT(DATE, CTE1.DateComplete))
                                           ELSE NULL
                                       END
              FROM CTEuniqueName AS CTE1
                   INNER JOIN dbo.VIEW2 AS V2 WITH(NOLOCK) ON V2.ID = CTE1.ID
                                                               AND V2.DateComplete = CTE1.DateComplete
                   INNER JOIN dbo.VIEW3 AS V3 WITH(NOLOCK) ON V3.QuizID = V2.QuizID
              WHERE TS.SecondID IN(SELECT SecondID
                                 FROM dbo.TABLE3 WITH(NOLOCK)
                                 WHERE Name = 'TEST');
     END;
END;

Also, in the time period after startup that the error is occurring, if I run the 2 stored procedures (that call the above function, and both procedures return a result set) in an SSMS window, then the procedures successfully return their respective result sets in the SSMS window and then SSMS switches to the Messages panel and displays the "invalid object name" error.

Does anyone know why this might be intermittently failing only in SQL Server 2019 (and seemingly only after SQL Server startup or a period of idel time)? Or, can anyone suggest how to troubleshoot issues like this? I have tried checking the SQL error log; and I have also tried running the Profiler with function calls included - neither of these has provided any clues.

Additional information:

• Running DBCC FREESYSTEMCACHE(N'ALL'); does cause the same error to temporarily occur again (in the same way that restarting the server does)
• The function is not inlineable (due to the CTE), so it doesn't seem like scalar UDF inlining is the cause of the problem
• There are no errors in the SQL Server error log when this occurs
• I tried to reproduce the problem using newly created databases on the same instance, with just the tables required by the function, but the functions always complete successfully in the new database

Answer 1

Initially, it seems like this was a bug related to the new Scalar UDF Inlining feature added in SQL Server 2019, since you mentioned that disabling inlining resolved the problem. On further inspection, the function cannot be inlined due to the presence of a CTE in the function definition.

Here's my (failed) attempt to reproduce the issue:

USE [master];
GO
DROP DATABASE IF EXISTS [256861OtherDatabase];
GO
CREATE DATABASE [256861OtherDatabase];
GO
USE [256861OtherDatabase];
GO

CREATE TABLE dbo.TableForView2
(
    ID int IDENTITY(1,1) NOT NULL,
    DateComplete datetime NOT NULL,
    SecondID int NOT NULL,
    QuizID int NOT NULL,
    CONSTRAINT PK_TableForView2 PRIMARY KEY (Id)
);
GO

CREATE TABLE dbo.TableForView3
(
    ID int IDENTITY(1,1) NOT NULL,
    Timeframe int NOT NULL,
    QuizID int NOT NULL,
    CONSTRAINT PK_TableForView3 PRIMARY KEY (Id)
);
GO

USE [master];
GO
DROP DATABASE IF EXISTS [256861];
GO
CREATE DATABASE [256861];
GO
USE [256861];
GO

CREATE TABLE dbo.TABLE1
(
    ID int IDENTITY(1,1) NOT NULL,
    PersonID int NOT NULL,
    CONSTRAINT PK_TABLE1 PRIMARY KEY (Id)
);
GO

CREATE VIEW dbo.View2
AS
SELECT
    ID,
    DateComplete,
    SecondID,
    QuizID
FROM [256861OtherDatabase].dbo.TableForView2 d
GO

CREATE TABLE dbo.TABLE3
(
    ID int IDENTITY(1,1) NOT NULL,
    SecondID int NOT NULL,
    [Name] varchar(50) NOT NULL,
    CONSTRAINT PK_TABLE3 PRIMARY KEY (Id)
);
GO

CREATE VIEW dbo.View3
AS
SELECT
    ID,
    Timeframe,
    QuizID
FROM [256861OtherDatabase].dbo.TableForView3 d
GO

CREATE FUNCTION dbo.TestFunction 
(
    @MyID INT = 150589, 
    @MyType VARCHAR(25) = 'Test'
)
RETURNS date
AS
BEGIN;
DECLARE @ExpirationDate DATE;
 IF @MyType = 'Test'
     BEGIN
         DECLARE @Test1 INT;
         WITH CTEuniqueName(ID, DateComplete)
              AS (SELECT T1.PersonID, MAX(T2.DateComplete) AS DateComplete
                  FROM dbo.TABLE1 AS T1 WITH(NOLOCK)
                       LEFT JOIN dbo.VIEW2 AS T2 WITH(NOLOCK) ON T2.ID = T1.ID
                  WHERE T1.ID = @MyID
                        AND T2.SecondID IN(SELECT SecondID
                                        FROM dbo.TABLE3 WITH(NOLOCK)
                                        WHERE Name = 'TEST')
                  GROUP BY T1.PersonID)
              SELECT @ExpirationDate = CASE
                                           WHEN V3.TimeFrame > 0 THEN DATEADD(DAY, TimeFrame, CONVERT(DATE, CTE1.DateComplete))
                                           ELSE NULL
                                       END
              FROM CTEuniqueName AS CTE1
                   INNER JOIN dbo.VIEW2 AS V2 WITH(NOLOCK) ON V2.ID = CTE1.ID
                                                               AND V2.DateComplete = CTE1.DateComplete
                   INNER JOIN dbo.VIEW3 AS V3 WITH(NOLOCK) ON V3.QuizID = V2.QuizID
              WHERE V2.SecondID IN(SELECT SecondID
                                 FROM dbo.TABLE3 WITH(NOLOCK)
                                 WHERE Name = 'TEST');
     END;
     RETURN @ExpirationDate;
END;
GO

I noticed that the function is marked as is_inlineable = 0 in sys.sql_modules, so I ran the function creation code again with the following Extended Events session running:

CREATE EVENT SESSION [inlining] ON SERVER 
ADD EVENT sqlserver.tsql_scalar_udf_not_inlineable
ADD TARGET package0.event_file(SET filename=N'inlining')
WITH (STARTUP_STATE=OFF)
GO

It produced 1 event with a blocked_reason of "CTE," which lines up with the Inlineable scalar UDFs requirements (that were updated to include CTEs after this question was initially posted).

This still seems like buggy behavior to me. The only suggestion I have, really, is to rewrite the function to avoid the CTE. This might work around the problem, and also could allow the function to be inlined.

Answer 2

ran into this today while a developer was testing another issue. On his SQL 2019 from time to time he encountered a "Invalid Object Name [name of UDF]".

Found this thread - and read a bit up on the new Intelligent Query Processing, and UDF Inlining.

I can now reproduce the scenario and will forward it to MS, dont know the procedure for this but guess I can figure that part out.

The problem seems to arise (at least in our case) when using an UDF created on an older SQL server and backup is restored on 2019 server.

Then the "is_inlineable" is 1 on the UDF, even if it has CTE in the body. After ALTERing the UDF on SQL 2019 the problem goes away.

Potentially this will affect all our customers upgrading to SQL 2019, so I see it as rather critical.

Below is a script to follow to reproduce. Tested on 15.0.2070.41

Best regards

/Anders

-- First steps are on non-SQL2019 server, used 14.0.2027.2 for this test
-- 1. Create Local database "InlineUDF" in SQL Server below 2019
-- 2. Execute create part of script
USE InlineUDF

CREATE TABLE Test ([Id] INT, [ParentID] INT NULL, [Name] NVARCHAR(10))

INSERT INTO Test (Id, ParentID, Name)
SELECT 1, NULL, 'Root'
UNION SELECT 2, 1, 'Child'
UNION SELECT 3, 1, 'Child1'
UNION SELECT 4, 1, 'Child2'
UNION SELECT 5, 1, 'Child3'
UNION SELECT 6, 3, 'Child4'
UNION SELECT 7, 6, 'Child5'
UNION SELECT 8, 5, 'Child6'
UNION SELECT 9, 2, 'Child7'

GO
CREATE FUNCTION [dbo].[ChildrenFilter]
(
    @parentId int
)
RETURNS int
AS
BEGIN

    DECLARE @returnValue int
    DECLARE @result int
    
    BEGIN
     -- Recursive statement selecting InspectionJob inclusive all childs
     WITH ChildrenCountRecursive(id) AS (
        SELECT Test.[Id]
        FROM   Test            
        WHERE Test.[Id] = @parentId
      UNION ALL
        SELECT  Test.[Id]
        FROM Test
        INNER JOIN ChildrenCountRecursive AS tr ON Test.[ParentID] = tr.[Id]
     )
     -- End of recursive statement
      
     -- Check inspection job and all childs for vesseltype and metadata.
     SELECT @result = COUNT(ChildrenCountRecursive.Id)
     FROM ChildrenCountRecursive
    END
    
    IF (@result >= 2)
      SELECT @returnValue = @parentId
    ELSE
      SELECT @returnValue = null
    
    RETURN @returnValue 
END

GO

-- 3. Backup database, and restore on SQL 2019 server

-- 4. Examine Inlineable on UDF
USE InlineUDF

SELECT is_inlineable, NAME
FROM
    sys.sql_modules m
INNER JOIN 
    sys.objects o on o.object_id = m.object_id
WHERE 
    o.name = 'ChildrenFilter'
-- UDF is inlineable.

-- 5. Clear proc cache - and run query. 
DBCC FREESYSTEMCACHE(N'ALL');

select * from Test 
WHERE Id IN (SELECT [dbo].[ChildrenFilter](Id))

-- 6. Update compatibility level to 150
ALTER DATABASE [InlineUDF] SET COMPATIBILITY_LEVEL = 150

 --7. Clear cache and try again - first time it fails - second time it's good. 
DBCC FREESYSTEMCACHE(N'ALL');

select * from Test 
WHERE Id IN (SELECT [dbo].[ChildrenFilter](Id))

-- 8. Alter UDF
GO
ALTER FUNCTION [dbo].[ChildrenFilter]
(
    @parentId int
)
RETURNS int
AS
BEGIN

    DECLARE @returnValue int
    DECLARE @result int
    
    BEGIN
     -- Recursive statement selecting InspectionJob inclusive all childs
     WITH ChildrenCountRecursive(id) AS (
        SELECT Test.[Id]
        FROM   Test            
        WHERE Test.[Id] = @parentId
      UNION ALL
        SELECT  Test.[Id]
        FROM Test
        INNER JOIN ChildrenCountRecursive AS tr ON Test.[ParentID] = tr.[Id]
     )
     -- End of recursive statement
      
     -- Check inspection job and all childs for vesseltype and metadata.
     SELECT @result = COUNT(ChildrenCountRecursive.Id)
     FROM ChildrenCountRecursive
    END
    
    IF (@result >= 2)
      SELECT @returnValue = @parentId
    ELSE
      SELECT @returnValue = null
    
    RETURN @returnValue 
END
GO
-- 9. Examine Inlineable on UDF
SELECT is_inlineable, NAME
FROM
    sys.sql_modules m
INNER JOIN 
    sys.objects o on o.object_id = m.object_id
WHERE 
    o.name = 'ChildrenFilter'
-- UDF is inlineable.

-- 10. Clear proc cache - and run query. 
DBCC FREESYSTEMCACHE(N'ALL');
GO
select * from Test 
WHERE Id IN (SELECT [dbo].[ChildrenFilter](Id))

-- Now it works...

Answer 3

I had the same issue. The first time (or first execution of a plan) the query fails. Changing the query causes this error again.

My solution was to edit (some minor code change like adding a ";") and recompile the scalar function. After recompile no errors occurred.