尽管没有包含0的列，但遇到的TSQL除以零 [英] TSQL divide by zero encountered despite no columns containing 0

问题描述

我一直试图理解为什么我的SQL查询会遇到被零除（消息8134），但是我肯定缺少一些东西。我想知道以下情况下的为什么，我不正在寻找 NULLIF ，在...的情况下或类似我所知道的（当然可以在以下情况下使用它们）。

我有一条SQL语句，其计算列类似于

  SELECT 
 TotalSize，
 FreeSpace，
（FreeSpace / TotalSize * 100）
从
 tblComputer 
 ... [几个联接] ... 
 WHERE 
 SomeCondition = SomeValue 
  

使用上面提到的错误消息运行此语句错误，这本身并不是问题-很明显 TotalSize 可能为0，因此会导致错误。

现在我不明白的是，当 TotalSize 列为0时，我没有任何行我将计算出的列注释掉，我再次检查了是否是这种情况。

然后我认为出于某种原因，列计算将对整个结果执行将 before 设置为实际使用where子句的条件进行过滤，但这a）恕我直言，b）尝试通过测试设置重现错误时一切正常（请参见下文） ）：

 插入到tblComputer（ComputerName，IsServer）值（'PC0001'，1）
插入到tblComputer（ ComputerName，IsServer）值（'PC0002'，1）
插入tblComputer（ComputerName，IsServer）值（'PC0003'，1）
插入tblComputer（ComputerName，IsServer）值（'PC0004'， 0）
插入tblComputer（计算机名，IsServer）值（'PC0005'，1）
插入tblComputer（计算机名，IsServer）值（'PC 0006'，0）
插入tblComputer（ComputerName，IsServer）值（'PC0007'，1）
 
插入tblHDD（ComputerID，TotalSize，FreeSpace）值（1,100,21）
插入tblHDD（ComputerID，TotalSize，FreeSpace）值（2,100,10）
插入tblHDD（计算机ID，TotalSize，FreeSpace）值（3,100,55）
插入tblHDD（计算机ID，TotalSize）的值，FreeSpace）值（4,0,10）
插入tblHDD（ComputerID，TotalSize，FreeSpace）值（5,100,23）
插入tblHDD（ComputerID，TotalSize，FreeSpace）值（6,100,18 ）
插入tblHDD（ComputerID，TotalSize，FreeSpace）值（7,100,11）
 
-此语句不会引发错误，因为显然是ComputerID 4 
的行- -在计算（FreeSpace / TotalSize * 100）
 SELECT 
 TotalSize，
 FreeSpace，
（FreeSpace / TotalSize * 100）
 FROM $ b $之前被过滤掉b tblComputer 
在
上加入
 tblHDD tblComputer.ID = tblHDD.ComputerID 
 WHERE 
 IsServer = 1 
  

我很沮丧，想知道原因是什么。

非常欢迎任何提出正确方向的想法或指点，

更新

到目前为止，非常感谢您的投入，但不幸的是，我似乎离问题的根源还很近。我设法将语句精简了一下，现在有了一种情况，如果删除了一个JOIN，我可以在没有错误的情况下执行它（我需要它来临时删除输出中的其他列）。

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

工作代码

 选择
 TotalSize，
 FreeSpace 
（（FreeSpace / TotalSize）* 100）
 FROM 
 MyTable1 
 INNER JOIN 
 MyTable2 ON 
 MyTable1.ID = MyTable2.Table1ID 
 WHERE 
 SomeCondition 
  

导致代码出错

  SELECT 
 TotalSize，
 FreeSpace 
（（FreeSpace / TotalSize）* 100）
从
 MyTable1 
内连接
 MyTable2在
 MyTable1.ID = MyTable2.Table1ID 
 -此加入原因es被零除错误
内部联接
 MyTable3 ON 
 MyTable2.ID = MyTable3.Table2ID 
 WHERE 
 SomeCondition 
  

我也使用游标尝试了运气，并逐行循环显示结果，但是在这种情况下，没有发生错误（无论哪个

对不起，代码缩进很抱歉，似乎无法应用正确的格式。

G。

解决方案

SQL是一种声明性语言。您编写了一个查询，该查询从逻辑上描述了您想要的结果，但这取决于优化程序来生成物理计划。该物理计划可能与查询的书面形式关系不大，因为优化器不会简单地对查询文本形式派生的步骤进行重新排序，而是可以应用300多种不同的转换来找到有效的执行策略。 / p>

优化器具有很大的自由度，可以对表达式，联接和其他逻辑查询结构进行重新排序。这意味着，通常，您不能依靠任何书面查询形式来强制对某事进行评估。特别是，Lieven给出的重写不会 not 强制WHERE子句谓词在表达式之前进行求值。根据成本估算，优化器可以决定在似乎最有效的位置进行评估。在某些情况下，这甚至可能意味着对该表达式进行了多次评估。

最初的问题考虑了这种可能性，但将其拒绝为没有太大意义 。不过，这就是产品的工作方式-如果SQL Server估计联接将减小集合大小，以使其在联接结果上计算表达式的成本降低，则可以免费这样做。

一般规则是永远不要依赖特定的评估顺序来避免发生溢出或被零除错误之类的事情。在此示例中，将使用CASE语句检查零除数-防御性编程的示例。

优化器对事物进行重新排序的自由是其基本宗旨。设计。您会发现一些情况会导致违反直觉的行为，但总的好处远大于缺点。

Paul

I've been trying to understand why I get a "divide by zero encountered" (Msg 8134) with my SQL query, but I must be missing something. I would like like to know the why for the specific case below, I am not looking for NULLIF, CASE WHEN... or similar as I already know about them (and can of course use them in a situation as the one below).

I have an SQL statement with a computed column similar to

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

Running this statement errors with the above mentioned error messages, which, in itself, is not the problem - obviously TotalSize might well be 0 and therefore cause the error.

Now what I don't understand is that I do not have any rows where the TotalSize column is 0 when I comment the computed column out, I double checked that this isn't the case.

Then I thought that for some reason the column computation would be performed on the whole result set before actually filtering with the conditions of the where clause, but this a) wouldn't make sense imho and b) when trying to reproduce the error with a test set-up everything works fine (see below):

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

I am quite stumped and would like to know what the reason is.

Any ideas or pointers into the right direction are very welcome, thanks in advance

Update

Thank you so far for your input, but unfortunately I seem not to be getting closer to the root of the problem. I managed to strip the statement down a little bit and now have the case that I can execute it without errors if one JOIN is removed (I would need it for additional columns in the output which I temporarily removed).

I do not understand, why using the JOIN leads to the error, shouldn't a standard INNER JOIN always either return the same number of rows or less, but never more?

Working code

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

Error causing code

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

I also tried my luck using a cursor and looping over the result row by row, but in that case no error occurred (no matter, which of the two statements above I tried).

Sorry for the messy code indentation, somehow the correct formatting doesn't seem to be applied.

G.

解决方案

SQL is a declarative language; you write a query that logically describes the result you want, but it is up to the optimizer to produce a physical plan. This physical plan may not bear much relation to the written form of the query, because the optimizer does not simply reorder 'steps' derived from the textual form of the query, it can apply over 300 different transformations to find an efficient execution strategy.

The optimizer has considerable freedom to reorder expressions, joins, and other logical query constructions. This means that you cannot, in general, rely on any written query form to force one thing to be evaluated before another. In particular, the rewrite given by Lieven does not force the WHERE clause predicate to be evaluated before the expression. The optimizer may, depending on cost estimations, decide to evaluate the expression wherever it seems most efficient to do so. This may even mean, in some cases, that the expression is evaluated more than once.

The original question considered this possibility, but rejected it as 'not making much sense'. Nevertheless, this is the way the product works - if SQL Server estimates that a join will reduce the set size enough to make it cheaper to compute the expression on the result of the join, it is free to do so.

The general rule is to never depend on a particular evaluation order to avoid things like overflow or divide-by-zero errors. In this example, one would employ a CASE statement to check for a zero divisor - an example of defensive programming.

The optimizer's freedom to reorder things is a fundamental tenet of its design. You can find cases where it leads to counter-intuitive behaviours, but overall the benefits far outweigh the disadvantages.

Paul

这篇关于尽管没有包含0的列，但遇到的TSQL除以零的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！