字符串聚合函数（STRING_AGG）表现不如预期

Question

我有以下查询：

WITH cteCountryLanguageMapping AS (
    SELECT * FROM (
        VALUES
            ('Spain', 'English'),
            ('Spain', 'Spanish'),
            ('Sweden', 'English'),
            ('Switzerland', 'English'),
            ('Switzerland', 'French'),
            ('Switzerland', 'German'),
            ('Switzerland', 'Italian')
    ) x ([Country], [Language])
)
SELECT
    [Country],
    CASE COUNT([Language])
        WHEN 1 THEN MAX([Language])
        WHEN 2 THEN STRING_AGG([Language], ' and ')
        ELSE STRING_AGG([Language], ', ')
    END AS [Languages],
    COUNT([Language]) AS [LanguageCount]
FROM cteCountryLanguageMapping
GROUP BY [Country]

我原本期望瑞士的语言栏内的值是逗号分隔的，例如：

  | Country     | Languages                                 | LanguageCount
--+-------------+-------------------------------------------+--------------
1 | Spain       | Spanish and English                       | 2
2 | Sweden      | English                                   | 1
3 | Switzerland | French, German, Italian, English          | 4

但我得到了以下输出（4个值由and隔开）：

  | Country     | Languages                                 | LanguageCount
--+-------------+-------------------------------------------+--------------
1 | Spain       | Spanish and English                       | 2
2 | Sweden      | English                                   | 1
3 | Switzerland | French and German and Italian and English | 4

我错过了什么？

---

这是另一个例子：

SELECT y, STRING_AGG(z, '+') AS STRING_AGG_PLUS, STRING_AGG(z, '-') AS STRING_AGG_MINUS
FROM (
    VALUES
        (1, 'a'),
        (1, 'b')
) x (y, z)
GROUP by y

  | y | STRING_AGG_PLUS | STRING_AGG_MINUS
--+---+-----------------+-----------------
1 | 1 | a+b             | a+b

这是SQL Server中的一个错误吗？

- Tom Hunter

---

似乎存在一个Bug，无论你如何编写CASE语句，它总是返回第一个STRING_AGG。 - dnoeth

这是一个优化器错误的美妙例子。更简单、更引人注目的复现方式：CASE COUNT([Language]) WHEN 1234567 THEN STRING_AGG([Language], ' and ') ELSE STRING_AGG([Language], ', ') END AS [Languages]（使用 1234567 案例）和 CASE COUNT([Language]) WHEN 1234567 THEN STRING_AGG([Language], ' and ') END AS [Languages]（省略 ELSE -- 现在匹配失败，表达式变成了 NULL）。无论“正确”的结果应该是什么，肯定不是这个。 - Jeroen Mostert

不确定这是否合适，但是...哈哈！既然这不是你的知识缺乏，而是许多人会遇到的真实问题，我强烈建议更改问题标题，使其更准确，而不是一个通用的“未按预期工作”，以最大化造福所有人。 - George Menoutis

这个 bug 似乎是专门针对 STRING_AGG 调整的。如果将 ELSE 更改为 'blargh' + STRING_AGG(...)，则会得到 'blarghFrench and German...，因此它不正确地将第二个 STRING_AGG 与第一个统一起来。最简单的解决方法是将 ELSE 表达式更改为 STRING_AGG([Language] + '', ', ') -- 这样可以避免 CSE，表明 CSE 忽略了 STRING_AGG 的第二个参数，从而暗示存在一个 bug。 - Jeroen Mostert

@JeroenMostert 那个解决方法听起来很不错，可以作为一个好的答案。 - IMSoP

Answer 1

是的，这是一个Bug（商标），存在于所有SQL Server 2017版本中（截至撰写本文时）。它已在Azure SQL Server和2019 RC1中得到修复。具体而言，在执行常见子表达式消除（确保我们不会多次计算表达式）的优化器部分错误地将形式为STRING_AGG(x，<separator>)的所有表达式视为相同，只要x匹配，无论<separator>是什么，都将这些表达式与查询中第一个计算的表达式统一起来。

一种解决方法是确保x不匹配，通过对其进行某种（近似）身份转换来实现。由于我们正在处理字符串，因此连接一个空字符串就可以：

SELECT y, STRING_AGG(z, '+') AS STRING_AGG_PLUS, STRING_AGG('' + z, '-') AS STRING_AGG_MINUS
FROM (
    VALUES
        (1, 'a'),
        (1, 'b')
) x (y, z)
GROUP by y

- Jeroen Mostert

---

我允许自己在Azure反馈上发布此解决方法。 - Lukasz Szozda

https://dba.stackexchange.com/a/257819/23463指出即使在CU17中，它仍然存在问题。 - Ross Presser

@RossPresser：重新测试了一下，确实，CU17并没有真正解决这个问题。已经修改了答案。 - Jeroen Mostert