尽管没有包含 0 的列，但遇到 TSQL 除以零的情况

Question

我一直试图理解为什么我的 SQL 查询遇到“被零除”（消息 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。

Answer 1

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

优化器有相当大的自由度来重新排序表达式、连接和其他逻辑查询结构。这意味着一般情况下，您不能依赖任何书面查询形式来强制先评估一件事，然后再评估另一件事。特别是，Lieven 给出的重写不会强制在表达式之前评估 WHERE 子句谓词。优化器可以根据成本估计决定在看起来最有效的地方评估表达式。在某些情况下，这甚至可能意味着表达式会被计算多次。

最初的问题考虑了这种可能性，但拒绝了它，因为“没有多大意义”。然而，这就是产品的工作方式 - 如果 SQL Server 估计连接将减少集合大小，足以使计算连接结果的表达式变得更便宜，那么可以自由地这样做。

一般规则是永远不要依赖特定的求值顺序来避免溢出或被零除错误等问题。在此示例中，我们将使用 CASE 语句来检查除数是否为零 - 这是防御性编程的一个示例。

优化器自由地重新排序是其设计的基本原则。您可能会发现它会导致反直觉行为的情况，但总的来说，好处远远大于坏处。

保罗

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

Answer 3

运行时返回哪些行：

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

这可能会为您提供有关 SQL Serve 如何将 (TotalSize * 100) 计算为零的线索。

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

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