Re: Finding a SQL bug in 9.2.0.8

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)

          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)

          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
damorgan_at_x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org