I'm experiencing what seems to be a regression in 12.1.0.2. The following is a highly simlpified repro using a single common table. The actual query is much more complex and uses different tables.
Using the following schema:
create table tmp
(
val number(9) not null
);
and the following query:
select *
from tmp t0
outer apply
(
select *
from tmp t1
where t1.val = t0.val
) t2
left outer join tmp t3 on t0.val = t3.val
left outer join tmp t4 on t0.val = t4.val
This works perfectly in 12.1.0.1 however in 12.1.0.2 I get the following error:
ORA-00904: "T0"."VAL": invalid identifier
00904. 00000 - "%s: invalid identifier"
*Cause:
*Action:
Error at Line: 10 Column: 27
If there is only one left join, it works.If the left joins are moved before the outer apply (which should yield the same result) it works.
I've found some references to regressions in 12.1.0.2 with left joins e.g. bug#18430870 but this doesn't return a wrong result, it fails to even execute.
Unfortunately the query passes through Entity Framework and ODP.NET managed and I'm not willing to bypass those so I cannot directly influence the actual generated SQL.
Is this a known issue? Are there any workarounds available?