Início / dba / Perguntas / 318188
Accepted
Rebecca
Asked: 2022-10-14 01:44:07 +0800 CST

Por que meu ORDER BY em STRING_AGG nem sempre funciona?

  • 772

Eu tenho uma tabela que consiste em um ID de registro, um ID de grupo (vinculando 1 ou mais registros em um grupo) e um valor de hash para cada registro.

CREATE TABLE HashTable(
    RecordID VARCHAR(255),
    GroupIdentifier VARCHAR(255),
    Hash VARCHAR (255),
    GroupHashList VARCHAR(4000)
)

(Eu sei que esta não é uma tabela eficiente, mas é apenas uma tabela temporária para os propósitos deste exemplo).

Eu quero gerar um hash para cada grupo, então pensei que a maneira mais simples seria concatenar os hashes de cada registro do grupo. RecordIDs são exclusivos, mas o que esses registros se relacionam não são necessariamente exclusivos, portanto, os hashes podem ser duplicados. O objetivo disso é sinalizar grupos totalmente duplicados, ou seja, um grupo onde todos os registros desse grupo são duplicados de todos os registros de outro grupo. A GUI precisa que todos os membros do grupo tenham o mesmo hash para reconhecê-los como um grupo duplicado.

Estou usando STRING_AGG para concatenar os hashes individuais dos registros no grupo e classificá-los pelo hash para garantir que obtenha a mesma sequência de caracteres para grupos duplicados. Na verdade, não me importo com a ordem dos hashes, desde que seja sempre a mesma. Quando executo como uma consulta SELECT, funciona bem e posso ver strings idênticas para grupos duplicados. Quando eu pego essa mesma consulta SELECT e a coloco em uma consulta UPDATE, a ordenação parece se perder.

SELECT STRING_AGG([Hash],';') WITHIN GROUP (ORDER BY [Hash] ASC) 
FROM HashTable
GROUP BY [GroupIdentifier]

Isso fornece os resultados (para um exemplo de par de grupos duplicados):

73F294873462B2BA0E930FD16DCCB7;90E749375DF806CB6E3F5CA48FFA38;E44256CE7CFCB971EB679BAC25A697
73F294873462B2BA0E930FD16DCCB7;90E749375DF806CB6E3F5CA48FFA38;E44256CE7CFCB971EB679BAC25A697

Quando coloco esse mesmo código na consulta UPDATE, ele não os classifica corretamente:

UPDATE HashTable
SET GroupHashList = c.HashList
FROM HashTable
INNER JOIN (
    SELECT (STRING_AGG([Hash],';') WITHIN GROUP (ORDER BY [Hash] ASC)) AS [HashList],
        [GroupIdentifier]
    FROM HashTable
    GROUP BY [GroupIdentifier]) c
ON c.[GroupIdentifier] = HashTable.[GroupIdentifier]

Resultados para os mesmos dois grupos que são gravados na tabela:

73F294873462B2BA0E930FD16DCCB7;90E749375DF806CB6E3F5CA48FFA38;E44256CE7CFCB971EB679BAC25A697
73F294873462B2BA0E930FD16DCCB7;E44256CE7CFCB971EB679BAC25A697;90E749375DF806CB6E3F5CA48FFA38

o que estou perdendo?

O que estou recebendo pela primeira vez é

Hash1; Hash2; Hash3
Hash1; Hash2; Hash3

Mas quando está na consulta UPDATE, recebo

Hash1; Hash2; Hash3
Hash1; Hash3; Hash2

A consulta de atualização é classificada por ID de registro, embora eu não saiba se isso é coincidência. ( https://dbfiddle.uk/CPG1-z2l )

sql-server

1 respostas

  1. Best Answer

    Isso parece ser um bug no otimizador.

    O otimizador, tendo percebido que a junção é uma junção automática, está transformando-a em um agregado de janela. Ele pode fazer isso apesar STRING_AGGde não estar disponível como um agregado de janela. A regra é chamada GenGbApplySimple e permite que uma autojunção seja convertida em um agregado de janela. Não há nada especificamente errado com isso até agora.

    Plano

    PasteThePlan

    O problema é que a agregação está acima do valor errado. É agregar o valor externo ao invés do interno.

    Se você fornecer aliases diferentes às duas referências, um exame cuidadoso do plano de consulta revelará o bug.

    STRING_AGG([dbo].[HashTable].[Hash] as [HT1].[Hash],'')
WITHIN GROUP (ORDER BY [HT2].[Hash])

    O outro problema é que os agregados usados ​​com essa regra (por exemplo MIN, MAX, , AVG) não têm uma WITHIN GROUPordenação para satisfazer, então o plano de substituição não leva em conta isso. Parece provável que STRING_AGGnão se destinava a funcionar as regras do GbApply , ou seria necessário trabalhar para torná-lo compatível (honrando a solicitação de classificação).

    Como você pode ver abaixo, o Ordenar somente ordena pela coluna de correlação GroupIdentifier, não pela Hashcoluna usada no arquivo WITHIN GROUP.

    <OrderBy>
  <OrderByColumn Ascending="1">
    <ColumnReference
      Database="[...]"
      Schema="[dbo]"
      Table="[HashTable]"
      Alias="[HT1]"
      Column="GroupIdentifier">
    </ColumnReference>
  </OrderByColumn>
</OrderBy>

    Se você for um sysadmin, poderá desativar essa regra para a consulta usando o seguinte arquivo OPTION.

    OPTION (QUERYRULEOFF GenGbApplySimple)

    Como solução alternativa, uma opção para evitar que essa otimização seja aplicada é usar umOUTER APPLY

    UPDATE HT1
SET GroupHashList = C.HashList
OUTPUT inserted.*
FROM HashTable AS HT1
OUTER APPLY
(
    SELECT
        HashList =
            STRING_AGG(HT2.[Hash], ';')
                WITHIN GROUP (ORDER BY HT2.[Hash] ASC)
    FROM HashTable AS HT2
    WHERE HT2.GroupIdentifier = HT1.GroupIdentifier
) C;

    Isso oferece uma autojunção bastante simples com um Stream Aggregate .

    db<>violino

    Eu sugiro fortemente que você registre isso como um bug com a Microsoft.

    Você também pode deixar comentários , mas isso normalmente não leva a uma resposta específica.

    Como um aparte, você deve seguir as regras de alias sugeridas por Conor Cunningham ao escrever UPDATEinstruções de várias tabelas:

    A cláusula não-ANSI FROM (que você está usando aqui) tem comportamentos de ligação específicos que podem ou não ser o que você espera. Sugiro que você comece alias as 3 referências a hashtable para serem diferentes e, em seguida, verifique se está se referindo explicitamente à que deseja. Pode ser (estou supondo) que esteja vinculado a um diferente do que você pensa e forneça uma saída indesejada como resultado.

    • 22

relate perguntas