应由WHERE子句删除的更新语句处理记录

Question

我犯了一个很奇怪的错误。考虑下表：

CREATE TABLE #MyTable (
    Key1 INT , Key2 INT ,
    x SMALLINT , y INT , z INT ,
    a FLOAT , b FLOAT , c SMALLINT , s FLOAT
)

-- insert many records

CREATE UNIQUE CLUSTERED INDEX CI ON #MyTable ( Key1 , Key2 )

由于某种原因，下面的update语句试图将其除以零。只有在c=0或c=1的情况下才能发生这种情况。WHERE子句明确指定了c>1。

-- this fails with divide-by-zero error
UPDATE  #MyTable
SET s = CASE
        WHEN a - SQUARE ( b ) / c <= 0 THEN 0
        ELSE ( a - SQUARE ( b ) / c ) / ( c - 1 )
    END
WHERE   x <= 622 AND c > 1 AND ( y > 0 OR z > 0 )

如果我冗余地检查我的c<=1表达式中的CASE，这个问题就完全消除了：

-- this completes without an error
UPDATE  #MyTable
SET s = CASE
        WHEN ( c <= 1 ) OR ( a - SQUARE ( b ) / c <= 0 ) THEN 0
        ELSE ( a - SQUARE ( b ) / c ) / ( c - 1 )
    END
WHERE   x <= 622 AND c > 1 AND ( y > 0 OR z > 0 )

以前有人遇到过这种情况吗？为什么Server会用c>1来触摸记录？

如果表中没有索引，也可以避免这个问题(在后面的步骤中，索引是有用的)。为什么索引的存在会导致忽略WHERE子句中的一个条件？

Answer 1

对于SQL Server将如何处理查询，您不应作任何假设，除非:您应该始终假定Server可以以与在屏幕上显式写入查询的方式不同的方式处理查询。而且，这种行为也可以根据影响新计划是否将用于下一次执行同一查询的任何因素进行更改，因此，如果您应用提示或以任何方式更改查询，或者添加或删除索引，错误就会消失，请不要假设错误将于明天返回。

在这种情况下，Server在从WHERE子句中删除行之前正在处理计算。避免这种情况的方法是，正如您所说的那样，确保这些行也在CASE表达式(not语句)中被过滤掉。

一种更常见但类似的方法是这样的：

SELECT DATEPART(MONTH, varchar_column)
FROM dbo.some_table
WHERE ISDATE(varchar_column) = 1;

在许多情况下，您将得到一条错误消息，因为Server试图针对列中的一些值应用日期函数，而这些值并不是日期(而这种尝试发生在筛选之前)。解决方法很繁琐--使用CASE表达式--但是，除非您有其他方法来验证该列的适配性(例如，计算列或首先修复数据类型)，否则这是必要的。请记住，即使是这个在某些情况下可能无法“短路”。

SELECT CASE WHEN ISDATE(varchar_column) = 1 
  THEN DATEPART(MONTH, varchar_column) END
FROM dbo.some_table
WHERE ISDATE(varchar_column) = 1;

Erland Sommarskog在以下反馈项目中对此作了更全面的解释：

• 服务器不应引发不合逻辑的错误