Why doesn't Oracle raise “ORA-00918: column ambiguously defined” for this query?

Question

I've just come across a strange behaviour in Oracle where I would expect ORA-00918 to be raised, but isn't. Take this query, for example.

SELECT *
FROM USER_TABLES TAB
JOIN USER_TRIGGERS TRG ON TRG.TABLE_NAME = TAB.TABLE_NAME
WHERE STATUS = 'DISABLED'

This query is notionally looking for the details of tables with disabled triggers, but please note that this is not the problem I'm trying to solve. The problem is not unique to this query, the data dictionary, views or tables; as far as I can tell it applies to any set of tables or views (from the two or three I've tried).

Anyway, try to run this query and you get ORA-00918 because both USER_TABLES and USER_TRIGGERS have a column called STATUS so to get the query to run the WHERE clause needs to be changed to TRG.STATUS. Ok, cool, but try instead joining another table.

SELECT *
FROM USER_TABLES TAB
JOIN USER_TRIGGERS TRG ON TRG.TABLE_NAME = TAB.TABLE_NAME
JOIN USER_CONSTRAINTS CON ON CON.TABLE_NAME = TAB.TABLE_NAME
WHERE STATUS = 'DISABLED'

This query, without qualifying which STATUS column you mean, magically works! Never mind the semantics or what the query returns, there is no error. USER_CONSTRAINTS even has a column called STATUS too, so how come it doesn't know what to do when there are two columns to choose from but it's okay with even more ambiguity?

This is all on 10.2.0.3.0 by the way, and in essence ORA-00918 stops being raised if you have more than two tables in your query. If this is an Oracle bug, does anyone know when it was fixed and so which Oracle version is likely to cause cowboy queries to blow up if our database is upgraded?

Update

Thanks to BQ for demonstrating the bug is fixed in 11.2.0.1.0. Bounty for anyone that can show it fixed in an earlier version!

very good question indeed, you can select distinct status on the second query, and select distinct tab.status and you get different results. — Benoit
I've been stung by this issue before. Couldn't work why a key column now had nulls after outer joining to another table. Good to see I'm not the only one! And glad to see it's fixed in 11g. — Mike Meyers

BQ. BQ. · Accepted Answer · 2010-11-24T16:53:03

Can't say when it was fixed, but here's my results:

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

SQL> SELECT *
  2  FROM USER_TABLES TAB
  3  JOIN USER_TRIGGERS TRG ON TRG.TABLE_NAME = TAB.TABLE_NAME
  4  WHERE STATUS = 'DISABLED';
WHERE STATUS = 'DISABLED'
      *
ERROR at line 4:
ORA-00918: column ambiguously defined

SQL> ed
Wrote file afiedt.buf

  1  SELECT *
  2  FROM USER_TABLES TAB
  3  JOIN USER_TRIGGERS TRG ON TRG.TABLE_NAME = TAB.TABLE_NAME
  4  JOIN USER_CONSTRAINTS CON ON CON.TABLE_NAME = TAB.TABLE_NAME
  5* WHERE STATUS = 'DISABLED'
SQL> /
WHERE STATUS = 'DISABLED'
      *
ERROR at line 5:
ORA-00918: column ambiguously defined

Why doesn't Oracle raise “ORA-00918: column ambiguously defined” for this query?

5 Answers