Why does Oracle SQL mysteriously resolve ambiguity in one joins and does not in others

Question

Hello.

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

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

Answer 1

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)