为什么Oracle SQL神秘地解决一个联接中的歧义而不解决其他联接中的歧义 [英] Why does Oracle SQL mysteriously resolve ambiguity in one joins and does not in others

问题描述

我是Oracle 10g用户.我不得不编写一些SQL查询，并发现了一个神秘的行为(如我所见).假设我们有一个表，该表能够以某种简单的两级树结构进行连接.下一个查询给我模棱两可的错误"，这是预期的:

I'm an Oracle 10g user. I had to write some SQL queries, and spotted a mysterious (as I see it) behaviour. Let's pretend we have a table, which is able to join itself in some kind of simple two-level tree structure. The next query gives me "ambiguity error", which is expected:

select title
  from table1
    left join table1 on condition

但是如果我要向联接中添加一个表，歧义问题将简单地消失:

BUT if I would add one more table to the join, the ambiguity problem will simply go away:

select title
  from table1
    join table2 on other_condition
    left join table1 on condition

对此有何解释?我完全想念它...完整的测试用例可以在 http://pastebin.com/webf513w 中找到

What would be the explanation of this? I miss it completely... The full test case can be found at http://pastebin.com/webf513w

推荐答案

对于第三个查询，Oracle 10g从第二个TestTable1(别名TestTable1_2)返回field3.这似乎是一个错误，似乎已在11g中修复.

For the third query, Oracle 10g returns field3 from the second TestTable1 (alias TestTable1_2). This appears to be a bug, which seems to have been fixed in 11g.

测试用例:

INSERT INTO TestTable1 VALUES (1,2,3,NULL);
INSERT INTO TestTable1 VALUES (2,5,6,1);
INSERT INTO TestTable2 VALUES (5,6,7);
INSERT INTO TestTable2 VALUES (2,20,30);

SELECT field3
FROM TestTable1
join TestTable2 ON TestTable1.field1 = TestTable2.field1
left join TestTable1 TestTable1_2 ON TestTable1.self_ref = TestTable1_2.id;

FIELD3
======
3
(null)

SELECT TestTable1.field3, TestTable2.field3, TestTable1_2.field3
FROM TestTable1
join TestTable2 ON TestTable1.field1 = TestTable2.field1
left join TestTable1 TestTable1_2 ON TestTable1.self_ref = TestTable1_2.id;

FIELD3 FIELD3_1 FIELD3_2
====== ======== ========
6      7        3
3      30       (null)

这篇关于为什么Oracle SQL神秘地解决一个联接中的歧义而不解决其他联接中的歧义的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！