SQL Server 2019 Bug with `SELECT @local_variable` syntax used to build a string via concatenation
We are testing our application with SQL Server 2019 so that we can formally publish that we support it. While testing, we encountered a major behavioral difference between SQL Server 2017 and SQL Server 2019 in one of our user-defined functions.
The function takes two parameters that comprise part of a WHERE clause. Then the SELECT caluse performs the concatenation as follows:
SELECT @Output =
WHEN '' THEN c2.name
ELSE @Output + '; ' + c2.name
In SQL Server 2017, this behaves as expected and concatenates all of the c2.name values into a semicolon delimited string. However, in SQL Server 2019, the resulting string contains only the first item.
The attached SQL script (with .txt extension) has the schema, sample data, and the user-defined function (problemFun) that will show the correct behavior in SQL Server 2017 and reproduce the incorrect behavior in SQL Server 2019. The script also includes a problemFun_v2 where I commented out the ORDER BY. This causes the concatenation to work correctly. That workaround is not acceptable, but I mention it in case it helps you identify the cause of the issue.
Here are the specific SQL Server versions I am working with:
Microsoft SQL Server 2019 (RTM-CU1) (KB4527376) - 15.0.4003.23 (X64) Dec 6 2019 14:53:33 Copyright (C) 2019 Microsoft Corporation Developer Edition (64-bit) on Windows 10 Pro 10.0 <X64> (Build 18362: ) (Hypervisor)
Microsoft SQL Server 2017 (RTM-GDR) (KB4505224) - 14.0.2027.2 (X64) Jun 15 2019 00:26:19 Copyright (C) 2017 Microsoft Corporation Developer Edition (64-bit) on Windows 10 Pro 10.0 <X64> (Build 18362: ) (Hypervisor)
Martin, I just found your SO post explaining this: https://stackoverflow.com/a/15163136/178424
That makes total sense.
We support SQL Server 2012-2019, so we can't use STRING_AGG.
This is is a significant behavior difference between SQL Server 2017 and SQL Server 2019 that is not anywhere warned about in release notes or otherwise documented. That's within the normal range for the definition of a bug.
Martin Smith commented
This is not a bug. You should use a supported string aggregation method. Some responses compiled from the old connect site are here https://stackoverflow.com/a/15163136/73226. As you are on 2019 you should use STRING_AGG
Note: This bug ONLY occurs inside a user-defined function. If you take the same SQL out of the function and replace RETURN with SELECT (and substitute values for the function parameters), it yields the expected result: 'Group 1; Group 2; Group 3'