sql - Datediff 函数仅在 WHERE 子句中导致溢出？

Question

我有 2 个表，其中包含我尝试根据日期列加入的记录。由于日期列的精度，它们并不完全相同，所以我想出了一种方法，将一个表中的记录连接到另一个表中日期最近的记录(仅当日期在 1 分钟内彼此)。我已经在几个表上成功地运行了这个，但是我最近遇到了一些导致 Datediff SQL 函数导致溢出的数据。

这是我正在处理的相关数据:

---------------   #tmp_Job_Queue   ---------------
SELECT * INTO #tmp_Job_Queue
FROM (
SELECT N'130' AS [ID], N'Process 1' AS [ProcessName], N'2006-12-28 14:37:24.717' AS [DateCompleted] UNION ALL
SELECT N'133' AS [ID], N'Process 1' AS [ProcessName], N'2007-01-09 15:42:43.500' AS [DateCompleted] UNION ALL
SELECT N'219' AS [ID], N'Process 1' AS [ProcessName], N'2008-01-08 14:52:52.797' AS [DateCompleted] UNION ALL
SELECT N'234' AS [ID], N'Process 1' AS [ProcessName], N'2008-02-15 17:00:40.440' AS [DateCompleted] UNION ALL
SELECT N'278' AS [ID], N'Process 1' AS [ProcessName], N'2008-12-23 11:14:06.420' AS [DateCompleted] UNION ALL
SELECT N'281' AS [ID], N'Process 1' AS [ProcessName], N'2008-12-23 15:14:51.797' AS [DateCompleted] UNION ALL
SELECT N'286' AS [ID], N'Process 1' AS [ProcessName], N'2009-01-21 14:46:16.367' AS [DateCompleted] UNION ALL
SELECT N'288' AS [ID], N'Process 1' AS [ProcessName], N'2009-01-22 10:33:21.150' AS [DateCompleted] UNION ALL
SELECT N'290' AS [ID], N'Process 1' AS [ProcessName], N'2009-01-26 08:18:22.527' AS [DateCompleted] UNION ALL
SELECT N'340' AS [ID], N'Process 1' AS [ProcessName], N'2009-12-30 14:58:17.193' AS [DateCompleted] UNION ALL
SELECT N'349' AS [ID], N'Process 1' AS [ProcessName], N'2010-01-19 12:40:26.190' AS [DateCompleted] UNION ALL
SELECT N'390' AS [ID], N'Process 1' AS [ProcessName], N'2010-12-21 11:25:50.057' AS [DateCompleted] UNION ALL
SELECT N'399' AS [ID], N'Process 1' AS [ProcessName], N'2011-01-25 15:44:59.673' AS [DateCompleted] UNION ALL
SELECT N'440' AS [ID], N'Process 1' AS [ProcessName], N'2011-12-19 08:40:41.547' AS [DateCompleted] UNION ALL 
SELECT N'447' AS [ID], N'Process 1' AS [ProcessName], N'2012-01-12 14:15:00.800' AS [DateCompleted] UNION ALL
SELECT N'563' AS [ID], N'Process 1' AS [ProcessName], N'2013-12-19 14:39:39.123' AS [DateCompleted] UNION ALL
SELECT N'569' AS [ID], N'Process 1' AS [ProcessName], N'2014-01-13 11:26:27.007' AS [DateCompleted] UNION ALL
SELECT N'631' AS [ID], N'Process 1' AS [ProcessName], N'2014-12-16 10:07:53.907' AS [DateCompleted] UNION ALL
SELECT N'639' AS [ID], N'Process 1' AS [ProcessName], N'2015-01-08 16:10:50.010' AS [DateCompleted] UNION ALL
SELECT N'689' AS [ID], N'Process 1' AS [ProcessName], N'2015-12-17 13:43:28.687' AS [DateCompleted] UNION ALL
SELECT N'691' AS [ID], N'Process 1' AS [ProcessName], N'2015-12-18 12:15:18.367' AS [DateCompleted] UNION ALL
SELECT N'699' AS [ID], N'Process 1' AS [ProcessName], N'2016-01-12 12:27:09.523' AS [DateCompleted] UNION ALL
SELECT N'794' AS [ID], N'Process 1' AS [ProcessName], N'2017-10-09 14:58:06.503' AS [DateCompleted] UNION ALL
SELECT N'817' AS [ID], N'Process 1' AS [ProcessName], N'2017-10-12 08:54:57.820' AS [DateCompleted] ) t;

---------------   #tmp_Log   ---------------
SELECT * INTO #tmp_Log
FROM (
SELECT N'5' AS [ID], N'Process 2' AS [ProcessName], N'2008-02-15 17:00:39.550' AS [CreateDate] UNION ALL
SELECT N'190' AS [ID], N'Process 2' AS [ProcessName], N'2017-10-09 14:58:05.383' AS [CreateDate] UNION ALL
SELECT N'191' AS [ID], N'Process 2' AS [ProcessName], N'2017-10-12 08:54:57.820' AS [CreateDate] UNION ALL
SELECT N'17' AS [ID], N'Process 2' AS [ProcessName], N'2009-01-21 14:46:15.150' AS [CreateDate] UNION ALL
SELECT N'18' AS [ID], N'Process 2' AS [ProcessName], N'2009-01-21 16:24:20.913' AS [CreateDate] UNION ALL
SELECT N'19' AS [ID], N'Process 2' AS [ProcessName], N'2009-01-22 10:33:19.777' AS [CreateDate] UNION ALL
SELECT N'33' AS [ID], N'Process 2' AS [ProcessName], N'2010-01-19 12:40:24.710' AS [CreateDate] UNION ALL
SELECT N'41' AS [ID], N'Process 2' AS [ProcessName], N'2010-12-21 11:25:47.360' AS [CreateDate] UNION ALL
SELECT N'60' AS [ID], N'Process 2' AS [ProcessName], N'2011-12-19 08:40:38.167' AS [CreateDate] UNION ALL
SELECT N'67' AS [ID], N'Process 2' AS [ProcessName], N'2012-01-12 14:14:58.773' AS [CreateDate] UNION ALL
SELECT N'79' AS [ID], N'Process 2' AS [ProcessName], N'2012-12-17 15:49:49.890' AS [CreateDate] UNION ALL
SELECT N'84' AS [ID], N'Process 2' AS [ProcessName], N'2013-01-07 08:57:58.957' AS [CreateDate] UNION ALL
SELECT N'21' AS [ID], N'Process 2' AS [ProcessName], N'2009-01-26 08:18:21.213' AS [CreateDate] UNION ALL
SELECT N'47' AS [ID], N'Process 2' AS [ProcessName], N'2011-01-25 15:44:57.760' AS [CreateDate] UNION ALL
SELECT N'96' AS [ID], N'Process 2' AS [ProcessName], N'2013-12-19 14:39:25.513' AS [CreateDate] UNION ALL
SELECT N'102' AS [ID], N'Process 2' AS [ProcessName], N'2014-01-13 11:26:22.107' AS [CreateDate] UNION ALL
SELECT N'114' AS [ID], N'Process 2' AS [ProcessName], N'2014-12-16 10:07:32.987' AS [CreateDate] UNION ALL
SELECT N'121' AS [ID], N'Process 2' AS [ProcessName], N'2015-01-08 16:10:45.110' AS [CreateDate] UNION ALL
SELECT N'135' AS [ID], N'Process 2' AS [ProcessName], N'2015-12-17 13:43:23.220' AS [CreateDate] UNION ALL
SELECT N'137' AS [ID], N'Process 2' AS [ProcessName], N'2015-12-18 12:15:15.577' AS [CreateDate] UNION ALL
SELECT N'145' AS [ID], N'Process 2' AS [ProcessName], N'2016-01-12 12:27:07.797' AS [CreateDate] ) t;

下面是我用来获取日期最近的记录的查询:

DECLARE @QProcess VARCHAR(50) = 'Process 1'
DECLARE @LProcess VARCHAR(50) = 'Process 2'

;WITH timeDifferences AS (
    SELECT  Q.ID AS QueueID, L.ID AS LogID,
            ABS(DATEDIFF(MS, L.CreateDate, Q.DateCompleted)) AS DiffInMS
    FROM    #tmp_Job_Queue AS Q
    JOIN    #tmp_Log       AS L
      ON    Q.ProcessName = @QProcess AND
            L.ProcessName = @LProcess AND
            ABS(DATEDIFF(MI, L.CreateDate, Q.DateCompleted)) <= 1
)
SELECT *
FROM   timeDifferences AS T1
WHERE  DiffInMS = (SELECT MIN(DiffInMS) FROM timeDifferences AS T2 WHERE T2.QueueID = T1.QueueID)

通常它可以工作，但是对于这组特定的数据，它会给我一个错误。如果我取出 WHERE 子句，它可以正常工作，但是一旦我把它放回去(即使我做了一些简单的事情，比如 WHERE DiffInMS = 0 它开始给出这个错误:

The datediff function resulted in an overflow. The number of dateparts separating two date/time instances is too large. Try to use datediff with a less precise datepart.

我可以更改它以使用一些中间临时表并让它运行:

DECLARE @QProcess VARCHAR(50) = 'Process 1'
DECLARE @LProcess VARCHAR(50) = 'Process 2'

;WITH timeDifferences AS (
    SELECT  Q.ID AS QueueID, L.ID AS LogID,
            ABS(DATEDIFF(MS, L.CreateDate, Q.DateCompleted)) AS DiffInMS
    FROM    #tmp_Job_Queue AS Q
    JOIN    #tmp_Log       AS L
      ON    Q.ProcessName = @QProcess AND
            L.ProcessName = @LProcess AND
            ABS(DATEDIFF(MI, L.CreateDate, Q.DateCompleted)) <= 1
)
SELECT *
INTO   #unfilteredDifferences
FROM   timeDifferences

SELECT  *
FROM    #unfilteredDifferences AS T1
where   DiffInMS = (SELECT MIN(DiffInMS) FROM #unfilteredDifferences AS T2 WHERE T2.QueueID = T1.QueueID)

所以这比任何事情都更令人烦恼。我假设它与 SQL Server 处理 CTE 的方式有关。谁能解释为什么我会这样？

编辑

更具体地说，为什么当我删除 WHERE 子句时没有出现溢出错误，但重新引入 WHERE 子句会导致错误？我查看了 SQL 操作顺序并确认 JOIN 在 SELECT 之前被评估，因此应该摆脱 datediff 大于 1 分钟的行。然后 Datediff(MS, ....) 不会对会导致溢出的行执行。或者至少，这就是我认为它应该起作用的方式？

此外，我预计在删除 WHERE 子句时仍会遇到错误，因为所有行都将被评估，但这似乎不是正在发生的事情。

Answer 1

您的查询中唯一会产生溢出错误的部分是:

ABS(DATEDIFF(MS, L.CreateDate, Q.DateCompleted)) AS DiffInMS

我认为您的日期范围不够大，任何“微小”差异都不会引起问题。 SQL Server 2016 使用 datediff_big() 解决了这个问题。很高兴知道问题终于得到解决。

为什么你有时只看到这个？我的猜测是您没有处理所有 数据。如果您在外部查询中使用 order by，您可能总是会遇到问题。它可能埋在深处的某个行中。

如果我猜你不关心大差异，你可以用类似的东西替换逻辑:

(CASE WHEN ABS(DATEDIFF(minute, L.CreateDate, Q.DateCompleted)) < 2000000000 / (60 * 1000)
      THEN ABS(DATEDIFF(MS, L.CreateDate, Q.DateCompleted))
 ELSE) AS DiffInMS

这只会在安全的情况下有所作为。此版本返回 NULL，但您可以轻松地包括:

     ELSE 2000000000

如果你想要一个上限。