主页 / dba / 问题 / 159264
Accepted
Avarkx
Asked: 2016-12-28 07:05:07 +0800 CST

CTE 列导致溢出 - 仅限 Order By！

  • 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;

没有什么特别有趣的查询，但如果我使用以下ORDER BY子句运行它，我会收到一条错误消息：

消息 517，级别 16，状态 1，第 4 行

向“日期时间”列添加值导致溢出。

但是，没有该ORDER BY子句，它运行得很好。此外，如果我在同一台服务器上的同一实例中包含的其他目录上运行查询，无论有没有该ORDER BY子句，查询都可以正常运行。

我查看了受影响的目录和查询按预期运行的目录之间的配置选项和兼容性级别，但没有发现任何可能保证差异的内容。还有其他人遇到过类似的问题吗？我现在可以解决它，但理想情况下需要能够解决问题，无论它是什么。

潜在提示 - 如果您在目录中有相对大量的对象 ( > 5000 )，您 - 可能 - 能够重现错误......这发生在我最大的目录上并且看起来如果我在中包含 TOP CTE，ORDER BY 问题消失了。

sql-server cte

2 个回答

  1. Best Answer

    SQL Server 不保证标量表达式的计算时间或计算次数。这意味着可能会根据执行计划中的操作顺序抛出错误的查询可能（或可能不会）在运行时这样做。

    该脚本使用的CROSS APPLY位置CROSS JOIN可能是预期的，但要点是ROW_NUMBER计算 的潜在行数取决于sys.objects叉积的大小。

    对于具有足够对象的数据库，DATEADD结果溢出是预期的风险。例如，这可以使用AdventureWorks示例数据库重现，该数据库在sys.objects. 叉积的大小为637 * 637 = 405,769；以下引发溢出错误：

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

    有人可能会争辩说，没有必要为未返回的行具体化表达式的结果（因此不会抛出错误），但这不是今天的工作方式。

    考虑：

    • 最高 的ROW_NUMBER将给出表达式中的最低值DateCode_FKDATEADD(DAY, 1 - ROW_NUMBER()...
    • 介绍ORDER BY是DateCode_FK DESC
    • 只需要显示顺序的前 10 行

    如果优化器包含逻辑来推断较低的行号导致较高 DateCode_FK的值，则不需要显式排序。执行计划只需要十行。十个最低的行号保证产生十个最高的DateCode_FK值。

    不管怎样，即使存在 Sort，争论点是 SQL Server 不应抛出错误，因为实际返回的十行中没有一行与溢出错误相关联。正如我上面所说，“这不是今天的工作方式”。

    避免错误的替代公式（尽管仍然不能保证这样做 - 请参阅我的开场白），使行编号确定，并使用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;

    粘贴计划

    计划

    结果

    • 9

  2. 我猜是假期宿醉。

    查询的ORDER BY外部部分有时会强制构建完整列表以便正确排序。从 返回 5k+ 行时sys.objects，DATETIME数据类型在尝试构建超过 25MM 天的日期时溢出。

    • 1

相关问题