SQL Server 2019 CU9 Scalar UDF Inlining issue

Question

Hi,
I´ve just updated our SQL environment (SQL Server 2019) to latest CU 9.
I run into a problem related to the new Scalar UDF Inlining functionality.
I have some nested scalar UDFs that runs into problem in some scenarios.

The SQL command returns error in SSMS and in the SQL Servers logs I get 0xc0000005 EXCEPTION_ACCESS_VIOLATION.

I don´t think this issue was present before CU9?

To reproduce the issue, please follow the steps below.

On a server running SQL Server 2019 15.0.4102.2 (CU9)

Run the following SQL commands:

CREATE DATABASE [TestDB]
GO

USE [TestDB]
GO

CREATE FUNCTION [dbo].[GetSub1] (@param NVARCHAR(4000))
RETURNS NVARCHAR(4000)
AS
BEGIN
    RETURN (@param + ' Test2')
END
GO

CREATE FUNCTION [dbo].[GetSub2] ()
RETURNS NVARCHAR(4000)
AS
BEGIN

    RETURN (
            SELECT TOP 1 name 
            FROM [sys].[columns]
            )
END
GO

CREATE FUNCTION [dbo].[GetMain1] ()
RETURNS NVARCHAR(4000)
AS
BEGIN
    RETURN (dbo.GetSub1(dbo.GetSub2()))
END
GO

This will create a new database and 3 UDFs.

After that run the following SQL command and you will get the error:

SELECT [dbo].[GetMain1] ()

If I rewrite one of the above UDFs like this it will work:

ALTER FUNCTION [dbo].[GetMain1] ()
RETURNS NVARCHAR(4000)
AS
BEGIN
    DECLARE @par NVARCHAR(4000)
    SET @par = dbo.GetSub2()

    RETURN (dbo.GetSub1(@par))

END
GO

It will also work if you disable the inlining functionality like this:

ALTER FUNCTION [dbo].[GetMain1] ()
RETURNS NVARCHAR(4000)
WITH INLINE = OFF
AS
BEGIN
    RETURN (dbo.GetSub1(dbo.GetSub2()))
END
GO

Answer 1

Hi @Viorel

Note: What I write here based on my personal experience and test and it is not documented well. I hope that I get it and explain it accurately.

As I started to say, this is a bit more complex than table valued function.

By default (without disabling the inline), when we execute a scalar UDF in SQL Server 2019, then SQL Server automatically "choose" if to execute it as a scalar expressions or as scalar subqueries that are executed inline (this part is documented). This is different from table valued function which are executed according to the structure of the function.

This means that the same function can be executed as inline once and as scalar expression in a different execution, and this is what you have here.

Check the execution plan when you execute the original sub function and the execution plan when you add your change. The Execution Plans are exactly the same, since the Server ignore the part you added when executing it directly (It "understand" that this part is not in-used").

This is why when you execute this directly you get inline UDF function.

CREATE OR ALTER FUNCTION [dbo].[GetSub2_testA] ()  
RETURNS NVARCHAR(4000)  
with inline = on  
AS  
BEGIN  
    --declare @r nvarchar(4000)  
    --select top(1) @r = name from sys.columns  
    ---- or: select top(1) @r = type_desc from sys.events         
    RETURN (  
            SELECT TOP 1 name   
            FROM [sys].[columns]  
            )  
END  
GO  
  
CREATE OR ALTER FUNCTION [dbo].[GetSub2_testB] ()  
RETURNS NVARCHAR(4000)  
with inline = on  
AS  
BEGIN  
    declare @r nvarchar(4000)  
    select top(1) @r = name from sys.columns  
    -- or: select top(1) @r = type_desc from sys.events         
    RETURN (  
            SELECT TOP 1 name   
            FROM [sys].[columns]  
            )  
END  
GO  
  
  
-- we get the same Execution Plans  
SELECT [dbo].[GetSub2_testA]()  
SELECT [dbo].[GetSub2_testB]()  
GO  

Now, execute the nested functions using your updated function

Check the execution plan and see that there is no inline execution when you execute the nested functions using your change.

When you are using your update then the server "notice" the extra part in the function and execute it as scalar expression. You can see that the EP is simple and does not include the content of the function and if you will check the SELECT action in the EP then you will not see any inline execution.

There are many cases that SQL Server parse query differently when it come as part of complex query. Seems like in nested functions it simply use the "function as it is" with the extra code which make it a multi statement function, while when you execute it directly then the server ignore the first statement and parse it as inline function.

I hope I succeed to explain the behavior well :-)

Answer 2

Hi,

After that run the following SQL command and you will get the error:

I will update the server and check again, but I confirm that there is no issue when I am using version 15.0.4083.2

I followed your code and it executed as expected.

----------Update after installing the last version----------

Using the same machine after installing updates.

SQL Server version 15.0.4102.2

I confirm! There is a BUG

Well done!

This is a big honor to be the first one who report a BUG. I do not want to take the credit from you, so you should report is :-)

Go to the following link and open a new report

https://feedback.azure.com/forums/908035-sql-server

Once you did it then come back and give us the link.

I will send direct email to one or two of the SQL Server developers to check it :-)

Well done!

Answer 3

Another workaround is to define and return an intermediate variable inside the GetSub2.

It works even if the variable is not returned:

ALTER FUNCTION [dbo].[GetSub2] ()
RETURNS NVARCHAR(4000)
with inline = on
AS
BEGIN

    declare @r nvarchar(4000)
    select top(1) @r = name from sys.columns
    -- or: select top(1) @r = type_desc from sys.events

    RETURN (
            SELECT TOP 1 name 
            FROM [sys].[columns]
            )
END
GO

Therefore, it seems that there is an inlining defect in CU9, which can be reported.

Answer 4

i am having the same issue with Microsoft SQL Server 2019 (RTM-CU18-GDR). So it has not been fixed. Unfortunately I cannot just use WITH INLINE=OFF as my code has to support 2012 - 2019. Will have to rewrite some nested functions in my views to use a single. Thanks for the writeup misery loves company.

Answer 5

just found this but i am confused. Mine is a fresh install of Microsoft SQL Server 2019 (RTM-CU18-GDR) and I still have the error.

https://support.microsoft.com/en-us/topic/f52d3759-a8b7-a107-1ab9-7fbee264dd5d

KB4538581 - FIX: Scalar UDF Inlining issues in SQL Server 2022 and 2019

• Access violation can occur when an object invokes a scalar inlineable UDF (UDF1) with a scalar inlineable UDF (UDF2) that's used as an input parameter after upgrading to CU9 (added in SQL Server 2019 CU11)