Início / dba / Perguntas / 159264
Accepted
Avarkx
Asked: 2016-12-28 07:05:07 +0800 CST

A coluna CTE causou um estouro - Ordenar apenas por!

  • 772
;WITH
cte_Date ( DateCode_FK ) AS (
    SELECT  DATEADD( DAY, 
                1 - ROW_NUMBER() OVER ( 
                        ORDER BY so1.object_id ),
                GETDATE() )
    FROM    sys.objects so1
    CROSS APPLY sys.objects so2 )
SELECT  TOP 10 d.DateCode_FK
FROM    cte_Date d
ORDER BY d.DateCode_FK DESC;

Nada muito interessante, mas estou recebendo uma mensagem de erro se eu executá-la com a ORDER BYcláusula:

Msg 517, Nível 16, Estado 1, Linha 4

Adicionar um valor a uma coluna 'datetime' causou um estouro.

No entanto, sem a ORDER BYcláusula, funciona muito bem. Além disso, se eu executar a consulta em outros catálogos contidos na mesma instância no mesmo servidor, a consulta funcionará bem com ou sem a ORDER BYcláusula.

Examinei as opções de configuração e os níveis de compatibilidade entre o catálogo afetado e um catálogo no qual a consulta é executada conforme o esperado, mas não encontrei nada que pudesse justificar a diferença. Alguém mais se deparou com um problema semelhante? Posso contornar isso por enquanto, mas, idealmente, preciso ser capaz de corrigir o problema, seja ele qual for.

Dica potencial - se você tiver um número relativamente grande de objetos em um catálogo (> 5000), você poderá reproduzir o erro... Isso está ocorrendo no meu maior catálogo e parece que se eu incluir um TOP em o CTE, o problema ORDER BY desaparece.

sql-server cte

2 respostas

  1. Best Answer

    O SQL Server não garante o tempo ou o número de avaliações para expressões escalares. Isso significa que uma consulta que pode lançar um erro dependendo da ordem das operações em um plano de execução pode (ou não) fazê-lo em tempo de execução.

    O script usa CROSS APPLYwhere CROSS JOINprovavelmente pretendido, mas o ponto é que o número potencial de linhas sobre o qual ROW_NUMBERé calculado depende do tamanho do sys.objectsproduto cruzado.

    Para um banco de dados com objetos suficientes, um estouro para o DATEADDresultado é um risco esperado. Por exemplo, isso pode ser reproduzido usando o banco de dados de exemplo AdventureWorks , que possui 637 entradas em formato sys.objects. O tamanho do produto vetorial é 637 * 637 = 405.769; o seguinte lança um erro de estouro:

    SELECT DATEADD(DAY, 1 - 405769, GETDATE());

    Pode-se argumentar que não há necessidade de materializar o resultado de uma expressão para linhas que não são retornadas (e, portanto, não geram um erro), mas não é assim que as coisas funcionam hoje.

    Considerar:

    • O mais alto ROW_NUMBER dará o menor valor para DateCode_FKna DATEADD(DAY, 1 - ROW_NUMBER()...expressão
    • a apresentação ORDER BYéDateCode_FK DESC
    • Apenas as primeiras 10 linhas na ordem de apresentação são necessárias

    Se o otimizador contivesse lógica para raciocinar que números de linha mais baixos levam a valores mais DateCode_FK altos, uma classificação explícita não seria necessária. Apenas dez linhas precisariam fluir pelo plano de execução. Os dez números de linha mais baixos são garantidos para produzir os dez DateCode_FKvalores mais altos.

    Independentemente disso, mesmo quando um Sort está presente, o argumento é que o SQL Server não deve gerar um erro porque nenhuma das dez linhas realmente retornadas está associada a um erro de estouro. Como eu disse acima, "não é assim que as coisas funcionam hoje" .

    Uma formulação alternativa que evita o erro (embora ainda não seja garantido - veja minha observação de abertura), torna a numeração de linhas determinística e usa CROSS JOIN:

    WITH cte_Date (DateCode_FK) AS
(
    SELECT TOP (10)
        DATEADD
        (
            DAY, 
            1 - ROW_NUMBER() OVER ( 
                ORDER BY 
                    so1.[object_id],
                    so2.[object_id]),
            GETDATE()
        )
    FROM sys.objects AS so1
    CROSS JOIN sys.objects AS so2
    ORDER BY
        ROW_NUMBER() OVER (
            ORDER BY 
                so1.[object_id],
                so2.[object_id]) ASC
)
SELECT TOP (10) -- Redundant TOP
    d.DateCode_FK
FROM cte_Date AS d
ORDER BY 
    d.DateCode_FK DESC;

    Cole o Plano

    Plano

    Resultados

    • 9

  2. Ressaca de feriado, eu acho.

    O ORDER BYna parte externa da consulta às vezes força a construção da lista completa para ordená-la corretamente. Com mais de 5 mil linhas retornadas de sys.objects, o DATETIMEtipo de dados transborda ao tentar criar datas com mais de 25 milhões de dias.

    • 1

relate perguntas