Solved

Divide by Zero problem with large dataset

Posted on 2008-10-06
19
834 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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
 
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction This article will provide a solution for an error that might occur installing a new SQL 2005 64-bit cluster. This article will assume that you are fully prepared to complete the installation and describes the error as it occurred durin…
Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
This Micro Tutorial will teach you how to censor certain areas of your screen. The example in this video will show a little boy's face being blurred. This will be demonstrated using Adobe Premiere Pro CS6.
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.

803 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