SQL Server - strange division by zero
I am making the following request:
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 the fn_Yval function (all parameters are float):
set @m = (@p2Y - @p1Y)/(@p2X - @p1X)
set @b = @p1Y - (@m*@p1X)
set @result = (@m*@xval+@b)
There are no entries in the vPolygonSegments view where p1X = p2X (these entries are excluded). However, when I execute my query, SQL Server returns an error: "Division by zero error". Curiously, if I only execute the first two lines (no where clause), the query returns the results just fine.
How do I fix this and / or what is causing this behavior?
Edit: Here's my opinion:
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
source to share
The problem is that the function in the select reason is evaluated before the function in the where clause is evaluated. This condition is very rare, but it is possible, which is why you need to consult it. I would suggest that you modify the function so that it can safely handle division by zero circumstances. Change this line:
set @m = (@p2Y - @p1Y)/(@p2X - @p1X)
For this:
set @m = (@p2Y - @p1Y)/NullIf((@p2X - @p1X), 0)
When @ p2x - @ p1x = 0, the NULLIF function will return NULL. Subsequently @m will be null, like all other values. Most likely, the function will return NULL.
In your where clause you have ...
Where dbo.fn_Yval(p1X, p1Y, p2X, p2Y, 4.003318) > 0
When the function returns NULL, it will not compare against 0 and will eventually be filtered out.
source to share
As Joel Coehorn pointed out, this is a likely problem. The following query should avoid the problem, but the problem is best fixed inside your function itself:
Select guiPolygonID, dbo.fn_Yval(p1X, p1Y, p2X, p2Y, 4.003318)
From [vPolygonSegments]
Where p2X <> p1X and dbo.fn_Yval(p1X, p1Y, p2X, p2Y, 4.003318) > 0
source to share
I suspect it has something to do with how the view is materialized for the request. Adding this functionality to the where clause essentially adds it to the original query for the view, so the optimizer can choose to apply this filter to the original data before P1.decX <> P2.decX
.
In any case, you don't know when you might want to reuse this function somewhere else that doesn't have the same limitation. It is best to have a bad data management plan. In this case, the suggestions NullIf
seem to be good.
source to share
I don't know if this will somehow answer the question, but you are evaluating fn_Yval twice for each entry. Why not make the result of the function a column in the view? Then your where clause might be something like "where Yval> 0".
Edit: Out of curiosity, fn_Yval doesn't have any side effects, does it?
source to share