Ambiguity in left join (only oracle?)

Question

My boss found an error in the request that I created, and I do not understand the cause of the error, although the results of the request confirm its correctness. Here's the request (simplified version) before fixing:

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

and after correction:

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

The error was that zero values ​​were shown for the CATCD column, that is, the query results included the results from the CATEGORIES table 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 I understand it, in the case of left joins, the "main" table in the query (PARTS) takes precedence over ambiguity. Am I mistaken, or just do not think about this problem correctly?

Update:

Here is a revised example where an 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) ; 

Does anyone have a key?

Answer 1

I'm afraid I can’t tell you why you are not getting an exception, but I can tell why he chose the CATEGORIES version for the column above the PARTS version.

As I understand it, in the case of left joins, the "main" table in the query (PARTS) takes precedence over ambiguity

It is not clear whether the “main” table simply means the left table in the left connection or the “driving table”, as you see the query conceptually ... But in any case, what you see as the “main” table in the query, as you wrote it will not necessarily be the "main" table when this query is actually executed.

My guess is that Oracle just uses the column from the first table it gets into when executing the query. And since most of the individual operations in SQL do not require one table to come before another, the DBMS will decide during the parsing, which is most effective for scanning in the first place. Try to get a query execution plan. I suspect this may show that he first hits CATEGORIES and then PARTS.

Answer 2

Here's the request (simplified version)

I think that by simplifying the request, you have removed the real cause of the error :-)

What version of oracle 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 is indefinitely defined

Answer 3

An interesting SQL server that causes an error (as it should)

 select id from sysobjects s left join syscolumns c on s.id = c.id 

Server: Msg 209, Level 16, State 1, Line 1 The ambiguous column name is 'id'.

 select id from sysobjects left join syscolumns on sysobjects.id = syscolumns.id 

Server: Msg 209, Level 16, State 1, Line 1 The ambiguous column name is 'id'.

Answer 4

From my experience, if you create such a query, the data result will be pulled by the CATCD on the right side of the connection, not on the left, if there is a field overlap like this.

So, since this connection will have all the entries from PARTS, and only some of them will go through CATEGORIES, you will have NULL in the CATCD field whenever there is no data on the right side.

By explicitly defining a column as from PARTS (i.e., on the left side), you will get a nonzero value, assuming the field has data in PARTS.

Remember that with LEFT JOIN you only guaranteed the data in the fields from the left table, there may be empty columns to the right.

Answer 5

This may be a mistake in the Oracle optimizer. I can reproduce the same behavior in a query with three tables. It seems intuitively that this should lead to an error. If I rewrote it in one of the following ways, it causes an error:

(1) Using an old style outer join

 select ptno, catcd from parts, categories, sections where categories.catcd (+) = parts.catcd and sections.seccd (+) = parts.seccd 

(2) Explicit isolation of two compounds

 select ptno, catcd from ( select ptno, seccd, catcd from parts left join categories on (categories.CATCD=parts.CATCD) ) left join sections on (sections.SECCD=parts.SECCD) 

I used DBMS_XPLAN to get detailed information about executing a query that showed something interesting. The plan basically consists in external joining PARTS and CATEGORIES, a project that sets the result, then external joins it with SECTIONS. The interesting part is that in the projection of the first outer join, it includes only PTNO and SECCD - it does NOT include CATCD from either of the first two tables. Therefore, the end result is getting CATCD from the third table.

But I do not know if this is the cause or effect.

Answer 6

I am using Oracle 9.2.0.8.0. and this gives the error "ORA-00918: the column is ambiguously defined."

Answer 7

This is a known bug with some versions of Oracle when using ANSI connections. The correct behavior would be to get error ORA-00918.

It is always better to specify table names; this way your queries are not interrupted when you manage to add a new column with a name that is also used in another table.

Answer 8

It is generally recommended that you be specific and fully qualify all column names anyway, as this saves the optimizer. Of course in SQL Server.

From what I can get from Oracle docs , it seems that it will only drop if you select the column name twice in the select list, or once in the select list, and then again in another place, like an order by clause.

You may have discovered an "undocumented function" :)

Answer 9

Like HollyStyles, I cannot find anything in Oracle docs that can explain what you see.

PostgreSQL, DB2, MySQL, and MSSQL refuse to run the first query because it is ambiguous.

Answer 10

@Pat: I get the same error here for your request. My request is a bit more complicated than what I originally posted. I am currently working on a reproducible simple example.

Answer 11

The more important question you should ask yourself is: why do I have a category code in the partition table that does not exist in the category table?

Answer 12

This is a bug in Oracle 9i. If you join more than 2 tables using ANSI notation, it will not detect ambiguities in column names and may return an invalid column if it is not used.

As already mentioned, it is fixed in 10g, so if an alias is not used, an error will be returned.