Solved

Divide by Zero problem with large dataset

Posted on 2008-10-06
19
831 Views
Last Modified: 2008-11-16
I'm trying to pinpoint a problem with a Divide by zero problem I'm having. My query looks like:

select key_id, a/b x
from table
where *conditions*

About 500000 rows should be returned. Here are three anomalies that have me stumped:

Anomaly #1:
Query #1:
select key_id, a/b x
from table
where b <> 0 and b is not null

Query #2:
select key_id, a/b x
from table
where key_id in (select key_id from table where b <> 0 and b is not null)

Query 1 runs fine, but Query 2 produces Divide by zero !?!? Is there a bug with large subqueries?

Anomaly #2:
Query #3:
select key_id, a/b x
from table
where key_id in (select key_id from table where b <> 0 and b is not null)

Query #4:
select key_id, a, a/b x
from table
where key_id in (select key_id from table where b <> 0 and b is not null)

Query 3 runs fine, but Query 4 produces Divide by zero !?!? Why does adding an extra column to the select statement solve things?

Anomaly #3:
Query #5:
select key_id, a/b x
from table
where key_id in (select key_id from table where b <> 0 and b is not null and key_id < 632303)

Query #6:
select key_id, a, a/b x
from table
where key_id in (select key_id from table where b <> 0 and b is not null and key_id < 632304)

Query 5 runs fine, but Query 6 produces Divide by zero. There is nothing interesting about the row with key_id = 632303. This must have something to do with the size of the dataset, no?

Any help appreciated.
0
Comment
Question by:marcgale
  • 7
  • 5
  • 2
  • +2
19 Comments
 
LVL 60

Accepted Solution

by:
chapmandew earned 168 total points
ID: 22652990
do something like this:

select key_id, a, case when b = 0 then 0 else a/b end  x
0
 
LVL 1

Author Comment

by:marcgale
ID: 22653063
a/0 is not 0, It is infinite. To say a/0 = 0 is not valid.

I can produce the correct result (see Query 2 or even query 4). I'm more interested in why the problem occurs in the first place so I can avoid it in the future.
0
 
LVL 4

Expert Comment

by:rehand
ID: 22653085
Why don't you just handle the exceptions?

SELECT
    Key_ID,
    CASE
        WHEN b = 0 THEN NULL
        WHEN b IS NULL THEN NULL
        WHEN a IS NULL THEN NULL
        ELSE a/b
    END AS X
FROM
   Table
0
 
LVL 1

Author Comment

by:marcgale
ID: 22653184
rehand: as I said, I have a solution (query 2), but I need to understand why there was an error in the first place. Query 1 shouldn't have any exceptions to handle.
0
 
LVL 60

Expert Comment

by:chapmandew
ID: 22653195
You've already been told why the error occurred.
0
 
LVL 1

Author Comment

by:marcgale
ID: 22653218
I mistyped: query 1 and query 4 produce correct results. Queries 2 and 3 produce div by zero.
0
 
LVL 60

Expert Comment

by:chapmandew
ID: 22653261
and it is because the field b has the value 0 in there.....
0
 
LVL 1

Author Comment

by:marcgale
ID: 22653264
chapmandew:"You've already been told why the error occurred."

Have I? In all of my queries, the WHERE clause excludes (or should exclude) cases where b=0 or b is null. There should not be a divide by zero.

Why does using a subquery (query 2) fail when using the same conditions in the where clause succeed. Why does the inclusion of an extra term in the SELECT clause (query 4) mysteriously fix the divide by zero problem?
0
Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

 
LVL 39

Assisted Solution

by:BrandonGalderisi
BrandonGalderisi earned 166 total points
ID: 22653311
Strange... I'm not having the same symptom:

vw_nums is just a view with numbers in it.  It returns 1-4billion+.  I'm getting 500k records.  So for every 300+2 (302, 602, 902, etc) it insert n/2 into b.  And for every 10K (10000,20000,30000, etc.) it inserts 0.  It property excludes all 10000 records with no error for me.
create table #a ( ident int identity, a int,b int)

insert into #a (a,b)

select n, case when n%10000=0 then 0 else case when n%300=0 then 2 else n/2 end end

from vw_nums

where n < 500000
 

select ident,a,b,a/b from #a

where b<>0 and b is not null
 

select ident,a,b,a/b from #a

where ident in (select ident from #a where b <> 0 and b is not null)

go

drop table #a

Open in new window

0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 22653333
This also has no errors and produces these results:

(499949 row(s) affected)

(499949 row(s) affected)

(499949 row(s) affected)

(499949 row(s) affected)


create table #a ( ident int identity, a int,b int)

insert into #a (a,b)

select n, case when n%10000=0 then 0 else case when n%300=0 then 2 else n/2 end end

from vw_nums

where n < 500000
 

select ident,a,b,a/b from #a

where b<>0 and b is not null
 

select ident,a,b,a/b from #a

where ident in (select ident from #a where b <> 0 and b is not null)
 

select ident,a/b from #a

where b<>0 and b is not null
 

select ident,a/b from #a

where ident in (select ident from #a where b <> 0 and b is not null)

go

drop table #a

Open in new window

0
 
LVL 60

Expert Comment

by:chapmandew
ID: 22653340
is the key_id field unique in the table?
0
 
LVL 4

Assisted Solution

by:rehand
rehand earned 166 total points
ID: 22653369
I just ran a test similar to BrandonGalderisi's but add the insertions if null values, and did not have any issues either. Try handling the exceptions in the query and not excluding them in the select and see if you still have the problem.
0
 
LVL 1

Author Comment

by:marcgale
ID: 22653416
key_id is unique.
"about 500000" = 512241, if that matters.
0
 
LVL 60

Expert Comment

by:chapmandew
ID: 22653441
do you get the error when you do this?

select key_id, a, case when b = 0 then 0 else a/b end  x

If you do not, then use it.
0
 
LVL 1

Author Comment

by:marcgale
ID: 22653530
rehand: there is no question that explicitly handling the exceptions solves the problem. My problem now is a crisis of faith. All 6 queries *should* work, but 3 of them don't. It scares the heck out of me. How many queries do I have running that I think are producing accurate results but are succumbing to the same problem?

I realize that re-creating my problem will be difficult. I'm just hoping that someone will be able to give me a sense of what secret rule I've broken so that I can not break it anymore.
0
 
LVL 8

Expert Comment

by:MikeP090797
ID: 23063988
Hi Marc
Did you manage to solve this issue? I am having something simular, except that my query has multiple subqueries and it's already 5 pages long.. If I add conditions to it, it will become like 20 pages, and I'd like to avoid that
0
 
LVL 1

Author Comment

by:marcgale
ID: 23066197
No satisfactory answer was ever received (in spite of the forced acceptance of an answer *flame*)
0

Featured Post

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Join & Write a Comment

Suggested Solutions

When writing XML code a very difficult part is when we like to remove all the elements or attributes from the XML that have no data. I would like to share a set of recursive MSSQL stored procedures that I have made to remove those elements from …
Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now