SQL Server - 奇怪的除以零
我正在执行以下查询：
Select guiPolygonID, dbo.fn_Yval(p1X, p1Y, p2X, p2Y, 4.003318)
From [vPolygonSegments]
Where dbo.fn_Yval(p1X, p1Y, p2X, p2Y, 4.003318) > 0
函数 fn_Yval 的重要部分（所有参数均为 float 类型）：
set @m = (@p2Y - @p1Y)/(@p2X - @p1X)
set @b = @p1Y - (@m*@p1X)
set @result = (@m*@xval+@b)
视图 vPolygonSegments 不包含 p1X = p2X 的记录（排除这些记录）。 然而，当我执行查询时，SQL Server 返回一个错误：“遇到除以零错误。” 奇怪的是，如果我只执行前两行（没有 where 子句），查询会很好地返回结果。
我该如何解决这个问题，和/或是什么导致了这种行为？
编辑： 这是我的观点：
Select P1.guiPolygonID,
P1.decX as p1X, P1.decY as p1Y,
P2.decX as p2X, P2.decY as p2Y
From PolygonPoints P1, PolygonPoints P2
Where P1.guiPolygonID = P2.guiPolygonID
and (
P1.lPointNumber - P2.lPointNumber = 1
or (
-- Some other unimportant code
)
)
and P1.decX <> P2.decX
I'm executing the following query:
Select guiPolygonID, dbo.fn_Yval(p1X, p1Y, p2X, p2Y, 4.003318)
From [vPolygonSegments]
Where dbo.fn_Yval(p1X, p1Y, p2X, p2Y, 4.003318) > 0
The important parts of function fn_Yval (all params are of type float):
set @m = (@p2Y - @p1Y)/(@p2X - @p1X)
set @b = @p1Y - (@m*@p1X)
set @result = (@m*@xval+@b)
The view vPolygonSegments contains no records where p1X = p2X (those records are excluded). Yet, when I execute my query, SQL Server returns an error: "Divide by zero error encountered." Curiously, if I execute only the first two lines (without the where clause), the query returns results just fine.
How do I fix this, and/or what is causing this behavior?
Edit:
Here is my view:
Select P1.guiPolygonID,
P1.decX as p1X, P1.decY as p1Y,
P2.decX as p2X, P2.decY as p2Y
From PolygonPoints P1, PolygonPoints P2
Where P1.guiPolygonID = P2.guiPolygonID
and (
P1.lPointNumber - P2.lPointNumber = 1
or (
-- Some other unimportant code
)
)
and P1.decX <> P2.decX
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（6）
这里的问题是，在计算 where 子句中的函数之前，先计算 select Cause 中的函数。 这种情况非常罕见，但有可能发生，因此您需要针对它进行编码。 我建议您修改该函数，以便它可以安全地处理被零除的情况。 将此行更改
为
：当 @p2x - @p1x = 0 时，NULLIF 函数将返回 NULL。 随后，@m 将为 null，所有其他值也将为 null。 该函数很可能会返回 NULL。
在你的 where 子句中，你...
当函数返回 NULL 时，它不会与 0 进行比较，并且最终会被过滤掉。
The problem here is that the function in the select cause is evaluated before the function in the where clause is evaluated. This condition is very rare, but it is possible, so you need to code against it. I would suggest that you modify the function so that it can safely handle divide by zero circumstances. Change this line:
To this:
When @p2x - @p1x = 0, the NULLIF function will return NULL. Subsequently, @m will be null, as will all the other values. Most likely, the function will return NULL.
In your where clause, you have...
When the function returns NULL, it won't compare to 0 and will end up getting filtered out anyway.
正如乔尔·科霍恩（Joel Coehoorn）指出的那样，这就是可能的问题。 下面的查询应该可以避免该问题，但最好在函数本身内部解决该问题：
As Joel Coehoorn pointed out, that is the likely issue. The query below should avoid the problem, but the problem is best fixed inside your function itself:
我怀疑这与查询的视图的具体化方式有关。 将该函数添加到 where 子句实质上会将其添加到视图的源查询中，这样优化器可能会选择在
P1.decX <> 之前将此过滤器应用于源数据。 P2.decX。无论如何，您不知道何时需要在没有相同约束的其他地方重用该函数。 最好预先制定一个如何处理不良数据的计划。 在这种情况下，
NullIf建议似乎是一个不错的建议。I suspect it has to do with how the view is materialized for the query. Adding that function to the where clause essentially adds it to the source query for the view, such that the optimizer might choose to apply this filter to the source data before
P1.decX <> P2.decX.Anyway, you don't know when you'll want to re-use that function somewhere else that doesn't have the same constraint. Best to have a plan for how to handle bad data up front. The
NullIfsuggestions seems like a good one in this case.当@p2X = @p1X 时会出现问题。 在这种情况下您预计会发生什么？
The problem occurs when @p2X = @p1X. What do you expect to happen in that case?
我不知道这会以任何方式回答这个问题，但您正在为每条记录评估 fn_Yval 两次。 为什么不将函数的结果作为视图中的一列？ 然后，您的 where 子句可能类似于“where Yval > 0”。
编辑：出于好奇， fn_Yval 没有任何副作用，不是吗？
I don't know that this will answer the question in any way, but you're evaluating fn_Yval twice for each record. Why not make the result of the function a column in the view? Then, your where clause could be something like "where Yval > 0".
Edit: Out of curiosity, fn_Yval doesn't have any side effects, does it?
我发现在涉及联接或视图时尝试使用 where 子句删除可能导致被零除错误的数据是有问题的。 要么在 join 子句中过滤数据，要么在函数中过滤掉数据。
更新：出于“G Mastros”所写的原因。
I found it to problematic to try to remove data that can cause divide by zero errors using a where clause when a join or view is involved. Either filter the data in the join clause or filter it out in the function.
update: for just the kind of reasons "G Mastros" wrote.