divide by zero, but no fields are zero

Question

Post reply

divide by zero, but no fields are zero

Steven vermeulen

SSC Veteran

Points: 207
More actions
March 21, 2007 at 10:37 am

#369918

Hi,
I am trying to fix a problem in a T-sql procedure
I'm getting a 'divide by zero error' in an update statement.
I took the FROM-piece from the statement, and did a select * on it
Select *
from table t
where t.value_a/t.value_b > 10
AND t.value_c < 100
=> same error.
Off course the first piece of the where-clause causes the divide by zero error
if I do the following select :
select *
from table t
where t.value_b = 0
I get a record count of 0, meaning no records where value_b is 0
How is it possible to get a divide by zero error if the divisor is always different from zero ? It does not matter if the values are floats, numerics or integers.

Viewing 11 posts - 1 through 10 (of 10 total)

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Try this way indstead. Aside from the divide by zero, it's better for index selection (if you have appropriate indexes)

Select *

from table t

where t.value_a > 10*t.value_b

AND t.value_c < 100

With float I could see impresision causing a problem. Dunno about int or numeric though.

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

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 2

What are the data types of value_a and value_b?

What values of value_a and value_b cause the problem?

mrpolecat SSCertifiable Points: 7014 More actions · Answer 3

mrpolecat

SSCertifiable

Points: 7014

March 21, 2007 at 1:28 pm

#696240

how about nulls instead of 0 for value_b?

Sergiy SSC Guru Points: 110208 More actions · Answer 4

Yes, it's always good to have

t.value_a/NULLIF(t.value_b, 0) > 10

but i would agree it's rather converting problem.

I guess value_a is integer, and value_b is float or decimal.

Division converts t.value_b to int and values > -0.5 and < 0.5 become 0.

_____________
Code for TallyGenerator

Steven vermeulen SSC Veteran Points: 207 More actions · Answer 5

The original datatypes were numeric (20,3) but are cast in the statement as float, so I tried to update them to numeric (20,10) first, and then also tried to update them to integer values (off course by losing some accuracy)

removing the cast, and leave the original values only is the fourth option I tried

All 4 cases causes the same error. and none of the values become zero in the three cases.

As value_b is a non-nullable field, there is no need (i.m.o) to check on nullability. Null value does not cause any divide by zero either as far as I know.

value_b is rather large, there is no value smaller than 1 (checked on that too) and it is never zero either (we check on that before) the lowest value of value_b is 90, and the largest was 6458200.

What I tried yesterday, and which does the trick also is the following :

where 10 <= CASE WHEN value_b = 0 then 10 else value_a/value_b end

but I found that rather tricky, as it just goes around the problem and it could cause some strange effects.

moving value_b to the other side of the comparison (as mentioned above) also does the trick, and is more suitable as a solution.

But that still does not explain why the error happened in the first place. There is no reason for the process to throw a 'divide by zero error' as there are no zero's in the whole table. (not even close either)

Is there anybody who can explain "why" I get this error?

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 6

You still didn't say which values of value_a and value_b cause the problem.

mrpolecat SSCertifiable Points: 7014 More actions · Answer 7

if you just select t.value_a/t.value_b from the table do you still get the error?

rlively-343684 SSC Veteran Points: 298 More actions · Answer 8

It could be an expression precedence problem.

If

Select *

from table t

where t.value_a/t.value_b > 10

AND t.value_c 10 AND t.value_c < 100)

instead of this:

(t.value_a / t.value_b) AND t.value_c < 100

And if value_b is = 100 then it evaluates as 0, which gives you a 0 divisor.

http://msdn2.microsoft.com/en-us/library/ms190276.aspx

Operator Precedence (Transact-SQL)

When a complex expression has multiple operators, operator precedence determines the sequence in which the operations are performed. The order of execution can significantly affect the resulting value.

Operators have the precedence levels shown in the following table. An operator on higher levels is evaluated before an operator on a lower level.

Level Operators

1~ (Bitwise NOT)

2* (Multiply), / (Division), % (Modulo)

3+ (Positive), - (Negative), + (Add), (+ Concatenate), - (Subtract), & (Bitwise AND)

4=, >, =, <=, , !=, !>, ! 10)

AND t.value_c < 100

Steven vermeulen SSC Veteran Points: 207 More actions · Answer 9

after doing some more checks, the following came out :

as actaully the from clause contained joins, the zero values were filtered out.

This means that the where clause is executed before the joinstatement.

The SQL query optimizer apparently thinks this is more performant.

If I force my queryplan, I also don't get the error anymore.

I will update the solution mentioned above to include a case statement

The above solution was not waterproof if both values are zero ... (but that is something you guys couldn't have known)

so issue solved. Thx for the input.

Sergiy SSC Guru Points: 110208 More actions · Answer 10

NULLIF works faster than CASE and makes your code more clean.

_____________
Code for TallyGenerator