Re: Finding a SQL bug in 9.2.0.8

On Sep 5, 3:11 pm, 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?

Hi All,

Firstly, thanks for your time.

I realise that to get this fixed will require several hours of time to simplify and then isolate the problem so that it can be reproduced. This is not really worth it for me given that there is a simple work around which is the second example.

I was hoping, on the off chance, that someone may have experienced a similar problem and thus could point me to the metalink bug. I find searching for SQL bugs quite difficult due to the large amount of noise due to the large amount of common keywords, eg, SELECT, ORDER BY, LAG and so on.

Thanks once again. Received on Sun Sep 07 2008 - 20:20:37 CDT