Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 847
  • Last Modified:

Divide by Zero problem with large dataset

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
marcgale
Asked:
marcgale
  • 7
  • 5
  • 2
  • +2
3 Solutions
 
chapmandewCommented:
do something like this:

select key_id, a, case when b = 0 then 0 else a/b end  x
0
 
marcgaleAuthor Commented:
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
 
rehandCommented:
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
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
marcgaleAuthor Commented:
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
 
chapmandewCommented:
You've already been told why the error occurred.
0
 
marcgaleAuthor Commented:
I mistyped: query 1 and query 4 produce correct results. Queries 2 and 3 produce div by zero.
0
 
chapmandewCommented:
and it is because the field b has the value 0 in there.....
0
 
marcgaleAuthor Commented:
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
 
BrandonGalderisiCommented:
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
 
BrandonGalderisiCommented:
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
 
chapmandewCommented:
is the key_id field unique in the table?
0
 
rehandCommented:
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
 
marcgaleAuthor Commented:
key_id is unique.
"about 500000" = 512241, if that matters.
0
 
chapmandewCommented:
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
 
marcgaleAuthor Commented:
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
 
MikeP090797Commented:
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
 
marcgaleAuthor Commented:
No satisfactory answer was ever received (in spite of the forced acceptance of an answer *flame*)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 7
  • 5
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now