Bug in SQL Server 2017

Question

I want to report a bug and workaround in SQL Server 2017. Microsoft suggested to open a post here, so... The issue has to do with string concatenation in combination with CAST and REPLACE. Something which occurs in my code quite often. Run this query:

USE master;

PRINT @@Version

DECLARE @l NVARCHAR(MAX) = '';

SELECT @l += CAST(s.object_id AS NVARCHAR(50)) + ' ' + REPLACE(s.name, 'x', 'y') + ', '
FROM sys.tables s
ORDER BY s.name;

PRINT '> ' + @l;

SET @l = '';

SELECT @l = @l + CAST(s.object_id AS NVARCHAR(50)) + ' ' + REPLACE(s.name, 'x', 'y') + ', '
FROM sys.tables s
ORDER BY s.name;

PRINT '> ' + @l;

On my servers the output is:

Microsoft SQL Server 2017 (RTM-CU6) (KB4101464) - 14.0.3025.34 (X64) 
    Apr  9 2018 18:00:41 
    Copyright (C) 2017 Microsoft Corporation
    Web Edition (64-bit) on Windows Server 2016 Standard 10.0 <X64> (Build 14393: ) (Hypervisor)

    > 1787153412 MSreplication_options, 117575457 spt_fallback_db, 133575514 spt_fallback_dev, 149575571 spt_fallback_usg, 1483152329 spt_monitor, 
    > 1483152329 spt_monitor,

So using @l += works, @l = @l + doesn't. When you leave out the CAST or the REPLACE the results are the same.

It is not a bug. It is a well known truth: [nvarchar concatenation / index / nvarchar(max) inexplicable behavior](https://stackoverflow.com/questions/15138593/nvarchar-concatenation-index-nvarcharmax-inexplicable-behavior/15163136#15163136) Correct way is to use `STRING_AGG` or `XML` trick for string concatenation. — Lukasz Szozda, Jun 05 '18 at 08:59

score 1 · Answer 1 · answered Jun 05 '18 at 09:05

1

See this answer but...

The code gives the same results (wrong) on SQL Server 2016 and 2014
It's OK in 2012

It how expressions are evaluated

@l += blah = blah is fully evaluated then concatenated
@l = @l + blah = concatenated left to right then assigned

Either way, using this technique to generate a CSV is an internal implementation that probably shouldn't be depended on

This works (added parenthesis around CAST+replace) because it forces correct evaluation order

SELECT 
    @l = @l + (CAST(s.object_id AS NVARCHAR(50)) + ' ' + REPLACE(s.name, 'x', 'y') + ', ')
FROM sys.tables s
ORDER BY s.name;

@l = @l + (blah) = blah is fully evaluated then concatenated

answered Jun 05 '18 at 09:05

gbn

422,506
82
585
676

Nice to have another workaround. It seems not a high priority bug at Microsoft. And I think is a pretty dangerous one, because the results are wrong, but there is no error message or something like that. So it might stay undetected for a while. – Hugo Jun 05 '18 at 13:50
The string concat is undocumented and assumed behaviour, but of course everyone does it. I have been known too although I tend to cross apply xml path now or string_agg depending on version – gbn Jun 05 '18 at 15:16

Lukasz Szozda · Answer 2 · 2018-06-05T15:23:11.203

0

As you mentioned that you use SQL Server 2017 I propose to use STRING_AGG and do not rely on undefined behavior.

SELECT STRING_AGG(CONCAT(s.object_id,' ',REPLACE(s.name, 'x', 'y')), ',')      
       WITHIN GROUP(ORDER BY s.name)
FROM sys.tables s

DBFiddle Demo

edited Jun 05 '18 at 15:23

answered Jun 05 '18 at 15:17

Lukasz Szozda

162,964
23
234
275

Bug in SQL Server 2017

2 Answers2