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

问题描述

我一直试图理解为什么我的 SQL 查询会出现遇到被零除"(消息 8134)，但我一定遗漏了一些东西.我想知道下面具体案例的为什么，我不寻找NULLIF，CASE WHEN... 或类似的，因为我已经知道它们(当然可以在下面的情况下使用它们).

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).

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

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

使用上述错误消息运行此语句会出错，这本身不是问题 - 显然 TotalSize 很可能为 0，因此会导致错误.

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.

现在我不明白的是，当我将计算列注释掉时，我没有任何 TotalSize 列为 0 的行，我仔细检查了情况并非如此.

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.

然后我认为由于某种原因，列计算将在整个结果集上执行之前实际使用 where 子句的条件进行过滤，但是这 a) 恕我直言和b) 当尝试通过测试设置重现错误时，一切正常(见下文):

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

更新

到目前为止，感谢您的投入，但不幸的是，我似乎并没有接近问题的根源.我设法将语句剥离了一点，现在有这样的情况，如果删除一个 JOIN，我可以毫无错误地执行它(我需要它用于临时删除的输出中的其他列).

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).

我不明白，为什么使用 JOIN 会导致错误，标准的 INNER JOIN 不应该总是返回相同的行数或更少，但永远不要更多强>?

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?

工作代码

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

我也尝试过使用游标并逐行循环结果的运气，但在那种情况下没有发生错误(无论我尝试了上面两个语句中的哪一个).

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

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.

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

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.

最初的问题考虑了这种可能性，但以没有多大意义"为由拒绝了它.尽管如此，这就是产品的工作方式 - 如果 SQL Server 估计连接将减少集合大小足以降低计算连接结果的表达式的成本，则可以随意这样做.

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.

一般规则是永远不要依赖特定的评估顺序，以避免出现溢出或除以零错误之类的情况.在这个例子中，我们将使用 CASE 语句来检查零除数 - 一个防御性编程的例子.

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.

保罗

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