Início / dba / Perguntas / 257808
Accepted
Steven Hibble
Asked: 2020-01-21 11:51:40 +0800 CST

STRING_AGG pode ser usado várias vezes com delimitadores diferentes?

  • 772

Eu estava fazendo alguma agregação de strings e notei o que parecia ser um bug para mim. STRING_AGGfoi usado duas vezes na mesma consulta com argumentos separadores diferentes. No entanto, ambos produziram os mesmos resultados (o "primeiro" separador é usado em ambos os casos). Parece acontecer apenas em algumas circunstâncias. Isso é um bug ou esse comportamento está documentado?

Primeiro, a configuração:

CREATE TABLE #Data
  ([Group] INT
 , Member  CHAR(1));

INSERT INTO #Data
VALUES (1, 'a'), (1, 'b')
     , (2, 'c'), (2, 'd');

De uma tabela vs. VALUESconstrutor. Sem WITHIN GROUP ORDER BY, está tudo bem. Com ele, o bug aparece

SELECT Commas = STRING_AGG(Member, ', ') --WITHIN GROUP(ORDER BY Member)
     , Colons = STRING_AGG(Member, '::') --WITHIN GROUP(ORDER BY Member)
FROM #Data;

SELECT Commas = STRING_AGG(Member, ', ') --WITHIN GROUP(ORDER BY Member)
     , Colons = STRING_AGG(Member, '::') --WITHIN GROUP(ORDER BY Member)
FROM (VALUES (1, 'a'), (1, 'b')
           , (2, 'c'), (2, 'd')) [v] ([Group], Member);

Adicionar um GROUP BYadiciona uma classificação e o erro existe para ambos os casos agora:

SELECT [Group]
     , Commas = STRING_AGG(Member, ', ')
     , Colons = STRING_AGG(Member, '::')
FROM #Data
GROUP BY [Group];

SELECT [Group]
     , Commas = STRING_AGG(Member, ', ')
     , Colons = STRING_AGG(Member, '::')
FROM (VALUES (1, 'a'), (1, 'b')
           , (2, 'c'), (2, 'd')) [v] ([Group], Member)
GROUP BY [Group];

Adicione que está definitivamente quebrado... como no fundo. Combine ambas as expressões em a HAVINGe você não receberá nada de volta. Use qualquer um sozinho e você obterá dados.

SELECT [Group]
     , Colons = STRING_AGG(Member, '::')
     , Commas = STRING_AGG(Member, ', ')
FROM #Data
GROUP BY [Group]
HAVING STRING_AGG(Member, ', ') LIKE '%, %'
   AND STRING_AGG(Member, '::') LIKE '%::%';

Mudar a ordem das colunas muda o separador utilizado.

Usei esses sinalizadores de rastreamento para investigar, mas não consigo encontrar nada sobre "otimizar" o segundo agregado.

OPTION (QUERYTRACEON 3604 -- Output info to client
      , QUERYTRACEON 8619 -- Show applied optimization rules
      , QUERYTRACEON 8606 -- Show logical query trees
      , QUERYTRACEON 8607 -- Show physical query tree
      , RECOMPILE);
sql-server sql-server-2017

1 respostas

  1. Best Answer

    Este é um erro. Esta resposta indica que ele foi corrigido no CU17, mas acabei de testar isso e achei negativo (e não há indicação de que a correção deveria ser retroportada aqui ).

    eu tentei

    SELECT [Group]
     , Commas = STRING_AGG(Member, ', ')
     , Colons = STRING_AGG(Member, '::')
FROM (VALUES (1, 'a'), (1, 'b')
           , (2, 'c'), (2, 'd')) [v] ([Group], Member)
GROUP BY [Group]
OPTION (QUERYTRACEON 3604 -- Output info to client
      , QUERYTRACEON 8607 -- Optimization output tree, before post-optimization rewrite phase.
      , QUERYTRACEON 7352 -- The final query tree after Post-optimization re-write.
      , RECOMPILE);
;

    Em 2017 CU17. Isso dá plano

    insira a descrição da imagem aqui

    Em 2019 não há escalar de computação e o Stream Aggregate tem valores definidos

    [Expr1010] = Scalar Operator(STRING_AGG([Union1009],', ')), 
[Expr1011] = Scalar Operator(STRING_AGG([Union1009],'::'))

    Em 2017 o Stream Aggregate definiu valor

    [Expr1010] = Scalar Operator(STRING_AGG([Union1009],', '))

    E há um Compute Scalar adicional com expressão

    [Expr1011] = Scalar Operator([Expr1010])

    A saída do sinalizador de rastreamento 8607é a mesma em 2017 e 2019, mas 7352difere, então presumo que esta seja uma reescrita de otimização de postagem com defeito.

    Como solução alternativa, você pode alterar a expressão para fazer um no-op como abaixo para evitar essa otimização defeituosa.

    SELECT [Group]
     , Commas = STRING_AGG(Member, ', ')
     , Colons = STRING_AGG('' + Member, '::')
    • 11

relate perguntas