Path: g2news2.google.com!postnews.google.com!g2g2000vbl.googlegroups.com!not-for-mail
From: onedbguru <onedbg...@yahoo.com>
Newsgroups: comp.databases.oracle.server
Subject: Re: Different results for the same query in 10g and 11g
Date: Fri, 1 Jul 2011 07:07:43 -0700 (PDT)
Organization: http://groups.google.com
Lines: 29
Message-ID: <1ce1e3ae-9d9e-44ef-bad0-83d0c99168dd@g2g2000vbl.googlegroups.com>
References: <5ab9936f-80b0-4117-8bd3-366952d98ac3@a31g2000vbt.googlegroups.com>
 <iucrsp$9h1$1@news.task.gda.pl>
NNTP-Posting-Host: 173.64.104.201
Mime-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable
X-Trace: posting.google.com 1309530256 6166 127.0.0.1 (1 Jul 2011 14:24:16 GMT)
X-Complaints-To: groups-abuse@google.com
NNTP-Posting-Date: Fri, 1 Jul 2011 14:24:16 +0000 (UTC)
Complaints-To: groups-abuse@google.com
Injection-Info: g2g2000vbl.googlegroups.com; posting-host=173.64.104.201; posting-account=RH60QQkAAADzKhzlh9Wlkn9a3iP-8DFN
User-Agent: G2/1.0
X-HTTP-UserAgent: Mozilla/5.0 (Windows NT 5.1) AppleWebKit/534.30 (KHTML, like
 Gecko) Chrome/12.0.742.112 Safari/534.30,gzip(gfe)

On Jun 28, 11:27=A0am, Grzegorz <grzegor...@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=3Dnvl(( select max(b.id)
> > =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0from tests b where b.id>2
> > =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0and a.id=3Db.id
> > =A0 =A0 =A0 =A0 =A0 =A0 =A0),
>
> Answered at oracle-l by Timur Akhmadeev
>
> It s a feature 7215982 unnest subquery embedded inside an expression .
> =A0Turning 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.