Re: SQL BUG in 7.3.2.3.2 engine!?

On 9 Oct 1997 00:44:42 GMT, harrist_at_ucs.orst.edu (Timothy Harris) wrote:

We had a similar problem with queries involving NOT IN in ORACLE 7.3.2 (and raised a TAR with ORACLE support). I suspect it was introduced by some optimiser improvements they put in. Switching to rule based optimisation is one workaround. The problem is fixed in 7.3.3.

Regards,
Michael Harvey.

>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
Received on Thu Oct 09 1997 - 00:00:00 CDT