TSQL除以零错误，尽管没有任何列包含0

Question

我一直在尝试理解为什么我的SQL查询会出现“除以零遇到”(Msg 8134)的错误，但我可能漏掉了某些东西。对于下面特定的情况，我想知道为什么会出现这种情况，我不需要使用NULLIF、CASE WHEN...或类似的方法，因为我已经知道它们（当然可以在像下面这种情况下使用它们）。

我有一个类似的计算列的SQL语句：

SELECT
    TotalSize,
    FreeSpace,
    (FreeSpace / TotalSize * 100)
FROM
    tblComputer
...[ couple of joins ]...
WHERE
    SomeCondition = SomeValue

运行此语句会出现上述错误消息，这本身并不是问题 - 显然 TotalSize 可能为0，从而导致了错误。

我不明白的是，当我注释掉计算列时，我没有任何 TotalSize 列为0的行，我再次确认这不是问题所在。

然后我想，由于某种原因，在实际筛选 where 子句条件之前，整个结果集可能会执行列计算，但是这样做 a) 在我看来不合理，b) 当尝试使用测试设置复制错误时，一切正常（见下文）：

INSERT INTO tblComputer (ComputerName, IsServer) VALUES ('PC0001',1)
INSERT INTO tblComputer (ComputerName, IsServer) VALUES ('PC0002',1)
INSERT INTO tblComputer (ComputerName, IsServer) VALUES ('PC0003',1)
INSERT INTO tblComputer (ComputerName, IsServer) VALUES ('PC0004',0)
INSERT INTO tblComputer (ComputerName, IsServer) VALUES ('PC0005',1)
INSERT INTO tblComputer (ComputerName, IsServer) VALUES ('PC0006',0)
INSERT INTO tblComputer (ComputerName, IsServer) VALUES ('PC0007',1)

INSERT INTO tblHDD (ComputerID, TotalSize, FreeSpace) VALUES (1,100,21)
INSERT INTO tblHDD (ComputerID, TotalSize, FreeSpace) VALUES (2,100,10)
INSERT INTO tblHDD (ComputerID, TotalSize, FreeSpace) VALUES (3,100,55)
INSERT INTO tblHDD (ComputerID, TotalSize, FreeSpace) VALUES (4,0,10)
INSERT INTO tblHDD (ComputerID, TotalSize, FreeSpace) VALUES (5,100,23)
INSERT INTO tblHDD (ComputerID, TotalSize, FreeSpace) VALUES (6,100,18)
INSERT INTO tblHDD (ComputerID, TotalSize, FreeSpace) VALUES (7,100,11)

-- This statement does not throw an error as apparently the row for ComputerID 4 
-- is filtered out before computing the (FreeSpace / TotalSize * 100)
SELECT 
TotalSize,
FreeSpace,
(FreeSpace / TotalSize * 100)
FROM 
tblComputer
JOIN
tblHDD ON
tblComputer.ID = tblHDD.ComputerID
WHERE
IsServer = 1

我很困惑，想知道原因。

欢迎任何想法或指导方向，先行致谢。

更新

非常感谢您的输入，但不幸的是，我似乎并没有接近问题的根源。 我设法将语句简化了一点，现在如果删除一个JOIN（我需要它来获取输出中的其他列，在此临时删除），则可以执行该语句而不会出错。

我不明白为什么使用JOIN会导致错误，标准的INNER JOIN是否始终会返回相同数量的行或更少，但从不会返回更多的行？

正常工作的代码:

SELECT 
TotalSize,
FreeSpace
((FreeSpace / TotalSize) * 100)
FROM 
MyTable1
INNER JOIN 
MyTable2 ON
MyTable1.ID = MyTable2.Table1ID
WHERE 
SomeCondition

引起错误的代码

SELECT 
TotalSize,
FreeSpace
((FreeSpace / TotalSize) * 100)
FROM 
MyTable1
INNER JOIN 
MyTable2 ON
MyTable1.ID = MyTable2.Table1ID
-- This JOIN causes "divide by zero encountered" error
INNER JOIN 
MyTable3 ON
MyTable2.ID = MyTable3.Table2ID
WHERE 
SomeCondition

我还尝试使用游标并逐行循环遍历结果，但在这种情况下不会发生任何错误（无论我尝试哪个上述语句）。

对于代码缩进混乱的问题感到抱歉，某些原因导致正确的格式未能应用。

G.

- Gorgsenegger

Answer 1

SQL是一种声明性语言；您编写一个查询来逻辑描述您想要的结果，但优化器负责生成物理计划。这个物理计划可能与查询的书面形式没有太大关系，因为优化器不仅可以重新排序从查询文本形式派生的“步骤”，还可以应用超过300种不同的转换来找到有效的执行策略。

优化器有相当大的自由度来重新排序表达式、连接和其他逻辑查询构造。这意味着您通常不能依赖任何书面查询形式来强制执行一件事情在另一件事情之前。特别是Lieven提供的重写并不能强制WHERE子句谓词在表达式之前被评估。根据成本估算，优化器可能决定在最有效的位置评估表达式。在某些情况下，这甚至可能意味着表达式被评估多次。

原始问题考虑了这种可能性，但将其拒绝为“没有太多意义”。然而，这就是产品的工作方式——如果SQL Server估计联接将减少集合大小以使计算表达式更便宜，则可以自由地这样做。

通常规则是永远不要依赖于特定的评估顺序，以避免诸如溢出或除零错误之类的问题。在这个例子中，可以使用CASE语句来检查是否存在零除数 - 这是一种防御性编程的例子。

优化器对重新排序的自由是其设计的基本原则。您可以找到导致反直觉行为的情况，但总体而言，好处远远大于缺点。

保罗

- user440595

---

感谢您的解释，它证实了我在过去几天中开始假设的内容，并且也得到了Lieven在上面评论中的建议。由于我只能选择一个答案，而他更多地参与了这个案例，所以我选择了他的答案，但再次感谢您的帮助。 - Gorgsenegger

+1. @Gorgsenegger的答案非常准确，也回答了我自己试图强制优化器首先评估where子句的错误假设。感谢您的支持，但您真的应该将此答案标记为已接受的答案。 - Lieven Keersmaekers

@Lieven，如果这对你没有问题的话，无论如何还是谢谢你;-) - Gorgsenegger

有没有 SQL 规范中指出这一点的简单指向位置？实际上我刚刚问了一个类似的问题，那里的一些人似乎认为标准规定必须先评估 where 子句。（粗略转述）https://dev59.com/h1XTa4cB1Zd3GeqPyA47 - Peter Recore

Answer 2

SQL Server使用的基本步骤处理单个SELECT语句包括以下内容：

1. 解析器扫描SELECT语句并将其分解为逻辑单元，例如关键字、表达式、运算符和标识符。
2. 构建查询树（有时称为序列树），描述将源数据转换为结果集所需的逻辑步骤。
3. 查询优化器分析可以访问源表的不同方式。然后选择返回结果最快且使用更少资源的一系列步骤。查询树被更新以记录这个确切的步骤序列。查询树的最终优化版本称为执行计划。
4. 关系引擎开始执行执行计划。随着处理需要从基表获取数据的步骤，关系引擎请求存储引擎从关系引擎请求的行集中传递数据。
5. 关系引擎将从存储引擎返回的数据处理成为定义为结果集的格式，并将结果集返回给客户端。

我的理解是，不能保证在计算所有行的计算列之前评估 where 子句。你可以通过像下面这样更改查询并强制 where 子句在计算之前进行评估来验证该假设。

SELECT
    TotalSize,
    FreeSpace,
    (FreeSpace / TotalSize * 100)
FROM (
  SELECT
      TotalSize,
      FreeSpace,
  FROM
      tblComputer
  ...[ couple of joins ]...
  WHERE
      SomeCondition = SomeValue
  ) t

- Lieven Keersmaekers

---

没错。在计算标量放置在筛选器前或后都是免费的。我记得 Remus Rusanu 给出了一个很好的答案，但现在找不到了。 - Martin Smith

谢谢您的回复，我已经更新了原帖以反映我的最新发现。不幸的是，我仍然没有找到问题的答案/原因... - Gorgsenegger

@Lieven，是的，我尝试将带有条件的部分作为子查询放入from中，但仍然遇到了错误。 - Gorgsenegger

现在这是我没想到的...你能发布计划吗？ - Lieven Keersmaekers

总结一下这个帖子的所有内容，并比较执行计划，对我来说似乎就像你在上面的第一条评论中已经提到的那样（SQLkiwi也提到了），执行顺序和过滤是任意的，它可能会根据优化器考虑的事情而改变，这些事情“我们”（人类）可能需要进一步调查才能理解。我会选择您的答案，因为我不能选择两个，而且您在这个帖子中付出了更多的努力。感谢您的帮助，我肯定学到了关于SQL的一些东西;-) - Gorgsenegger

Answer 3

当您运行以下代码时，会返回哪些行：

SELECT
   TotalSize
FROM
   tblComputer
   ...[ couple of joins ]...
WHERE
   SomeCondition = SomeValue
   and ((TotalSize * 100) = 0)

这可能会让你明白SQL Server是如何将(TotalSize * 100)计算为零的。

另一个想法是，在你的where语句中是否有任何可能也是问题的东西？
你假设它是TotalSize，但它可能在其他地方。

- Bravax

---

谢谢您的建议。这次对我的情况没有帮助，但还是值得记住，因为它可能在以后有用 :-) - Gorgsenegger

Answer 4

我遇到了同样的问题。在我的情况下，NULL是可以接受的，所以我能够通过以下方式解决它：

Select Expression1 / Expression2 -- Caused Division By 0
Select Expression1 / NULLIF(Expression2,0) -- Causes result to be NULL

如果您需要其他处理，可以像这样将整个表达式包装在ISNULL函数中：

Select ISNULL(Expression1 / NULLIF(Expression2,0)-5) -- Returns -5 instead of null or divide by 0

- Bytemaster

---

谢谢你，但正如我在问题中所说的那样，我正在寻找“为什么”，而不是使用你的建议绕过它的方法。 - Gorgsenegger

第一次阅读时不知怎么就错过了那个。另一个答案已经基本涵盖了 - 这取决于运行时生成的执行计划。我刚刚遇到类似的情况，其中一个视图中我漏掉了这个操作 - 花费了很长时间，但终于有人提出了一个针对它的查询，改变了计划，从而导致错误。 - Bytemaster