为什么Oracle 10g不会抱怨列含糊不清? [英] Why Oracle 10g doesn't complain about column ambiguity?

问题描述

我正在使用Oracle 10g(XE 10.2.0.1.0)，并且发现了我不理解的行为:

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
;

问题是我认为Oracle应该在where子句中抱怨department_id的歧义，因为它在表employees和departments中都是一列.事实是在Oracle 10g中，事实并非如此，结果表明它将department_id解释为departments中的那个.但是，如果我注释掉第二个join语句(上面的第四行)，Oracle确实会按预期抱怨"ORA-00918:列的定义不明确".

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.

那么，有人可以帮助解释Oracle 10g中如何定义歧义吗?也许这是10g的错误?

So, can somebody help to explain how the ambiguity is defined in Oracle 10g? Or perhaps this is a bug in 10g?

BTW:这些表是在Oracle 10g中捆绑的默认HR模式中定义的.

BTW: The tables are defined in the default HR schema bundled in the Oracle 10g.

更新:刚刚找到一个相关的帖子: 为什么Oracle SQL神秘地解决了一个联接中的歧义，而不解决了其他联接中的模棱两可

Update: Just found a related post: Why does Oracle SQL mysteriously resolve ambiguity in one joins and does not in others

推荐答案

我相信Oracle选择不修复它是Oracle 10g中的错误.当我们将应用程序从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.

这篇关于为什么Oracle 10g不会抱怨列含糊不清?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！