Re: Finding a SQL bug in 9.2.0.8

On Sep 5, 12:11 am, DG problem <skatef..._at_gmail.com> 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?

You really need to set up a repeatable test case that anyone could run (table ddl, sample data) using a query as similar to the actual production code as possible. Having such a test case would then allow you to submit an SR with Oracle regarding this issue, although since 9.2.0.8 is now in extended support I wouldn't expect much more than an 'upgrade to at least 10.2.0.3' directive from Oracle Support. Also, having such a test case would allow those of us using 10.2.0.x and 11.1 releases to test and verify these versions are not affected.

Given what little you've posted I doubt you, or anyone else, will have much success searching MetaLink for this issue.

David Fitzjarrell Received on Fri Sep 05 2008 - 07:43:11 CDT