Re: Finding a SQL bug in 9.2.0.8

On Sep 4, 10: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?

My crystal balls appear to be swollen today, and they say you have a situation where b is null that is behaving different than you expect when compared in a not-IN, or perhaps the out-of-window default of null is doing that. If you want to break my balls, follow the suggestions of a test case, and see if your actual plans have any clues.

Of course, I have no idea what I'm talking about with non-relational analytic functions. It's just my balls talking.

jg

--
@home.com is bogus.
Cox overloads poles:  http://www.signonsandiego.com/uniontrib/20080905/news_1n5puc.html