CODEWITHSUNDEEP
sql-server
Peppermallow
Peppermallow

Reputation: 53

TSQL Oddity with String_Agg

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?

Upvotes: 3

Views: 58

Answers (1)

Eralper
Eralper

Reputation: 6612

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.

enter image description here

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

enter image description here

Upvotes: 3

Related Questions