Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> Re: [SPAM] 10gR2 Upgrade .. Watch out

Re: [SPAM] 10gR2 Upgrade .. Watch out

From: Niall Litchfield <>
Date: Tue, 2 Jan 2007 14:56:47 +0000
Message-ID: <>

On 12/28/06, Nuno Souto <> wrote:
> Quoting Wolfgang Breitling <>:
> >
> > As far as I'm concerned it merely exposes a bug in your application
> > code, relying on group by to return the result set in a certain
> > order. Just because it happens to work in some cases (here Oracle pre
> > 10gR2) does not make it the default behaviour. It was just a side
> > effect of an implementation detail. The default behaviour is that
> > without the order by caluse Oracle can return the resultset in any
> > order it darn well pleases, including in the group by order, but it
> > doesn't have to. And Oracle warned all along not to rely on it.
> >
> as much as I might agree with this, the simple fact
> is that in the given example "a=20" doesn't work but
> "a between 20 and 20" works. If that is not the
> definition of a bug, I don't know what is.

I'm not sure that I agree. I certainly don't agree if you are suggesting that results coming back in a different order from equivalent, but differently constructed, SQL statements without an order by clause should be a bug, that definitely falls into the works as documented category. On the other hand presumably you get different results here because the execution plan is different from one statement to the other, whether logically identical unhinted sql statements should have physically identical execution plans is an interesting question - my gut feeling is that they should get the same (optimal) execution plan, but I'm open to persuasion otherwise.

that is should

select ename from emp where eno = 20;
have the same plan in all circumstances as select ename from emp where eno between 20 and 20;

I know that binds would mess this up anyway so this is probably a moot point.


> Niall Litchfield
> Oracle DBA
Received on Tue Jan 02 2007 - 08:56:47 CST

Original text of this message