| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> SQL BUG in 7.3.2.3.2 engine!?
We've sent this bug into Oracle but they're taking their sweet time with it so I thought I'd ask others if they've seen this behavior.
We found this when one of our programs started multiplying dollar amounts for no good reason... I've boiled the select statment down to it's most basic form which should work with any machine that has the EMP table. If you don't, then pick another generally simple table in place of EMP.
It looks like a 'NOT IN' subquery, followed by an 'IN' subquery is where it gets confused.
You'll notice that the
first two subquery conditions are nonsense and both will evaluate out to true
thus they *should* have no effect on the query. What really happens though is
that SQL will return one row for EACH item returned by the 2nd subquery. I
picked all users and chose only usernames that start with SYS thus this query
will incorrectly return 2 rows(if you switch the 'SYS' to 'OPS', you'll get a
ton more). I selected ROWID to show that we are *not* returning multiple rows
from EMP(look at the data...). The same ROWID shows up several times and that
should *NEVER* happen when selecting from a single table. Switching the order
of the two subqueries in the where clause fixes the problem(a single row
returned); which proves something fishy is going on since switching the order
of ands should have no effect on the logic.(replacing the subqueries with
constants also makes the problems dissapear)
select ROWID, EMPNO, ENAME
from EMP
where 'Y' not in
(select 'X'
from SYS.DUAL)
and 'X' in
(select 'X'
from all_users
where username like 'SYS%')
The person at Oracle did state that since I'm getting identical rows back, it doesn't sound like a big deal. What she didn't understand is that my original query is far more complicated than this one and that it's grouping rows and summing dollar amounts!! I'm also worried where else in our bazillions of lines of code this may be sticking us again...
I'll try and monitor the Oracle groups but if you have some light to shed please e-mail me at harrist_at_ucs.orst.edu in addition to posting here.
Thanks,
Tim Harris
Functional/Technical Analyst
Oregon State University
Received on Thu Oct 09 1997 - 00:00:00 CDT
 |
 |