ANSI Join – Rename of Alias Leads to a Different Result

I know that ANSI joins have some different strategy for query blocks compared with the Oracle join syntax, but I did not know until recently, that some extra care with checking the syntax should be taken.

Let’s start with a little anecdotic evidence – the query below (which I call a double outer join) is a valid syntax in 19.8

  select *
  from tab1 a
  left outer join tab2 b on a.id = b.fk_id(+);

Well this is probably tolerated due to Oracle internal interpretation and can IMO cause no harm – if you try to place the (+) on the other side you get an error.

But recently I was confronted with an example, where similar typo can produce a completely different query result.

I’ll illustrate it on a minimal example.

  create table tab1 as select 1 id from dual;   create table tab2 as select 1 fk_id from dual;   create table tab3 (fk_id number);
  create table tab4 as select 1 fk_id from dual;

The following query returns NULL, because the outer join to tab3 fails as the table is empty, so no match in tab4 is the consequence.

  select d.fk_id
  from tab1 a

  left outer join tab2 b on a.id = b.fk_id
  left outer join tab3 c on a.id = c.fk_id
  left outer join tab4 d on c.fk_id = d.fk_id

      FK_ID


  ----------

      (Null)

           1

Apparently the result of the join to tab2.fk_id now “helps” to make the join to the tab3.
I think it is a bug – rewriting the query in Oracle proprietary outer join you get ORA-00918: column ambiguously defined.

I would not share it as it is probably not a big problem, but I was somehow fascinating with this behaviour and decided to post it. For me it means that until this is fixed in some cases for the check of ambiguously defined columns in ANSI SQL is responsible the query writer...

Is my interpretation right?

KR,

Jaromir D.B.Nemec

--
http://www.freelists.org/webpage/oracle-l