debugMinor
Is Scalar UDF inlining broken?
Viewed 0 times
brokenscalarinliningudf
Problem
This SQL code returns "a" instead of "asdfg":
Reproduced on both 15.0.2000.5 and 15.0.2070.41. Works fine in SQL Server 2017.
Workaround is to use WITH INLINE = OFF.
EDIT:
If you agree it's a bug, vote here please:
https://feedback.azure.com/forums/908035-sql-server/suggestions/39190126-sql-server-2019-scalar-functions-with-inline-are-b
EDIT 2:
CU2 for MS SQL Server 2019 is solving this issue :-)
CREATE FUNCTION [dbo].[GetPayload2] (@ID int) RETURNS VARCHAR(300) AS
BEGIN
IF @ID = 1
RETURN 'asdfg'
RETURN ''
END
GO
SELECT dbo.GetPayload2(1)Reproduced on both 15.0.2000.5 and 15.0.2070.41. Works fine in SQL Server 2017.
Workaround is to use WITH INLINE = OFF.
EDIT:
If you agree it's a bug, vote here please:
https://feedback.azure.com/forums/908035-sql-server/suggestions/39190126-sql-server-2019-scalar-functions-with-inline-are-b
EDIT 2:
CU2 for MS SQL Server 2019 is solving this issue :-)
Solution
Here's where the problem creeps in. The plan contains a Constant Scan defining the output.
CASE WHEN CASE WHEN (1) = (1) THEN (1) ELSE (0) END = (0) THEN '' ELSE CONVERT_IMPLICIT(varchar(1),CASE WHEN (1) = (1) THEN 'asdfg' ELSE NULL END,0) END
Why does it down-cast to varchar(1)? I guess it takes the length of the first string it encounters during optimization (presumably the empty string) with a floor of 1. If I change the fall-through result to
I think the best work-around would be to explicitly cast the result:
Bizarrely, it's also corrected by this:
CASE WHEN CASE WHEN (1) = (1) THEN (1) ELSE (0) END = (0) THEN '' ELSE CONVERT_IMPLICIT(varchar(1),CASE WHEN (1) = (1) THEN 'asdfg' ELSE NULL END,0) END
Why does it down-cast to varchar(1)? I guess it takes the length of the first string it encounters during optimization (presumably the empty string) with a floor of 1. If I change the fall-through result to
return 'yy' the implicit cast becomes varchar(2).I think the best work-around would be to explicitly cast the result:
RETURN convert(varchar(300), '')Bizarrely, it's also corrected by this:
declare @x sql_variant;
SELECT @x = dbo.GetPayload2(1);
select Result = @x, Property = SQL_VARIANT_PROPERTY(@x, 'MaxLength');
Result Property
-------- --------
asdfg 300Code Snippets
RETURN convert(varchar(300), '')declare @x sql_variant;
SELECT @x = dbo.GetPayload2(1);
select Result = @x, Property = SQL_VARIANT_PROPERTY(@x, 'MaxLength');
Result Property
-------- --------
asdfg 300Context
StackExchange Database Administrators Q#259046, answer score: 3
Revisions (0)
No revisions yet.