Prev: So whats up with the 11.2 java security hole?
Next: Best way to duplicate 11g table and range partition
From: Havel Zhang on 7 Feb 2010 20:16
hi all:
I found some query will cause ORA-00918 error(column ambiguously
defined) in 11G, but running well in 10G.
I give an example:
---------------------------
select

supplier_code,dept_code,local_name,english_name
from
supplier a inner join
(select supplier_code s,
dept_code d,
max(rowid) r
from supplier
group by
supplier_code,dept_code
) b on a.supplier_code =
b.s and a.dept_code = b.d and a.rowid = b.r

---------------------------
The query above can running well in 10G, but will cause an
error on 11G, of course, I can rewrite the query, but who can tell me
why, 11G have new feature ? have any parameter to solve this problem?

Thank you.

Havel
From: Maxim Demenko on 7 Feb 2010 21:54
On 08.02.2010 02:16, Havel Zhang wrote:
> hi all:
> I found some query will cause ORA-00918 error(column ambiguously
> defined) in 11G, but running well in 10G.
> I give an example:
> ---------------------------
> select
>
> supplier_code,dept_code,local_name,english_name
> from
> supplier a inner join
> (select supplier_code s,
> dept_code d,
> max(rowid) r
> from supplier
> group by
> supplier_code,dept_code
> ) b on a.supplier_code =
> b.s and a.dept_code = b.d and a.rowid = b.r
>
> ---------------------------
> The query above can running well in 10G, but will cause an
> error on 11G, of course, I can rewrite the query, but who can tell me
> why, 11G have new feature ? have any parameter to solve this problem?
>
> Thank you.
>
> Havel

There was an opposite bug - not generating an ORA-00918 with ansi join
and ambiguously defined columns, so that Oracle picked the columns at a
random and results were inconsistent. It was fixed and now you have to
alias all the columns using ansi join (at least it was my impression
from the note which i referenced) , so the bug you are hitting is closed
as not a bug. See Note 835701.1, Bug 7343313, Bug 6760937, Bug 5368296.

Best regards

Maxim
From: Serge Rielau on 8 Feb 2010 01:22
That's odd.. the query looks fine by me.
Assuming that the issue is the GROUP BY clause then it is correct NOT to
specify the column aliases here because they are used for the output of
the SELECT list. GROUP BY is processed before SELECT.
The GROUP BY cannot see e.g. "D".
From an ANSI point of view I see nothing wrong with this query.

--
Serge Rielau
SQL Architect DB2 for LUW
IBM Toronto Lab

From: Havel Zhang on 8 Feb 2010 02:12
On 2ÔÂ8ÈÕ, ÏÂÎç2ʱ22·Ö, Serge Rielau <srie...(a)ca.ibm.com> wrote:
> That's odd.. the query looks fine by me.
> Assuming that the issue is the GROUP BY clause then it is correct NOT to
> specify the column aliases here because they are used for the output of
> the SELECT list. GROUP BY is processed before SELECT.
> The GROUP BY cannot see e.g. "D".
> From an ANSI point of view I see nothing wrong with this query.
>
> --
> Serge Rielau
> SQL Architect DB2 for LUW
> IBM Toronto Lab

> From an ANSI point of view I see nothing wrong with this query.
yes, the query work fine in 10g, but cause ora-918 in 11G.

From: Shakespeare on 8 Feb 2010 05:19
Op 8-2-2010 2:16, Havel Zhang schreef:
> hi all:
> I found some query will cause ORA-00918 error(column ambiguously
> defined) in 11G, but running well in 10G.
> I give an example:
> ---------------------------
> select
>
> supplier_code,dept_code,local_name,english_name
> from
> supplier a inner join
> (select supplier_code s,
> dept_code d,
> max(rowid) r
> from supplier
> group by
> supplier_code,dept_code
> ) b on a.supplier_code =
> b.s and a.dept_code = b.d and a.rowid = b.r
>
> ---------------------------
> The query above can running well in 10G, but will cause an
> error on 11G, of course, I can rewrite the query, but who can tell me
> why, 11G have new feature ? have any parameter to solve this problem?
>
> Thank you.
>
> Havel

Apart from the bug you encounter, I definitely think you should rethink
your query, if this is a real life example and if I understand its
functionality.

Shakespeare
 |  Next  |  Last
Pages: 1 2
Prev: So whats up with the 11.2 java security hole?
Next: Best way to duplicate 11g table and range partition