为什么 Oracle 不提出“ORA-00918:列有歧义定义"?对于这个查询?

2021-12-30 00:00:00 sql oracle11g oracle oracle10g ora-00918

我刚刚在 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 bug，有谁知道它是什么时候修复的，那么如果我们的数据库升级，哪个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

相关文章