HiveBrain v1.2.0
Get Started
← Back to all entries
debugMinor

Is Scalar UDF inlining broken?

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
brokenscalarinliningudf

Problem

This SQL code returns "a" instead of "asdfg":

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 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     300

Code 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     300

Context

StackExchange Database Administrators Q#259046, answer score: 3

Revisions (0)

No revisions yet.