SQL Server 2019 CU9 Scalar UDF Inlining issue

Question

question

Stefan-1807 asked Mar 17, '21 pituach edited Mar 19, '21

SQL Server 2019 CU9 Scalar UDF Inlining issue

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

sql-server-general

5 |1600 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Answer 1 · 2021-03-17T16:51:35.993Z

pituach answered Mar 17, '21 pituach commented Mar 18, '21

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!

· 4

5 |1600 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Stefan-1807 · Mar 18, 2021 at 07:29 AM

Thanks a lot @pituach
Here is the link:
https://feedback.azure.com/forums/908035-sql-server/suggestions/42946974-nested-inline-udf-scalar-functions-sql-server-15-0

Please let me know if you here something from the SQL Server developers :)

1 ·

pituach Stefan-1807 · Mar 18, 2021 at 07:33 PM

Great work 👍

0 ·

Stefan-1807 · Mar 17, 2021 at 05:19 PM

Thanks @pituach
Please let me know how it goes after upgrade.

0 ·

pituach Stefan-1807 · Mar 17, 2021 at 05:34 PM

I updated my answer :-)

You are right 👍

Go, report it as I explain in the updated answer and come back with a link to the report

This is definitely a BUG

1 ·

Answer 2 · 2021-03-17T16:54:24.593Z

Viorel-1 answered Mar 17, '21 pituach commented Mar 19, '21

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.

· 4

5 |1600 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Stefan-1807 · Mar 17, 2021 at 05:20 PM

Thanks @Viorel-1

Yes, it seems like a defect.

0 ·

pituach · Mar 17, 2021 at 08:20 PM

@Viorel-1 , This is not another workaround but the same as @Stefan-1807 used for his first workaround. You simply use multi statement function which mean that it is not an inline function (even if you are using "with inline = on" which has no meaning here since as I mentioned this is a multi statement function).

Basically seems like there is an issue with nested inline User-defined Scalar Functions in SQL Server version 15.0.4102.2

0 ·

Viorel-1 pituach · Mar 18, 2021 at 09:12 AM

But if I execute separately ‘SELECT [dbo].[GetSub2]()’, using the modified GetSub2, and check the Actual Execution Plan in Management Studio, I see that the SELECT block displays “Contains Inline Scalar Tsql Udfs: True”, which, according to some articles, is an indicator of inlined function call. (This flag is also reported in case of original single-statement GetSub2).

I am not sure that documentation requires just a single statement.

In addition, the meaning of ‘with inline = on’ option in general becomes unclear. (However, according to documentation, if the function is not inlineable, then ‘with inline = on’ reports an error, which did not happen in above experiments).

0 ·

pituach Viorel-1 · Mar 19, 2021 at 02:57 AM

Hi :-)

This is a bit more complex than table valued function. I cannot explain in 1000 char so I cannot add it as comment (I hate this feature in the QnA forums!!! It drive me nuts) I will try to explain in a new "answer"

0 ·

Answer 3 · 2021-03-19T03:29:05.937Z

pituach answered Mar 19, '21 pituach edited Mar 19, '21

Hi @Viorel-1

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 :-)

image.png (36.8 KiB)

image.png (15.2 KiB)

5 |1600 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

question