Why doesn't Oracle throw an "ambiguous column reference" here?

Question

I have a couple of SQL queries here-

WITH emp AS
         (SELECT 1 AS empid, 'Adam' AS ename, 10 AS deptno, 'Broker' AS description FROM dual
          UNION ALL
          SELECT 2, 'Bob', 20, 'Accountant' FROM dual
          UNION ALL
          SELECT 3, 'Charles', 30, 'Programmer' FROM dual
          UNION ALL
          SELECT 4, 'Dan', 10, 'Manager' FROM dual
          UNION ALL
          SELECT 5, 'Eric', 10, 'Salesman' FROM dual
          UNION ALL
          SELECT 6, 'Franc', 20, 'Consultant' FROM dual),
     dept AS
         (SELECT 10 AS deptno, 'Accounts' AS dname, 100 employment_type_id FROM dual
          UNION ALL
          SELECT 20, 'Broking', 100 FROM dual
          UNION ALL
          SELECT 30, 'Corporate Relations', 200 FROM dual),
     employment_type AS
         (SELECT 100 AS employment_type_id, 'Permanent' AS description FROM dual
          UNION ALL
          SELECT 200, 'Contract' FROM dual)
/* --- Query 1
select e.ename, d.dname, e.description
  from emp e
       inner join dept d on e.deptno = d.deptno
       inner join employment_type e on d.employment_type_id = e.employment_type_id
-- */
-- /* Query 2
SELECT e.ename, d.dname, e.description
  FROM employment_type e
       INNER JOIN dept d ON e.employment_type_id = d.employment_type_id
       INNER JOIN emp e ON d.deptno = e.deptno
-- */
;

As you can see in both the queries the alias for tables emp and employment_type are the same, i.e., e.

When I select a column by saying e.description shouldn't I get an error saying something like

ambiguous column reference

Morevoer, the result of the two queries is different! In the first, emp.description is selected while in the second, employment_type.description is selected.

Please let me know why this happens and how the confusion arising from this can be avoided.

Answer 1

Oracle SQL never complied fully to any ANSI/ISO SQL Standard. For example, it never supported AS in the from clause:

select *from dual AS d; -- fails

The current status of compliance (Oracle Compliance To Core SQL:2011 for Oracle 12c) shows that various features of ANSI SQL supported mostly partially, for example:

...
E031, Identifiers:
    Oracle supports this feature, with the following exceptions:
    ...

or,

E051, Basic query specification
    Oracle fully supports the following subfeatures:
    ...

And while it does not say anything about ambiguous aliases (or range variables officially), you may envisage that differences can easily go deeper than stated in the page.

I currently know no way to make Oracle report ambiguity in such cases, but just paying attention to make your aliases distinct is not that hard in my opinion.

You may wonder if ANSI SQL Standard says exactly that duplicate aliases in the same scope are not allowed. It does, in section 7.6 of Part 2, SQL/Foundation document of SQL:2011 standard. (You can download the draft from www.wiscorp.com). Specifically, in subsection Syntax Rules, 10), it says (I cut it a little):

10) Let RV be a range variable that is exposed by TR. Let RV1 be a range variable that is exposed by a <table reference> TR1 that has the same scope clause as TR. 
   a) If RV is a <table name>, then
      i) If RV1 is a <table name>, then RV1 shall not be equivalent to RV.
      ii) Otherwise, RV1 shall not be equivalent to the <qualified identifier> of RV.
   b) Otherwise
      i) If RV1 is a <table name>, then the <qualified identifier> of RV1 shall not be equivalent to RV.
      ii) Otherwise, RV1 shall not be equivalent to RV.

Here RV's are variable ranges, and you can see that the last choice applies to the case of two aliases.

We know that major SQL brands implement this check (SQL Server, MySQL, PostgreSQL), so this information should be accurate, despite coming from a draft.