Forum Stats

  • 3,759,505 Users
  • 2,251,555 Discussions
  • 7,870,684 Comments

Discussions

Oracle 12.1.0.2 regression with outer apply and left joins

Greg Bachraty
Greg Bachraty Member Posts: 13 Red Ribbon
edited Dec 12, 2016 4:22AM in SQL & PL/SQL

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?

Best Answer

Answers

This discussion has been closed.