Re: Finding a SQL bug in 9.2.0.8

On Sep 5, 9:29 am, DA Morgan <damor..._at_psoug.org> wrote:
> DG problem wrote:
> > On a HP-UX machine running Oracle Enterprize 9208
>
> > This query (has been simplified)
>
> > select a,b
> > from t1
> > where (a,b) not in (
> > --  select * from (
> >       select a,b
> >       from t1
> >       where a in (1,2)
> > --  )
> > );
>
> > returns a different number of rows to this query
>
> > select a,b
> > from t1
> > where (a,b) not in (
> >   select * from (
> >     select a,b
> >     from t1
> >     where a in (1,2)
> >   )
> > );
>
> > The last query returns the correct number of rows.
>
> > The sub query has a complex select part which includes
>
> > Case When ... in (8,10) and Lag(...,1,null) over (order by ..., ...)
> > = ...
>
> > This seems like a bug to me, but has anyone got any tips on finding
> > the bug in metalink?
>
> SQL> create table t1 (
>    2  a number,
>    3  b number);
>
> Table created.
>
> SQL> insert into t1 values (1,1);
>
> 1 row created.
>
> SQL> insert into t1 values (1,2);
>
> 1 row created.
>
> SQL> insert into t1 values (2,1);
>
> 1 row created.
>
> SQL> insert into t1 values (2,2);
>
> 1 row created.
>
> SQL> insert into t1 values (3,3);
>
> 1 row created.
>
> SQL> select a,b
>    2  from t1
>    3  where (a,b) not in (
>    4  --  select * from (
>    5        select a,b
>    6        from t1
>    7        where a in (1,2)
>    8  --  )
>    9  );
>
>           A          B
> ---------- ----------
>           3          3
>
> SQL> select a,b
>    2  from t1
>    3  where (a,b) not in (
>    4    select * from (
>    5      select a,b
>    6      from t1
>    7      where a in (1,2)
>    8    )
>    9  );
>
>           A          B
> ---------- ----------
>           3          3
>
> SQL>
>
> If the problem exists there is a simple solution ... move to a
> currently supported version of the product.
> --
> Daniel A. Morgan
> Oracle Ace Director & Instructor
> University of Washington
> damor..._at_x.washington.edu (replace x with u to respond)
> Puget Sound Oracle Users Groupwww.psoug.org- Hide quoted text -
>
> - Show quoted text -

A nice example, but I expect it doesn't reflect the reality of th OP's situation, judging by the code snippet he did provide:

Case When ... in (8,10) and Lag(...,1,null) over (order by ..., ...)

I think a better representation of the actual query, including table ddl and sample data, would provide a better platform from which to 'launch' advice.

My two cents.

David Fitzjarrell Received on Fri Sep 05 2008 - 09:43:21 CDT