Re: Different results for the same query in 10g and 11g

From: onedbguru <onedbguru_at_yahoo.com>
Date: Fri, 1 Jul 2011 07:07:43 -0700 (PDT)
Message-ID: <1ce1e3ae-9d9e-44ef-bad0-83d0c99168dd_at_g2g2000vbl.googlegroups.com>

On Jun 28, 11:27 am, Grzegorz <grzegor..._at_interia.pl> wrote:
> On 2011-06-27 22:09, Santana wrote:
>
> > Hi all.
> > I need your help for the following query that return one row in 10g
> > and dont return any row in 11g: I know which ther is others ways to
> > implement
> > this query but i wan understand what is wrong!
>
> > This is mysterious query :
>
> > select *
> > from tests a
> > where id=nvl(( select max(b.id)
> >                  from tests b where b.id>2
> >                  and a.id=b.id
> >              ),
>
> Answered at oracle-l by Timur Akhmadeev
>
> It s a feature 7215982 unnest subquery embedded inside an expression .
>  Turning it off with the _fix_control will fix it.
>
> Regards
> GG

Make sure you gather statistics as well.. I have seen interesting results when there are no statistics on the table. Received on Fri Jul 01 2011 - 09:07:43 CDT