Re: Anyone have more information about metalink note 362585.1 or bug 4622729?

can you send the explain plan for the query giving the wrong result (with predicates)?

On 1/10/07, Charles Schultz <sacrophyte_at_gmail.com> wrote:
>
> We did apply it and it did not resolve our problem, hence the new SR.
> Kinda makes me wonder what the one-off actually does. =)
>
> On 1/10/07, Ghassan Salem <salem.ghassan_at_gmail.com> wrote:
> >
> > From what I see, it's not fixed in 10.2.0.2, but .3, there is a one-off
> > on top of .2 that you can apply.
> > rgds
> >
> > On 1/10/07, Charles Schultz <sacrophyte_at_gmail.com> wrote:
> > >
> > > We applied the patch for 10.2.0.2, but we are still seeing the same
> > > problem as documented in the metalink note. We have already raised the issue
> > > with Oracle Support, but since I expect a less-than-complete answer from
> > > them about the internal details, I was hoping to pose a few questions here.
> > >
> > > Given the scenario in the note, exactly why are the filter conditions
> > > different for the two select statements? Which filter, specifically, is
> > > "NULL IS NOT NULL"? And why would cursor_sharing play a role in the filter
> > > operation? The more details the better, I am eager to learn. *grin*
> > >
> > > create table t1 (a number not null);
> > > create table t2 (b number,c varchar2(1));
> > > insert into t1 values (1);
> > > commit;
> > >
> > > set autotrace on explain
> > >
> > > SQL> select a from t1 where a not in (select b from t2 where b is not
> > > null and c ='');
> > > no rows selected
> > >
> > > SQL> select a from t1 where a not in (select b from t2 where b is not
> > > null and c =' ');
> > >
> > > A
> > > ----------
> > > 1
> > > SQL> select a from t1 where a not in (select b from t2 where b is not
> > > null and c ='');
> > > ...
> > > Predicate Information (identified by operation id):
> > > ---------------------------------------------------
> > >
> > > 1 - filter(NULL IS NOT NULL)
> > > 2 - access("A"="B")
> > > 4 - filter("B" IS NOT NULL)
> > >
> > > SQL> select a from t1 where a not in (select b from t2 where b is not
> > > null and c =' ');
> > > ...
> > > Predicate Information (identified by operation id):
> > > ---------------------------------------------------
> > >
> > > 1 - access("A"="B")
> > > 3 - filter("B" IS NOT NULL AND "C"=' ')
> > >
> > > --
> > > Charles Schultz
> >
> >
> >
>
>
> --
> Charles Schultz

--
http://www.freelists.org/webpage/oracle-l