SQL0801N Division by zero was attempted 
Author Message
 SQL0801N Division by zero was attempted
Hi,

I have a problem to understand this:

connect to sample;
select * from staff where 10000/(comm-180) and comm > 180 > 0;
select * from staff where comm > 180 and 10000/(comm-180) > 0;

With the first query I got the rows without an error. With the second
query I got the SQL-Error SQL0801 Division by zero was attempted.

I think I must always get the same!

With an complex query I got the same when I changed the optimization
level in the same complex query.

Regards

Michael



Mon, 18 Jul 2005 22:31:00 GMT
 SQL0801N Division by zero was attempted

The optimizer chooses ordering of predicates. If there is no reason to
order yoyu get whatever.....
Try this: WHERE comm IS NOT NULL AND 10000/(COALESCE(comm, 0) -180) > 0

--
Serge Rielau
DB2 UDB SQL Compiler Development
IBM Software Lab, Toronto

Visit DB2 Developer Domain at
http://www7b.software.ibm.com/dmdd/



Mon, 18 Jul 2005 23:03:39 GMT
 SQL0801N Division by zero was attempted
Serge Rielau wrote on Thursday 30 January 2003 16:03:

Quote:
> The optimizer chooses ordering of predicates. If there is no reason to
> order yoyu get whatever.....
> Try this: WHERE comm IS NOT NULL AND 10000/(COALESCE(comm, 0) -180) > 0

I think the problem is not with the "comm" but rather with the result of the
expression "comm - 180".

How about this:

WHERE 10000 / NULLIF(comm-180) AND comm > 180

This will result in "comm - 180" to be mapped to a NULL if comm is indeed 180.  
And that, in turn will give 10000 / NULL --> NULL.

--
Knut Stolze
Information Integration
IBM Germany / University of Jena



Tue, 19 Jul 2005 00:01:40 GMT
 SQL0801N Division by zero was attempted
Yes you are right. I was still asleep. Div by zero is different from div
by NULL.

--
Serge Rielau
DB2 UDB SQL Compiler Development
IBM Software Lab, Toronto

Visit DB2 Developer Domain at
http://www7b.software.ibm.com/dmdd/



Tue, 19 Jul 2005 00:22:25 GMT
 SQL0801N Division by zero was attempted
You're all sleeping today... !!!

change
NULLIF(comm-180)
to
NULLIF(comm, 180)

;-))

PM



Tue, 19 Jul 2005 02:57:50 GMT
 SQL0801N Division by zero was attempted
Do you mean:
select * from staff where comm > 180 and comm < 10180;
Quote:

>Hi,

>I have a problem to understand this:

>connect to sample;
>select * from staff where 10000/(comm-180) and comm > 180 > 0;
>select * from staff where comm > 180 and 10000/(comm-180) > 0;

>With the first query I got the rows without an error. With the second
>query I got the SQL-Error SQL0801 Division by zero was attempted.

>I think I must always get the same!

>With an complex query I got the same when I changed the optimization
>level in the same complex query.

>Regards

>Michael



Tue, 19 Jul 2005 02:57:57 GMT
 SQL0801N Division by zero was attempted

Quote:

> Hi,

> I have a problem to understand this:

> connect to sample;
> select * from staff where 10000/(comm-180) and comm > 180 > 0;
> select * from staff where comm > 180 and 10000/(comm-180) > 0;

> With the first query I got the rows without an error. With the second
> query I got the SQL-Error SQL0801 Division by zero was attempted.

In most cases when I ran into similar problems, I was able to rewrite the
predicates so that they didnt include division. In this particular case I
would aim for:

        10000/(comm-180) > 0 <=>
              (comm-180) > 0 <=>
               comm      > 180

I.e.

        select * from staff where comm > 180

should do the trick, or am I missing something?

HTH
/Lennart

Quote:
> I think I must always get the same!

> With an complex query I got the same when I changed the optimization
> level in the same complex query.

> Regards

> Michael



Tue, 19 Jul 2005 03:44:16 GMT
 SQL0801N Division by zero was attempted
PM (pm3iinc-nospam) wrote on Thursday 30 January 2003 19:57:

Quote:
> You're all sleeping today... !!!

Yep, it was very late (about 3 am my time ;-))

Quote:
> change
> NULLIF(comm-180)
> to
> NULLIF(comm, 180)

Right.

--
Knut Stolze
Information Integration
IBM Germany / University of Jena



Wed, 20 Jul 2005 03:51:22 GMT
 SQL0801N Division by zero was attempted
Hi,

thanks for your answers!

I know there are exist some workarounds, but I want to know is this a
bug? Is this ANSI-SQL?

With SQL-Server, Access,... I got no errors, the database checks first
the 'comm > 180'-clause and second the division.

Regards

Michael



Fri, 22 Jul 2005 22:04:20 GMT
 SQL0801N Division by zero was attempted
hobbit wrote on Monday 03 February 2003 15:04:

Quote:
> Hi,

> thanks for your answers!

> I know there are exist some workarounds, but I want to know is this a
> bug? Is this ANSI-SQL?

The error is perfectly fine.  It is up to the DBMS to decide which predicate
is to be evaluated first.  The issue gets even more interesting if you have
parallel execution of a statement, for example on MPP systems.

Note that SQL does not have a concept like sequence points in C/C++.

Quote:
> With SQL-Server, Access,... I got no errors, the database checks first
> the 'comm > 180'-clause and second the division.

You were just lucky, I'd say.

--
Knut Stolze
Information Integration
IBM Germany / University of Jena



Fri, 22 Jul 2005 22:28:29 GMT
 SQL0801N Division by zero was attempted
Does this ensure the sequence of operations?
select *
  from (SELECT * FROM staff where comm > 180
       ) AS Q
 WHERE 10000/(comm-180) > 0
Quote:

> hobbit wrote on Monday 03 February 2003 15:04:

> > Hi,

> > thanks for your answers!

> > I know there are exist some workarounds, but I want to know is this a
> > bug? Is this ANSI-SQL?

> The error is perfectly fine.  It is up to the DBMS to decide which predicate
> is to be evaluated first.  The issue gets even more interesting if you have
> parallel execution of a statement, for example on MPP systems.

> Note that SQL does not have a concept like sequence points in C/C++.

> > With SQL-Server, Access,... I got no errors, the database checks first
> > the 'comm > 180'-clause and second the division.

> You were just lucky, I'd say.



Sat, 23 Jul 2005 06:23:35 GMT
 SQL0801N Division by zero was attempted
Tokunaga T. wrote on Monday 03 February 2003 23:23:

Quote:
> Does this ensure the sequence of operations?
> select *
>   from (SELECT * FROM staff where comm > 180
>        ) AS Q
>  WHERE 10000/(comm-180) > 0

I would think so because the subquery must not return any rows where comm =
180.  Thus, the outer query could never run into a div by zero.  If you see
something different, then I would assume that the optimizer does something it
shouldn't do.  (But then you have to ask someone else about it. ;-))

--
Knut Stolze
Information Integration
IBM Germany / University of Jena



Sat, 23 Jul 2005 20:00:33 GMT
 SQL0801N Division by zero was attempted
I think it should be fine since the order of evaluation is something like :
From
where
group
order
select

PM



Sun, 24 Jul 2005 03:17:51 GMT
 
 [ 13 post ] 

 Relevant Pages 

1. Division by Zero?

2. ODBC Division By Zero (SQL Server)

3. Partition Explorer division by zero

4. division by zero

5. dIVISION BY ZERO WITHIN STORED PROCEDURES

6. Division by Zero, need solution

7. Infinity and Division by Zero

8. Division results in one or zero

9. ODBC Division by zero ERROR

10. Trace flag for division by zero

11. Access to SQL upsizing tool - Division by zero error

12. Division by Zero


 
Powered by phpBB® Forum Software