SQL Server Scalar UDF inline

Copper Contributor

I m currently using the below SQL Server 2019 with compatibility 150

Microsoft SQL Server 2019 (RTM-CU18-GDR) (KB5021124) - 15.0.4280.7 (X64) Jan 23 2023 12:37:13 Copyright (C) 2019 Microsoft Corporation Enterprise Edition: Core-based Licensing (64-bit) on Windows Server 2016 Datacenter 10.0 <X64> (Build 14393: ) (Hypervisor)

 

Seeing a weird behavior with one of the functions where its returning a different result set with 150 mode on. However if the function is modified with INLINE OFF or if it is called with hint OPTION (USE HINT('DISABLE_TSQL_SCALAR_UDF_INLINING')) its returning the correct result.


Did anyone face any similar issue with this version. I have come across issues that describe problems with UDF previously so wanted to check if anyone faced anything like this with this version. 

2 Replies
You haven't mentioned what the UDF do and how the "different results" look like, so difficult to guess nor do we know how to reproduce.
You have to provide more details.
hi, here is a demo version of the function and the sample call to it with output

-- function used to get value for a key
CREATE FUNCTION [dbo].[fn_ForDemo] (@mSettings NVARCHAR(1000),
@mKey NVARCHAR(100))
RETURNS NVARCHAR(100)
AS
BEGIN
DECLARE @mValue NVARCHAR(100)
DECLARE @mKeyValue NVARCHAR(200)

IF @mSettings IS NULL OR @mKey IS NULL
BEGIN
SET @mValue = NULL
END
ELSE
BEGIN
SELECT @mKeyValue = VALUE
FROM STRING_SPLIT(@mSettings, ';')
WHERE VALUE LIKE '%' + @mKey + '|:|%'

SELECT @mValue = Value
FROM STRING_SPLIT(@mKeyValue, ':')

IF @mValue LIKE '|%'
SET @mValue = SUBSTRING(@mValue, 2, LEN(@mValue) - 1)

IF @mValue LIKE '%|'
SET @mValue = SUBSTRING(@mValue, 1, LEN(@mValue) - 1)
END

RETURN COALESCE(@mValue, '')
END
GO

SELECT dbo.fn_ForDemo('abc|:|123|;|def|:|345|;|ghy|:|678|;|jkl|:|999|;|mno|:|888'
,'jkl')
--output= jkl(incorrect)


SELECT dbo.fn_ForDemo('abc|:|123|;|def|:|345|;|ghy|:|678|;|jkl|:|999|;|mno|:|888'
,'jkl')
OPTION (USE HINT('DISABLE_TSQL_SCALAR_UDF_INLINING'))
--output= 999 (correct)