Double/Random alias name in select clause on Oracle 11g does not throw invalid identifier exception

Question

Yesterday I stumbled upon some strange behavior on a customers Oracle 10g instance. Some procedure I wrote gave me an invalid identifier exception but was running fine on my Oracle 11g instances.

The relevant query was something like the following:

select b.b.v_col_b 
from tbla a
left join tblb b on a.pk_col_a = b.fk_a;

Pleas note the b.b.v_col_b part of the query. Changing from left join to inner join did finally raise a ORA-00904: "B"."B"."V_COL_B": invalid identifier exception, but:

1. Isn't this a syntax error?
2. Can somebody explain this behaviour?

A working demo can be found on sqlfiddle

Edit: Added the table definition:

create table tbla (
  pk_col_a int primary key, 
  v_col_a varchar2(50));

create table tblb (
  pk_col_b int primary key, 
  fk_a int, 
  v_col_b varchar2(50));

Edit2: As @LalitKumarB's mentioned this only seems to happen on Oracle 11g

Answer 1

Congratulations, you have found a bug :)

In that particular case you can write whatever you want when selecting any of the tblb columns:

select helloworld.b.v_col_b, mghjfghj.b.fk_a, asdasdas.b.pk_col_b
from tbla a
left join tblb b on a.pk_col_a = b.fk_a;

You can even do it with a right join:

select helloworld.b.v_col_b, mghjfghj.b.fk_a, asdasdas.b.pk_col_b
from tblb b
right join tbla a on a.pk_col_a = b.fk_a;

It won't work with the Oracle join syntax ( (+) notation ) though.

It is not an expected behaviour and, as Lalit noted in the comments, is fixed in 12C. You can file a bug request wit Oracle Support if you want. Maybe there is already a patch for it.