为什么 Oracle 10g 不会抱怨列歧义？
我正在使用 Oracle 10g (XE 10.2.0.1.0)，并发现一个我不理解的行为：
select *
from employees manager
join employees worker on MANAGER.EMPLOYEE_ID = WORKER.MANAGER_ID
join departments on DEPARTMENTS.manager_id = 108
where
department_id = 100
;
问题是我认为 Oracle 应该抱怨 department_id 的歧义子句，因为它是表 employees 和 departments 中的一列。事实是在 Oracle 10g 中，情况并非如此，结果显示它将 department_id 解释为 departments 中的 ID。但是，如果我注释掉第二个连接语句（上面第 4 行），Oracle 确实会按预期抱怨“ORA-00918：列定义不明确”。
那么，有人可以帮忙解释一下 Oracle 10g 中如何定义歧义吗？或者这可能是 10g 中的一个错误？
顺便说一句：这些表是在 Oracle 10g 中捆绑的默认 HR 模式中定义的。
更新：刚刚找到一个相关帖子： 为什么Oracle SQL 神秘地解决了一个连接中的歧义，而其他连接中却没有解决
I'm using Oracle 10g (XE 10.2.0.1.0), and find a behavior that I don't understand:
select *
from employees manager
join employees worker on MANAGER.EMPLOYEE_ID = WORKER.MANAGER_ID
join departments on DEPARTMENTS.manager_id = 108
where
department_id = 100
;
The problem is I think Oracle should have complain about the ambiguity of department_id in the where clause, since it's a column in both the table employees and departments. The fact is in Oracle 10g, it doesn't, and the result shows that it interprets the department_id as the one in departments. However, if I comment out the second join statement (4th line above), Oracle does complain “ORA-00918: column ambiguously defined” as expected.
So, can somebody help to explain how the ambiguity is defined in Oracle 10g? Or perhaps this is a bug in 10g?
BTW: The tables are defined in the default HR schema bundled in the Oracle 10g.
Update: Just found a related post:
Why does Oracle SQL mysteriously resolve ambiguity in one joins and does not in others
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
我相信这是 Oracle 10g 中的一个错误，Oracle 选择不修复。当我们将应用程序从 10g 升级到 11gR2 时，我们发现一些查询在不明确的列名方面编写得“松散”，但在 Oracle 10g 中却可以工作。他们在 11gR2 中都停止工作了。我们联系了 Oracle，但他们几乎说对不明确的列名的宽容行为是 Oracle 10g 的正确行为，而严格的行为是 11g 的正确行为。
I believe it is a bug in Oracle 10g that Oracle chose not to fix. When we were upgrading our applications from 10g to 11gR2, we found a couple of queries that were written "loosely" in respect of ambiguous column names but worked in Oracle 10g. They all stopped working in 11gR2. We contacted Oracle but they pretty much said that the tolerant behavior toward ambiguous column names is a correct behavior for Oracle 10g and the stringent behavior is the correct behavior for 11g.
我认为是的，因为部门没有
别名。因此，所有没有被. departments。所以我也认为当你给
departments一个别名时，你应该再次得到 ORA-00918。但无法在这里测试...I think it is, because departments have no
alias. Therefore everything without being qualified by an<alias>.is first treated to be fromdepartments.So I also think when you give
departmentsan alias you should get the ORA-00918 again. Cannot test here though...