Ambiguity in Left joins (oracle only?)

Question

My boss found a bug in a query I created, and I don't understand the reasoning behind the bug, although the query results prove he's correct. Here's the query (simplified version) before the fix:

select PTNO,PTNM,CATCD
from PARTS 
left join CATEGORIES on (CATEGORIES.CATCD=PARTS.CATCD); 

and here it is after the fix:

select PTNO,PTNM,PARTS.CATCD
from PARTS 
left join CATEGORIES on (CATEGORIES.CATCD=PARTS.CATCD); 

The bug was, that null values were being shown for column CATCD, i.e. the query results included results from table CATEGORIES instead of PARTS. Here's what I don't understand: if there was ambiguity in the original query, why didn't Oracle throw an error? As far as I understood, in the case of left joins, the "main" table in the query (PARTS) has precedence in ambiguity. Am I wrong, or just not thinking about this problem correctly?

Update:

Here's a revised example, where the ambiguity error is not thrown:

CREATE TABLE PARTS (PTNO NUMBER, CATCD NUMBER, SECCD NUMBER);

CREATE TABLE CATEGORIES(CATCD NUMBER);

CREATE TABLE SECTIONS(SECCD NUMBER, CATCD NUMBER);


select PTNO,CATCD 
from PARTS 
left join CATEGORIES on (CATEGORIES.CATCD=PARTS.CATCD) 
left join SECTIONS on (SECTIONS.SECCD=PARTS.SECCD) ;

Anybody have a clue?

Answer 1

Here's the query (simplified version)

I think by simplifying the query you removed the real cause of the bug :-)

What oracle version are you using? Oracle 10g ( 10.2.0.1.0 ) gives:

create table parts (ptno number , ptnm number , catcd number);  
create table CATEGORIES (catcd number);

select PTNO,PTNM,CATCD from PARTS  
left join CATEGORIES on (CATEGORIES.CATCD=PARTS.CATCD);

I get ORA-00918: column ambiguously defined