Início / dba / Perguntas / 324065
Accepted
SlowMagic
Asked: 2023-02-27 01:07:31 +0800 CST

CTE com UNION ALL não funcionando como esperado

  • 772

A consulta abaixo parece simples e direta, mas produz resultados inesperados.


CREATE TABLE #NUMBERS
(
    N BIGINT
);

INSERT INTO #NUMBERS VALUES
(1),
(2),
(3),
(4),
(5),
(6),
(7),
(8),
(9)
;



WITH
A AS
(   
    -- CHOOSE A ROW AT RANDOM
    SELECT   TOP 1 *
    FROM     #NUMBERS            
    ORDER BY NewID()           
),
B AS
(
    SELECT A.N AS QUANTITY, 'METERS' AS UNIT FROM A
    
    UNION ALL

    SELECT A.N*100 AS QUANTITY, 'CENTIMETERS' AS UNIT FROM A
    
    UNION ALL

    SELECT A.N*1000 AS QUANTITY, 'MILLIMETERS' AS UNIT FROM A
    
    UNION ALL

    SELECT A.N*1000000 AS QUANTITY, 'MICRONS' AS UNIT FROM A

    UNION ALL

    SELECT A.N*1000000000 AS QUANTITY, 'NANOMETERS' AS UNIT FROM A
)
SELECT   *
FROM     B
ORDER BY B.QUANTITY
;

Eu esperaria que ele executasse o CTE A uma vez e, em seguida, carregasse esses resultados para o CTE B para produzir resultados mais ou menos assim:

QUANTIDADE UNIDADE
4 METROS
400 CENTÍMETROS
4000 MILÍMETROS
4000000 MICRONS
4000000000 NANOMETROS

No entanto, produz resultados como este:

QUANTIDADE UNIDADE
8 METROS
700 CENTÍMETROS
1000 MILÍMETROS
6000000 MICRONS
3000000000 NANOMETROS

Isso significa que ele está voltando e executando CTE A cinco vezes, uma vez para cada menção de A em CTE B. Isso não é apenas indesejado e não intuitivo, mas também parece desnecessariamente ineficiente.

O que está acontecendo e como um gênio CTE o reescreveria para produzir os resultados desejados?


BTW, as páginas de documentação da Microsoft sobre CTEs contêm esta declaração enigmática que pode ou não estar relacionada:

Se mais de um CTE_query_definition for definido, as definições de consulta deverão ser unidas por um destes operadores de conjunto: UNION ALL, UNION, EXCEPT ou INTERSECT.


Por fim, reescrever a consulta para eliminar CTE B não ajudou:

WITH
A AS
(   
    -- CHOOSE A ROW AT RANDOM
    SELECT   TOP 1 *
    FROM     #NUMBERS            
    ORDER BY NewID()           
)
SELECT   *
FROM     (
          SELECT A.N AS QUANTITY, 'METERS' AS UNIT FROM A
    
          UNION ALL

          SELECT A.N*100 AS QUANTITY, 'CENTIMETERS' AS UNIT FROM A
    
          UNION ALL

          SELECT A.N*1000 AS QUANTITY, 'MILLIMETERS' AS UNIT FROM A
    
          UNION ALL

          SELECT A.N*1000000 AS QUANTITY, 'MICRONS' AS UNIT FROM A

          UNION ALL

          SELECT A.N*1000000000 AS QUANTITY, 'NANOMETERS' AS UNIT FROM A

         ) AS B
ORDER BY B.QUANTITY
;
sql-server-2017

3 respostas

  1. Best Answer

    É útil pensar em expressões de tabela comuns mais como expressões e menos como tabelas (permanentes). Cada vez que você faz referência a uma expressão de tabela comum, ela deve se expressar novamente.

    Aqui está um exemplo simples:

    DECLARE
    @t table(id int);

INSERT 
    @t
(
    id
)
SELECT
    id = 1

SET STATISTICS XML ON;

WITH
    t AS
(
    SELECT
        t.id
    FROM @t AS t
)
SELECT
    t.*
FROM t 
JOIN t AS t1
  ON t1.id = t.id
JOIN t AS t2
  ON t2.id = t.id;

    O plano de consulta será mais ou menos assim, com uma junção para a variável de tabela base para cada junção entre a expressão de tabela comum:

    NOZES

    Da mesma forma, UNION (ALL) também produzirá uma referência a cada vez:

    WITH
    t AS
(
    SELECT
        t.id
    FROM @t AS t
)
SELECT
    t.*
FROM t 
UNION ALL
SELECT
    t.*
FROM @t AS t
UNION ALL
SELECT
    t.*
FROM @t AS t;

    NOZES

    Se você precisa estabilizar um resultado, você precisa usar um:

    • #tempmesa
    • @tablevariável
    • tabela permanente
    • 7

  2. Outras respostas explicaram o motivo do problema acontecer: basicamente, um CTE é apenas uma expressão que avalia quantas vezes for referenciada, fazendo com que Aretorne um valor diferente a cada avaliação.

    O que eu gostaria de abordar em minha resposta é esta parte da pergunta:

    como um gênio CTE o reescreveria para produzir os resultados desejados?

    Passear por alguns dos lugares onde os gênios da CTE se reúnem para discutir seus negócios relacionados à CTE pode ter me ensinado alguns truques que gostaria de compartilhar.

    O que eu acho que seria muito útil aqui para resolver o problema em questão são duas coisas:

    • o CROSS APPLYoperador;
    • o VALUESconstrutor de linha.

    Usando esses dois, eu reescreveria especificamente o BCTE assim:

    B AS
(
    SELECT   X.*
    FROM     A
    CROSS APPLY
    (
        VALUES
        (A.N, 'METERS'),
        (A.N*100, 'CENTIMETERS'),
        (A.N*1000, 'MILLIMETERS'),
        (A.N*1000000, 'MICRONS'),
        (A.N*1000000000, 'NANOMETERS')
    ) AS X (QUANTITY, UNIT)
)

    deixando o resto da consulta intacta.

    O caminho Bdefinido acima Aé referenciado (e avaliado) apenas uma vez. Ele ainda produz um conjunto de linhas em vez de uma única linha, porque substitui (com a ajuda de CROSS APPLY) a linha retornada por Aum conjunto de linhas, e o conjunto de linhas (construído por VALUES) basicamente usa A.Ncomo um argumento, produzindo o conjunto de valores desejado.

    Você pode testar a consulta completa em dbfiddle.uk .

    • 7

  3. CTEs nem sempre são materializados como muitos acreditam.

    • 1

relate perguntas