Re: SQL BUG in 7.3.2.3.2 engine!?

Depending on your luck, you may have to upgrade to 7.3.3.3, I have reproduced the problem in 7.3.3.0
There is a (known) bug that misses the UNIQUE bit of 'sort unique' when dealing with disitnct values,
and distinct values are the requirement of IN and NOT clauses.

dcampisi_at_lydall.com wrote in article
<876404200.17349_at_dejanews.com>...
> 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.
>
> 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