Início / dba / Perguntas / 294319
Accepted
Doug Coats
Asked: 2021-06-16 10:18:53 +0800 CST

CTEs aninhados que retornam resultados incorretos em determinadas situações

  • 772

OK, primeiro deixe-me dizer que recebi esse problema de:

Eu estava tentando ajudar a descobrir o problema, mas fiquei perplexo ao tentar depurar o código uma etapa de cada vez. Eu sei que o problema é devido aos CTEs aninhados (porque durante a depuração, se você despejar cada etapa aka cteX em tabelas temporárias, os resultados corretos são alcançados), mas não sabendo como eles funcionam "sob o capô", não posso explicá-lo de maneira sensata fora de "não funciona yo." Eu suspeito que tenha algo a ver com a forma como o compilador está tentando avaliá-los todos ao mesmo tempo durante o tempo de execução, mas sem mais contexto não posso dizer com certeza.

Minha pergunta é apenas tentar entender como eles funcionam sob o capô e como isso se relaciona com essa situação. Agora que estou envolvido, só quero entender o assunto para poder falar sobre ele no futuro e aprender algo divertido nesse meio tempo.

Quem responder aqui também pode fazer cross post no SO e responder lá também.

Configuração do código:

declare @t1 TABLE (ID varchar(max),Action varchar(max), DateTime datetime );
INSERT INTO @t1
Select *
from
(
VALUES 
('w2337','Open','2020-11-06 12:28:10.000'),
('w2337','Hold','2021-06-14 14:50:59.000'),
('w2337','Open','2021-06-14 14:51:26.000'),
('w2337','Hold','2021-06-15 14:50:59.000'),
('w2337','Open','2021-06-17 14:51:26.000'),
('w2337','Open','2021-06-18 14:51:26.000')

) t (ID, Action, DateTime);

with cte1 as (
select [ID],[Action],[DateTime]
,ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) as [RegIndex]
,DENSE_RANK () OVER (ORDER BY ID) as [Index by ID]
,ROW_NUMBER() OVER (PARTITION BY ID ORDER BY [DateTime]) as [Index by DateTimeID]
,CASE when [Action]='Hold' then ROW_NUMBER() OVER (PARTITION BY ID,Action ORDER BY DateTime) end as [TimesHeld]
FROM @t1 
)
,cte2 as (
select *, MAX([TimesHeld]) OVER (PARTITION BY ID ORDER BY RegIndex ROWS UNBOUNDED PRECEDING) as [FD] from cte1 
)
,cte3 as(
select *, CASE when [Action]='Open' then ROW_NUMBER() OVER (PARTITION BY ID,Action ORDER BY DateTime) end as [TimesOpened]
from cte2 
where FD is not null
)
select 
    a.*, ' ' as thing, b.DateTime  -- b.*   alternating between the direct column versus all is the issue
from cte3 a 
LEFT OUTER JOIN cte3 b
ON a.ID=b.ID and a.TimesHeld=b.TimesOpened 
where a.TimesHeld is not null and b.TimesOpened is not null

Detalhes:

Ao compilar as consultas abaixo, no LEFT OUTER JOIN final, se você selecionar b.* obterá os resultados corretos. No entanto, se você selecionar apenas uma coluna (datetime, por exemplo), os resultados não estarão corretos.

Correto:

insira a descrição da imagem aqui

Incorreta:

insira a descrição da imagem aqui

sql-server cte

2 respostas

  1. Best Answer

    O problema não são os resultados incorretos, mas sim a ordenação não determinística da ROW_NUMBERfunção da janela.

    Vou fazer referência ao ótimo artigo de Itzik Ben-Gan, definitivamente confira na íntegra quando tiver tempo: Números de linha com ordem não determinística

    A maneira como RegIndexé calculada em cte1 é explicitamente não determinística:

    ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) as [RegIndex]

    A ordenação por uma subconsulta que produz um valor constante permite que o otimizador saiba que a ordem não importa aqui:

    ...com esta solução, o otimizador do SQL Server reconhece que não há relevância de ordenação e, portanto, não impõe uma classificação desnecessária ou limita as escolhas do mecanismo de armazenamento àquelas que devem garantir a ordem

    A partir disso, FDin cte2 é baseado em RegIndex, e, portanto, também não é determinístico:

    MAX([TimesHeld]) OVER (PARTITION BY ID ORDER BY RegIndex ROWS UNBOUNDED PRECEDING) as [FD]

    Finalmente, a consulta em cte3 é filtrada pelo valor de FD:

    where FD is not null

    O resultado final é que um conjunto diferente de linhas pode terminar em cte3 dependendo de como a consulta é otimizada. E o número e os tipos de dados das colunas incluídas na SELECTlista podem influenciar absolutamente como a consulta é otimizada, resultando em resultados diferentes aqui.

    • 25

  2. Como você espera resultados determinísticos quando o primeiro cte ( cte1) usa:

    ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) as RegIndex

    e então essa coluna Regindex é usada nos seguintes CTEs?

    OVER (ORDER BY (SELECT NULL))basicamente diz ao otimizador "escolha qualquer ordem que desejar" e me dê os números das linhas.

    É muito esperado que da próxima vez que você executá-lo, você possa obter (como você faz) um pedido diferente - já que você diz novamente "escolha qualquer pedido que quiser" - e, portanto, resultados diferentes.

    • 12

relate perguntas