TSQL Oddity with String_Agg

Question

This query returns the wrong results for the second string Agg function:

select
string_agg(t.name, ';') as BBB,
string_agg(t.name, ', ' ) as CCC
from sys.tables as t 

But it works if you alter t.name eg:

select
string_agg(t.name, ';') as BBB,
string_agg('' + t.name, ', ' ) as CCC
from sys.tables as t 

Any idea why? Is this a bug?

Answer 1

Interesting, it is a bug, sure. It checks the first argument and use it as a cache for the second.

It runs odd on sys.* objects

But the query runs successfully on other database tables.

I executed the exact query with execution plan.

And what is more interesting, when I executed the query on a user database on a different table, the query returns expected data.

There is no error in this case