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?


person Tom Hunter    schedule 27.09.2018    source источник
comment
Кажется, это ошибка, она всегда возвращает 1-й STRING_AGG, независимо от того, как вы пишете CASE.   -  person dnoeth    schedule 27.09.2018
comment
Это красота ошибки оптимизатора. Более простая и наглядная реплика: 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). Каким бы ни был правильный результат, наверняка это не так.   -  person Jeroen Mostert    schedule 27.09.2018
comment
Не уверен, что это уместно, но.... LOL! Поскольку это не недостаток знаний с вашей стороны, а не реальная вещь, с которой многие столкнутся, я настоятельно рекомендую изменить заголовок вопроса, чтобы он был более точным, чем общий, не работающий должным образом, чтобы максимизировать пользу для всех.   -  person George Menoutis    schedule 27.09.2018
comment
В результирующем плане выполнения второй STRING_AGG полностью отсутствует, и вместо этого выражение привязывается к первому, как если бы CASE дважды сказал STRING_AGG([Language], ' and '). Любые последующие CASE также поглощаются. Похоже, с удалением подвыражений происходит что-то очень странное.   -  person Jeroen Mostert    schedule 27.09.2018
comment
Эта ошибка, по-видимому, специально настроена для STRING_AGG. Если ELSE изменить на 'blargh' + STRING_AGG(...), вы получите 'blarghFrench and German..., поэтому он неправильно объединяет второй STRING_AGG с первым. Простейший обходной путь — изменить выражение ELSE на STRING_AGG([Language] + '', ', ') — это аннулирует CSE, предполагая наличие ошибки, из-за которой CSE игнорирует второй аргумент STRING_AGG.   -  person Jeroen Mostert    schedule 27.09.2018
comment
@JeroenMostert Похоже, этот обходной путь станет хорошим ответом.   -  person IMSoP    schedule 27.09.2018
comment
Я хотел бы добавить, что запрос работает должным образом в базе данных SQL Azure, поэтому, надеюсь, исправление для локальной версии находится в разработке.   -  person Dan Guzman    schedule 27.09.2018


Ответы (1)


Да, это ошибка (tm), присутствующая во всех версиях 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
person Jeroen Mostert    schedule 27.09.2018
comment
Я позволил себе опубликовать этот обходной путь на Отзыв Azure - person Lukasz Szozda; 10.02.2019
comment
dba.stackexchange.com/a/257819/23463 указывает, что он все еще не работает, даже в CU17. - person Ross Presser; 22.01.2020
comment
@RossPresser: перепроверил и, конечно же, CU17 на самом деле это не исправляет. Исправил ответ. - person Jeroen Mostert; 22.01.2020