Início / dba / Perguntas / 267156
Accepted
sepupic
Asked: 2020-05-15 10:01:59 +0800 CST

GROUPING SETS retornando resultados inesperados com expressão calculada

  • 772

Aqui temos duas consultas semelhantes usando grouping sets onde a SELECTcláusula contém algumas expressões calculadas em agregação:

SELECT RN10, RN10 / 10, COUNT(*) FROM 
(
       SELECT RN, RN/10 AS RN10, RN/100 AS RN100 FROM 
       (
               SELECT RN = -1 + ROW_NUMBER() OVER (ORDER BY 1/0) 
               FROM master..spt_values
       ) A
) B
GROUP BY GROUPING SETS ((RN10), (RN10 / 10), ())
ORDER BY 1, 2

seu plano está aqui: primeiro plano de consulta

e

SELECT RN10, SUBSTRING(RN,3,99), COUNT(*) FROM 
(
       SELECT RN, SUBSTRING(RN,2,99) AS RN10 FROM 
       (
               SELECT RN = CAST(-1 + ROW_NUMBER() OVER (ORDER BY 1/0) AS VARCHAR(99)) 
               FROM master..spt_values
       ) A
) B
GROUP BY GROUPING SETS ((RN10), (SUBSTRING(RN,3,99)), ())
ORDER BY 1, 2

o plano correspondente está aqui: segundo plano de consulta

Ambas as consultas primeiro calculam alguma expressão para agregação, RN10 / 10no primeiro caso e SUBSTRING(RN,3,99)no segundo, então a mesma expressão é usada na SELECTcláusula, mas como o primeiro plano mostra ela é recalculada na primeira consulta e não na segunda.

Como resultado, temos NULLs no primeiro conjunto de resultados que é bastante inesperado:

resultados

Alguém pode explicar porque a primeira consulta faz o cálculo 2 vezes (uma na agregação e mais uma vez na final select) enquanto a segunda faz apenas uma vez?

sql-server execution-plan

1 respostas

  1. Best Answer

    Vou usar um exemplo mais simples onde fica claro para ver quais são os resultados esperados.

    CREATE TABLE Queen
(
   FirstName        VARCHAR(7),
   Surname          VARCHAR(7)
); 

INSERT INTO Queen
    (FirstName, Surname)
VALUES
    ('Brian',   'May'),
    ('Freddie', 'Mercury'),
    ('John',    'Deacon'),
    ('Roger',   'Taylor')
;

    Consulta 1

    SELECT Surname,
       NULL AS SurnameInitial,
       COUNT(*) AS Count
FROM   Queen
GROUP  BY Surname
UNION ALL
SELECT NULL AS Surname,
       LEFT(Surname,1) AS SurnameInitial,
       COUNT(*) AS Count
FROM   Queen
GROUP  BY LEFT(Surname,1)

    Resultados da Consulta 1

    +---------+----------------+-------+
| Surname | SurnameInitial | Count |
+---------+----------------+-------+
| Deacon  | NULL           |     1 |
| May     | NULL           |     1 |
| Mercury | NULL           |     1 |
| Taylor  | NULL           |     1 |
| NULL    | D              |     1 |
| NULL    | M              |     2 |
| NULL    | T              |     1 |
+---------+----------------+-------+

    Consulta 2

    SELECT Surname,
       LEFT(Surname,1) AS SurnameInitial,
       COUNT(*) AS Count
FROM   Queen
GROUP  BY GROUPING SETS ( ( Surname ), (LEFT(Surname,1)) ) 
ORDER BY SurnameInitial, Surname

    Resultados da Consulta 2

    Apesar do ORDER BY SurnameInitialfato de NULLclassificar primeiro no SQL Server as linhas com SurnameInitialas NULLsão ordenadas por último.

    +---------+----------------+-------+
| Surname | SurnameInitial | Count |
+---------+----------------+-------+
| Deacon  | D              |     1 |
| May     | M              |     1 |
| Mercury | M              |     1 |
| Taylor  | T              |     1 |
| NULL    | NULL           |     1 |
| NULL    | NULL           |     2 |
| NULL    | NULL           |     1 |
+---------+----------------+-------+

    As consultas 1 e 2 devem retornar os mesmos resultados. O problema é que o SQL Server decide tratá-lo como o seguinte SQL

    WITH GrpSets AS
(
SELECT Surname,
       COUNT(*) AS Count
FROM   Queen
GROUP  BY Surname
UNION ALL
SELECT NULL AS Surname,
       COUNT(*) AS Count
FROM   Queen
GROUP  BY LEFT(Surname,1)
)
SELECT Surname,
       LEFT(Surname,1) AS SurnameInitial,
       Count
FROM GrpSets

    Isso só parece um bug para mim (o sinalizador de rastreamento 8605 mostra que o dano já foi feito na representação inicial da árvore de consulta). RELATÓRIO DE ERRO .

    Consulta 3

    SELECT Surname,
       LEFT(FirstName,1) AS FirstNameInitial,
       COUNT(*) AS Count
FROM   Queen
GROUP  BY GROUPING SETS ( ( Surname ), (LEFT(FirstName,1)) )

    Resultados da Consulta 3

    +---------+------------------+-------+
| Surname | FirstNameInitial | Count |
+---------+------------------+-------+
| NULL    | B                |     1 |
| NULL    | F                |     1 |
| NULL    | J                |     1 |
| NULL    | R                |     1 |
| Deacon  | NULL             |     1 |
| May     | NULL             |     1 |
| Mercury | NULL             |     1 |
| Taylor  | NULL             |     1 |
+---------+------------------+-------+

    Query3 não atende ao padrão problemático de agrupamento em uma coluna e uma expressão que faz referência a essa coluna. De qualquer forma, nem seria possível que o mesmo problema ocorresse aqui porque a parte dos conjuntos de agrupamento é equivalente a

    SELECT Surname,
       NULL AS FirstNameInitial,
       COUNT(*) AS Count
FROM   Queen
GROUP  BY Surname
UNION ALL
SELECT NULL AS Surname,
       LEFT(FirstName,1) AS FirstNameInitial,
       COUNT(*) AS Count
FROM   Queen
GROUP  BY LEFT(FirstName,1)

    Isso não passa toda a coluna upstream (ou mesmo tem uma coluna FirstNameexclusiva garantida que pode ser passada), então não é possível que a expressão seja calculada em cima disso.FirstNameLEFT(FirstName,1)

    Pela mesma razão que você não vê o problema com (RN10), (SUBSTRING(RN,3,99)).

    @i-one raciocina nos comentários que é provável

    um bug na normalização (algebrização). Ele tem uma lógica que encontra uma correspondência para colunas e expressões não agregadas na SELECTlista em membros de GROUP BY. A mesma lógica aparentemente nos permite escrever, por exemplo,

    SELECT Surname, LEFT(Surname, 1), COUNT(*)
FROM   Queen
GROUP BY Surname

    sem ter que adicionar explicitamente a expressão calculada como abaixo

    GROUP BY Surname, LEFT(Surname, 1)

    Ou outro exemplo seria

    SELECT Surname,
       LEFT(Surname,1) AS SurnameInitial,
       LEFT(Surname,2) AS SurnamePrefix,
       COUNT(*) AS Count
FROM   Queen
GROUP  BY GROUPING SETS ( ( Surname ), (LEFT(Surname,1)) )

    Neste caso, o LEFT(Surname,2)é permitido e a única maneira de calcular seria fazê-lo da maneira problemática para o LEFT(Surname,1)caso.

    • 12

relate perguntas