| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: SQL BUG in 7.3.2.3.2 engine!?
Yes, it is a bug in 7.3.2.3, even if Oracle does not admit it.. It is fixed (probably by the Oracle development group that does not admit it) in 7.3.3. This bug is even more puzzling "features". Try to put DISTINCT into IN subquery:
select ROWID, EMPNO, ENAME
from EMP
where 'Y' not in
(select 'X'
from SYS.DUAL)
and 'X' in
(select DISTINCT 'X'
from all_users
where username like 'SYS%')
You would assume that distinct will take care of it. But guess what - query returns multiple rows. Anyway, your only choice is 7.3.3 upgrade.
Solomon Yakobson.
In article <61h9dq$e5_at_news.orst.edu>,
harrist_at_ucs.orst.edu (Timothy Harris) wrote:
>
> 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%')
> and EMPNO = 7788
>
> 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
-------------------==== Posted via Deja News ====-----------------------
http://www.dejanews.com/ Search, Read, Post to Usenet
Received on Thu Oct 09 1997 - 00:00:00 CDT
 |
 |