STRING_AGG не ведет себя так, как ожидалось

У меня есть следующий запрос:

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?

Ответ 1

Да, это ошибка (tm), присутствующая (на момент написания) в версиях до SQL Server 2017 CU17 (исправлена в базе данных SQL Azure и SQL Server 2019 RC1 - учитывая время, прошедшее без обратного порта, эта ошибка может сохранится в SQL Server 2017 до 2019 года). В частности, часть в оптимизаторе, которая выполняет общее удаление подвыражений (гарантируя, что мы не вычисляем выражения больше, чем необходимо), неправильно считает все выражения формы STRING_AGG(x, <separator>) идентичными, пока совпадает x, независимо от того, что <separator> и объединяет их с первым вычисленным выражением в запросе.

Один из обходных путей - убедиться, что x не совпадает, выполнив какое-то (near-) преобразование идентичности на нем. Так как мы имеем дело со строками, конкатенация пустой будет делать:

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

Ответ 2

Не повторяйте себя *. Вы повторяете себя, используя MAX(...), LIST_AGG(...', ') и LIST_AGG(...' and '). Вы могли бы просто переписать свой запрос таким образом и могли бы получить лучший план:

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

Результат:

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

DB Fiddle

^{* Я не хотел повторять других, говоря, что это ошибка.}