<p>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:</p>

<pre><code>select PTNO,PTNM,CATCD
from PARTS 
left join CATEGORIES on (CATEGORIES.CATCD=PARTS.CATCD); 
</code></pre>

<p>and here it is after the fix:</p>

<pre><code>select PTNO,PTNM,PARTS.CATCD
from PARTS 
left join CATEGORIES on (CATEGORIES.CATCD=PARTS.CATCD); 
</code></pre>

<p>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?</p>

<p>Update:</p>

<p>Here's a revised example, where the ambiguity error is not thrown:</p>

<pre><code>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) ;
</code></pre>

<p>Anybody have a clue?</p>

[{'answer_id': 58896, 'author': 'hollystyles', 'author_id': 2083160, 'author_profile': 'https://Stackoverflow.com/users/2083160', 'pm_score': 0, 'selected': False, 'text': '<p>It is generally advised to be specific and fully qualify all column names anyway, as it saves the optimizer a little work. Certainly in SQL Server.</p>\n\n<p>From what I can gleen from the <a href="http://www.oracle.com/technology/index.html" rel="nofollow noreferrer">Oracle docs</a>, it seems it will only throw if you select the column name twice in the select list, or once in the select list and then again elsewhere like an order by clause.</p>\n\n<p>Perhaps you have uncovered an \'undocumented feature\' :)</p>\n'}, {'answer_id': 58902, 'author': 'SQLMenace', 'author_id': 740, 'author_profile': 'https://Stackoverflow.com/users/740', 'pm_score': 2, 'selected': False, 'text': "<p>Interesting in SQL server that throws an error (as it should)</p>\n\n<pre><code>select id\nfrom sysobjects s\nleft join syscolumns c on s.id = c.id\n</code></pre>\n\n<p>Server: Msg 209, Level 16, State 1, Line 1\nAmbiguous column name 'id'.</p>\n\n<pre><code>select id\nfrom sysobjects \nleft join syscolumns  on sysobjects.id = syscolumns.id\n</code></pre>\n\n<p>Server: Msg 209, Level 16, State 1, Line 1\nAmbiguous column name 'id'.</p>\n"}, {'answer_id': 58907, 'author': 'Pat', 'author_id': 238, 'author_profile': 'https://Stackoverflow.com/users/238', 'pm_score': 3, 'selected': False, 'text': "<blockquote>\n  <p>Here's the query (simplified version)  </p>\n</blockquote>\n\n<p>I think by simplifying the query you removed the real cause of the bug :-)</p>\n\n<p>What oracle version are you using? Oracle 10g ( 10.2.0.1.0 ) gives:  </p>\n\n<pre><code>create table parts (ptno number , ptnm number , catcd number);  \ncreate table CATEGORIES (catcd number);\n\nselect PTNO,PTNM,CATCD from PARTS  \nleft join CATEGORIES on (CATEGORIES.CATCD=PARTS.CATCD);\n</code></pre>\n\n<p>I get ORA-00918: column ambiguously defined</p>\n"}, {'answer_id': 58924, 'author': 'user2752', 'author_id': 2752, 'author_profile': 'https://Stackoverflow.com/users/2752', 'pm_score': 1, 'selected': False, 'text': '<p>I am using Oracle 9.2.0.8.0. and it does give the error "ORA-00918: column ambiguously defined".</p>\n'}, {'answer_id': 58929, 'author': 'Troels Arvin', 'author_id': 4462, 'author_profile': 'https://Stackoverflow.com/users/4462', 'pm_score': 0, 'selected': False, 'text': "<p>Like HollyStyles, I cannot find anything in the Oracle docs which can explain what you are seeing.</p>\n\n<p>PostgreSQL, DB2, MySQL and MSSQL all refuse to run the first query, as it's ambiguous.</p>\n"}, {'answer_id': 58957, 'author': 'Laith', 'author_id': 5961, 'author_profile': 'https://Stackoverflow.com/users/5961', 'pm_score': 2, 'selected': False, 'text': '<p>From my experience if you create a query like this the data result will pull CATCD from the right side of the join not the left when there is a field overlap like this. </p>\n\n<p>So since this join will have all records from PARTS with only some pull through from CATEGORIES you will have NULL in the CATCD field any time there is no data on the right side.</p>\n\n<p>By explicitly defining the column as from PARTS (ie left side) you will get a non null value assuming that the field has data in PARTS.</p>\n\n<p>Remember that with LEFT JOIN you are only guarantied data in fields from the left table, there may well be empty columns to the right.</p>\n'}, {'answer_id': 58998, 'author': 'Ovesh', 'author_id': 3751, 'author_profile': 'https://Stackoverflow.com/users/3751', 'pm_score': 0, 'selected': False, 'text': "<p>@Pat: I get the same error here for your query. My query is just a little bit more complicated than what I originally posted. I'm working on a reproducible simple example now. </p>\n"}, {'answer_id': 59733, 'author': 'Chris Ammerman', 'author_id': 2729, 'author_profile': 'https://Stackoverflow.com/users/2729', 'pm_score': 2, 'selected': True, 'text': '<p>I\'m afraid I can\'t tell you why you\'re not getting an exception, but I can postulate as to why it chose CATEGORIES\' version of the column over PARTS\' version.</p>\n\n<blockquote>\n  <p>As far as I understood, in the case of left joins, the "main" table in the query (PARTS) has precedence in ambiguity</p>\n</blockquote>\n\n<p>It\'s not clear whether by "main" you mean simply the left table in a left join, or the "driving" table, as you see the query conceptually...  But in either case, what you see as the "main" table in the query as you\'ve written it will not necessarily be the "main" table in the actual execution of that query.</p>\n\n<p>My guess is that Oracle is simply using the column from the first table it hits in executing the query.  And since most individual operations in SQL do not require one table to be hit before the other, the DBMS will decide at parse time which is the most efficient one to scan first.  Try getting an execution plan for the query.  I suspect it may reveal that it\'s hitting CATEGORIES first and then PARTS.</p>\n'}, {'answer_id': 64169, 'author': 'Dave Costa', 'author_id': 6568, 'author_profile': 'https://Stackoverflow.com/users/6568', 'pm_score': 2, 'selected': False, 'text': "<p>This may be a bug in the Oracle optimizer.  I can reproduce the same behavior on the query with 3 tables.  Intuitively it does seem that it should produce an error.  If I rewrite it in either of the following ways, it does generate an error:</p>\n\n<p>(1) Using old-style outer join</p>\n\n<pre><code>select ptno, catcd\nfrom parts, categories, sections\nwhere categories.catcd (+) = parts.catcd\n  and sections.seccd (+) = parts.seccd\n</code></pre>\n\n<p>(2) Explicitly isolating the two joins</p>\n\n<pre><code>select ptno, catcd\nfrom (\n  select ptno, seccd, catcd\n  from parts\n  left join categories on (categories.CATCD=parts.CATCD) \n)\nleft join sections on (sections.SECCD=parts.SECCD)\n</code></pre>\n\n<p>I used DBMS_XPLAN to get details on the execution of the query, which did show something interesting.  The plan is basically to outer join PARTS and CATEGORIES, project that result set, then outer join it to SECTIONS.  The interesting part is that in the projection of the first outer join, it is only including PTNO and SECCD -- it is NOT including the CATCD from either of the first two tables.  Therefore the final result is getting CATCD from the third table.</p>\n\n<p>But I don't know whether this is a cause or an effect.</p>\n"}, {'answer_id': 84785, 'author': 'Mike McAllister', 'author_id': 16247, 'author_profile': 'https://Stackoverflow.com/users/16247', 'pm_score': 0, 'selected': False, 'text': "<p>A bigger question you should be asking yourself is - why do I have a category code in the parts table that doesn't exist in the categories table?</p>\n"}, {'answer_id': 93855, 'author': 'Myto', 'author_id': 17827, 'author_profile': 'https://Stackoverflow.com/users/17827', 'pm_score': 1, 'selected': False, 'text': "<p>This is a known bug with some Oracle versions when using ANSI-style joins. The correct behavior would be to get an ORA-00918 error.</p>\n\n<p>It's always best to specify your table names anyway; that way your queries don't break when you happen to add a new column with a name that is also used in another table.</p>\n"}, {'answer_id': 7030915, 'author': 'steve godfrey', 'author_id': 711571, 'author_profile': 'https://Stackoverflow.com/users/711571', 'pm_score': 0, 'selected': False, 'text': "<p>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 can return the wrong column if an alias isn't used. </p>\n\n<p>As has been mentioned already, it is fixed in 10g, so if an alias isn't used, an error will be returned.</p>\n"}]
['https://Stackoverflow.com/questions/58831', 'https://Stackoverflow.com', 'https://Stackoverflow.com/users/3751/']