5

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

3

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
| improve this answer | |
  • 1
    Thanks Michael. Do you agree it should guess the lenght out of function definition and not the first string inside function? – jericzech Feb 7 at 11:43
  • Agree 100%. That's where the return type's explicitly stated. Anything else is just guessing. – Michael Green Feb 8 at 5:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.