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:

Isn't this a syntax error?
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

It also selects: select k.b.v_col_b from tbla a left join tblb b on a.pk_col_a = b.fk_a; Weird — Giorgi Nakeuri
@Jajob Are you sure the query works fine on 12c? Because both the queries certainly throws Invalid identifier on my 12c instance. Exact version is 12.1.0.1. — Lalit Kumar B
I see the same behaviour on 10.2.0.5 and 11.2.0.3; the outer join 'works', the inner join gets the error. What patch level do you see it fail on in 10g? Or does your real query behave even more strangely than the demo? — Alex Poole

Mihail Mihail · Accepted Answer · 2015-08-27T09:33:20

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.

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

2 Answers