Bizarre Divide by Zero behaviour

Question

Bizarre Divide by Zero behaviour

theboyholty

Ten Centuries

Points: 1050
More actions
August 17, 2015 at 7:47 am

#303486

I have a query that, in this example, pulls a single row result.
I'm dividing 140 by 28 and you'd think the result would be 5, but I'm getting this divide by zero error, and it gets weirder still...
Here's the initial query for starters:
SELECT
i.CPSP35
,i.VINT35
,(i.CPSP35 / i.VINT35) as High
FROM dbo.SSCCWorderExtract e
INNER JOIN DEV.dbo.INP35 i ON e.ITEM40 = i.PNUM35
WHERE i.CONO35 = 'K1'
The CPSP35 field contains 140 and VINT35 is 28. Both are defined as decimal (28,0) data types. As mentioned the above query returns the divide by zero error.
However if I add the following case statement...
SELECT
i.CPSP35
,i.VINT35
,(i.CPSP35 / i.VINT35) as High
,case when i.VINT35 = 0 then 1 else i.VINT35 end as irrelevant_field
FROM dbo.SSCCWorderExtract e
INNER JOIN DEV.dbo.INP35 i ON e.ITEM40 = i.PNUM35
WHERE i.CONO35 = 'K1'
..then it works perfectly!
The only difference between the two queries above is that case statement.
If I was looking at this as I hope you will be I'd ask about that 'dbo.SSCCWorderExtract' object which is a view, and I've looked deeply into that. It doesn't contain any fields called CPSP35, VINT35 or PNUM35, but looks like it is the join which is causing the trouble because I can run the divide on the INP35 table itself and it works ok. In fact it only pulls one record aswell.
I don't understand how I can break the query by adding this CASE statement that isn't even being used.
I know that I can fix this by putting the very same case statement in the divide calculation, and I have done that but I would love to be able to understand why this is happening.
Thanks.

Viewing 9 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

sgmunson SSC Guru Points: 110651 More actions · Answer 1

Have you verified that there are no other records that would otherwise qualify to pass through this query, where the divisor ends up being 0 ? Also, check the setting for ARITH_ABORT to be sure it didn't get turned off for the other run. I've had a number of situations where I failed to realize that there was other data that would make it through the query than what I was anticipating, and once I coded the CASE statement into the division, the other data revealed itself. All the rest of us can do, however, is guess, as we have no sample data to work with.

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 2

What is the code of the referenced objects too? They could be got-knows-what-containing views/mTVFs, etc.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 3

What does this return?

SELECT

i.CPSP35

,i.VINT35

FROM DEV.dbo.INP35

WHERE VINT35 = 0

SQL may run the division before it does the filter and join, so if there are rows with VINT35 = 0 that are later filtered out, they'd still cause the divide by zero error.

Edit: Fixed query.

Edit 2: And why the case statement fixed it. Probably changed the plan so that the division is done later in the query. Safer to use a CASE to make sure that the denominator can't be 0, that way the query's safe from plan changes in the future.

SELECT

i.CPSP35

,i.VINT35

,CASE i.VINT35 WHEN 0 THEN 0 ELSE (i.CPSP35 / i.VINT35) END as High

FROM dbo.SSCCWorderExtract e

INNER JOIN DEV.dbo.INP35 i ON e.ITEM40 = i.PNUM35

WHERE i.CONO35 = 'K1'

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

theboyholty Ten Centuries Points: 1050 More actions · Answer 4

GilaMonster (8/17/2015)
What does this return?
SELECT
i.CPSP35
,i.VINT35
FROM dbo.SSCCWorderExtract e
WHERE VINT35 = 0
SQL may run the division before it does the filter and join, so if there are rows with VINT35 = 0 that are later filtered out, they'd still cause the divide by zero error.

Hi Gail and thanks,

It was the other table, the DEV.dbo.INP35 that those fields come from but I see what you're getting at.

There are zeroes in that field so maybe that's it.

theboyholty Ten Centuries Points: 1050 More actions · Answer 5

Update:

I just amended the code to add a line to the join clause only letting through those records with a VINT > 0:

SELECT

i.CPSP35

,i.VINT35

,(i.CPSP35 / i.VINT35) as High

FROM dbo.SSCCWorderExtract e

INNER JOIN DEV.dbo.INP35 i ON e.ITEM40 = i.PNUM35 and i.VINT35 > 0

WHERE i.CONO35 = 'K1'

..and it worked.

Thanks everyone - we live and learn don't we?

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 6

I'd still recommend you put a CASE around the division (see my edits in earlier post), as a plan change could still result in the division happening before the filter.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 7

GilaMonster (8/17/2015)
I'd still recommend you put a CASE around the division (see my edits in earlier post), as a plan change could still result in the division happening before the filter.

ABSOLUTELY agree! The optimizer can do almost anything it wants with your query as long as Boolean and algebraically it provides the same output or effect (even if that output/effect isn't what you intended!!). This includes pushing predicates around the plan and with AND-type Booleans being commutative that is a common one that gets people.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 8

-- This has become more or less the gold standard method

-- for avoiding the division by zero error:

SELECT

i.CPSP35,

i.VINT35,

ISNULL(i.CPSP35 / NULLIF(i.VINT35,0),0) AS High

FROM dbo.SSCCWorderExtract e

INNER JOIN DEV.dbo.INP35 i

ON e.ITEM40 = i.PNUM35

WHERE i.CONO35 = 'K1'

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden