Re: SQL BUG in 7.3.2.3.2 engine!?

dcampisi_at_lydall.com wrote:
> 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%')
> and EMPNO = 7788
 

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

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

Thanks to drawing our attention to this bug. We found it also 'works' on our 7.3.2.1 Oracle. Symptoms are exactly the same to described in this thread.

Konstantin   Received on Fri Oct 10 1997 - 00:00:00 CDT