Why I can not using alias in 11G

[Havel Zhang]

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

[Maxim Demenko]

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

[Serge Rielau]

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

[Havel Zhang]

yes, the query work fine in 10g, but cause ora-918 in 11G.

[Shakespeare]

Op 8-2-2010 2:16, Havel Zhang schreef:

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

[Mark D Powell]

If you prefix the select list supplier_code column with
a.supplier_code does the error go away?

I am not sure if Oracle considers an inline view to be a sub-query but
the Oracle SQL manual specifically states to always alias all sub-
query columns:

>> ver 10gR2 SQL Ch 9 'Using Subqueries' >>
If columns in a subquery have the same name as columns in the
containing statement, then you must prefix any reference to the column
of the table from the containing statement with the table name or
alias. To make your statements easier to read, always qualify the
columns in a subquery with the name or alias of the table, view, or
materialized view
<<

Also if prefixing does not eliminate the issue, try using a normal pre-
ANSI-92 join syntax. That should get you around the issue.

HTH -- Mark D Powell --