ORA-00918 expected but ...

From: Torsten Edler <Torsten.Edler_at_googlemail.com>
Date: 11 Apr 2007 07:29:20 -0700
Message-ID: <1176301760.292027.197200@q75g2000hsh.googlegroups.com>

I have a small example (which i have stripped down from a real world problem) which puzzles me. It consists of an outer join of three tables where i would expect to get an ORA-00918 "column ambiguously defined" but Oracle 10.2.0.3.0 on Windows accepts it perfectly well.

create table t1 (x number, y1 number);
create table t2 (x number, y2 number);
create table t3 (x number, y2 number);

insert into t1 values (1,1);

insert into t1 values (2,2);
insert into t3 values (2,2);

select distinct t1.x, y1, x, t2.x, a.x
from t1
left outer join t2 on t1.x=t2.x
left outer join t3 a on t1.x=a.x
;

the db chooses x automatically to mean t2.x instead of complaining about ambiguity whereas if t3 is left out of the join

select distinct t1.x, y1, x, t2.x
from t1
left outer join t2 on t1.x=t2.x
;

an ORA-00918 is received because x is ambiguous. Is this a bug or my lack of understanding? Received on Wed Apr 11 2007 - 09:29:20 CDT