Why does Oracle SQL mysteriously resolve ambiguity in one joins and does not in others
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开发者_StackOverflow社区 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
What would be the explanation of this? I miss it completely... The full test case can be found at http://pastebin.com/webf513w
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.
Testcase:
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)
精彩评论