Forum Stats

  • 3,852,824 Users
  • 2,264,140 Discussions


Oracle regression with outer apply and left joins

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

I'm experiencing what seems to be a regression in 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 however in I get the following error:

ORA-00904: "T0"."VAL": invalid identifier
00904. 00000 -  "%s: invalid identifier"
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 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


This discussion has been closed.