Oracle为什么不提出"ORA-00918:列定义不明确"?对于这个查询? [英] Why doesn't Oracle raise "ORA-00918: column ambiguously defined" for this query?

问题描述

我刚刚在Oracle中遇到了一个奇怪的行为，我希望可以提高ORA-00918，但事实并非如此.以该查询为例.

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).

无论如何，尝试运行此查询，您会得到ORA-00918，因为USER_TABLES和USER_TRIGGERS都有一个名为STATUS的列，因此要使查询运行WHERE子句，需要将其更改为TRG.STATUS.好的，很酷，但是请尝试加入另一个表.

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'

此查询在不限定您要表示哪个STATUS列的情况下非常有效！不用担心语义或查询返回的内容，没有错误. USER_CONSTRAINTS甚至还有一个名为STATUS的列，所以当有两个列可供选择时，怎么会不知道该怎么办，但还有更多歧义可以吗?

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?

顺便说一下，所有这些都在10.2.0.3.0上，并且如果查询中有两个以上的表，本质上将停止引发ORA-00918.如果这是一个Oracle错误，是否有人知道它的修复时间，因此如果升级我们的数据库，哪个Oracle版本可能导致牛仔查询崩溃?

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?

更新

感谢BQ演示该错误已在11.2.0.1.0中修复.凡是可以在更早版本中显示它的人都可以得到赏金！

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!

推荐答案

无法确定修复的时间，但这是我的结果:

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

这篇关于Oracle为什么不提出"ORA-00918:列定义不明确"?对于这个查询?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！