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!

Answer 1

Searched Oracle Support and found this:

Bug 5368296 - ANSI join SQL may not report ORA-918 for ambiguous column [ID 5368296.8]

Versions confirmed as being affected:

• 10.2.0.3
• 10.2.0.4

This issue is fixed in

• 10.2.0.4 Patch 2 on Windows Platforms
• 10.2.0.5 (Server Patch Set)
• 11.1.0.6 (Base Release)

Not posting more than that since you need an Oracle Support account to view the details, but thought the Oracle Bug number/versions affected would be okay to share to point you in the right direction on Oracle Support.