Why Oracle 10g doesn't complain about column a

问题:

I'm using Oracle 10g (XE 10.2.0.1.0), and find a behavior that I don't understand:

select * 
from employees manager
    join employees worker on MANAGER.EMPLOYEE_ID = WORKER.MANAGER_ID
    join departments on DEPARTMENTS.manager_id = 108
where
    department_id = 100
;

The problem is I think Oracle should have complain about the ambiguity of department_id in the where clause, since it's a column in both the table employees and departments. The fact is in Oracle 10g, it doesn't, and the result shows that it interprets the department_id as the one in departments. However, if I comment out the second join statement (4th line above), Oracle does complain “ORA-00918: column ambiguously defined” as expected.

So, can somebody help to explain how the ambiguity is defined in Oracle 10g? Or perhaps this is a bug in 10g?

BTW: The tables are defined in the default HR schema bundled in the Oracle 10g.

Update: Just found a related post: Why does Oracle SQL mysteriously resolve ambiguity in one joins and does not in others

回答1:

I believe it is a bug in Oracle 10g that Oracle chose not to fix. When we were upgrading our applications from 10g to 11gR2, we found a couple of queries that were written "loosely" in respect of ambiguous column names but worked in Oracle 10g. They all stopped working in 11gR2. We contacted Oracle but they pretty much said that the tolerant behavior toward ambiguous column names is a correct behavior for Oracle 10g and the stringent behavior is the correct behavior for 11g.

回答2:

I think it is, because departments have no alias. Therefore everything without being qualified by an <alias>. is first treated to be from departments.

So I also think when you give departments an alias you should get the ORA-00918 again. Cannot test here though...