Re: [SPAM] 10gR2 Upgrade .. Watch out
- From: "Niall Litchfield" <niall.litchfield@xxxxxxxxx>
- To: dbvision@xxxxxxxxxxxx
- Date: Tue, 2 Jan 2007 14:56:47 +0000
On 12/28/06, Nuno Souto <dbvision@xxxxxxxxxxxx> wrote:
Quoting Wolfgang Breitling <breitliw@xxxxxxxxxxxxx>:
>
> 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
http://www.orawin.info
Other related posts:
