为什么 Oracle 不会为此查询引发“ORA-00918：列有歧义定义”？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4269205/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Why doesn't Oracle raise "ORA-00918: column ambiguously defined" for this query?
提问by batwad
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.
我刚刚在 Oracle 中遇到了一个奇怪的行为，我希望 ORA-00918 会被提出，但事实并非如此。以这个查询为例。
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_TABLESand USER_TRIGGERShave a column called STATUSso to get the query to run the WHEREclause needs to be changed to TRG.STATUS. Ok, cool, but try instead joining another table.
无论如何，尝试运行此查询，你会得到ORA-00918，因为两者USER_TABLES并USER_TRIGGERS有一个名为列STATUS，从而得到查询运行WHERE条款需要更改为TRG.STATUS。好的，很酷，但请尝试加入另一张桌子。
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_CONSTRAINTSeven has a column called STATUStoo, 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?
这个查询，没有限定您的意思是哪个 STATUS 列，神奇地工作！不管语义或查询返回什么，没有错误。 USER_CONSTRAINTS甚至还有一个列也被称为STATUS，那么当有两列可供选择时它为什么不知道该怎么做，但如果有更多的歧义就可以了？
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?
顺便说一下，这一切都在 10.2.0.3.0 上，如果您的查询中有两个以上的表，则本质上 ORA-00918 将停止引发。如果这是一个 Oracle 错误，有没有人知道它是什么时候修复的，那么如果我们的数据库升级，哪个 Oracle 版本可能会导致牛仔查询爆炸？
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!
感谢 BQ 演示该错误已在 11.2.0.1.0 中修复。任何可以在早期版本中修复它的人都可以获得赏金！
采纳答案by BQ.
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
回答by BQ.
Searched Oracle Support and found this:
搜索 Oracle 支持并找到了这个：
Bug 5368296 - ANSI join SQL may not report ORA-918 for ambiguous column [ID 5368296.8]
错误 5368296 - ANSI 连接 SQL 可能不会为不明确的列报告 ORA-918 [ID 5368296.8]
Versions confirmed as being affected:
确认受影响的版本：
- 10.2.0.3
- 10.2.0.4
- 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)
- Windows 平台上的 10.2.0.4 补丁 2
- 10.2.0.5（服务器补丁集）
- 11.1.0.6（基础版本）
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.
由于您需要一个 Oracle Support 帐户才能查看详细信息，因此不会发布更多信息，但认为可以分享受影响的 Oracle 错误编号/版本，以便为您指明 Oracle 支持的正确方向。
回答by erbsock
You are using ANSI SQL. I'm guessing that it associates the STATUS in the where clause with the driving table.
您正在使用 ANSI SQL。我猜测它将 where 子句中的 STATUS 与驱动表相关联。
When you use "oracle" syntax you'll see the expected behaviour.
当您使用“oracle”语法时，您会看到预期的行为。
SELECT *
FROM USER_TABLES TAB, USER_TRIGGERS TRG, USER_CONSTRAINTS CON
WHERE TRG.TABLE_NAME = TAB.TABLE_NAME
AND CON.TABLE_NAME = TAB.TABLE_NAME
AND STATUS = 'DISABLED'
回答by Sebastien Tardif
More confirmed bug about this here: http://oracledoug.com/serendipity/index.php?/archives/1555-Bug-Hunting.html
更多关于此的确认错误：http: //oracledoug.com/serendipity/index.php?/archives/1555-Bug-Hunting.html
Latest update is that it's fixed in 11.2.0.2
最新更新是在 11.2.0.2 中修复
回答by Jan Snelders
Well, if I try this on 11.2.0.2.0, I get the same issue. Regardless of the functionality, if you add in some left and right joins, this bug does not seem to be fixed at all!
好吧，如果我在 11.2.0.2.0 上尝试这个，我会遇到同样的问题。不管功能如何，如果添加一些左右连接，这个错误似乎根本没有修复！
SELECT *
FROM USER_TABLES TAB
LEFT JOIN USER_TRIGGERS TRG ON TRG.TABLE_NAME = TAB.TABLE_NAME
RIGHT JOIN USER_CONSTRAINTS CON ON CON.TABLE_NAME = TAB.TABLE_NAME
WHERE STATUS = 'DISABLED'
