Oracle nested correlated subquery limitation combined with LIMIT clause

[François Devémy]

Hello everyone,


I have a problem in Oracle:
I am using a correlated subquery with a limit clause.
But two things collade to create a bug:
1) limit clause is not supported in Oracle 11, so the limit is rendered 
(currently) as a nested select
2) there is a limitation to the depth of the correlation: according to the 
aofficial doc 
(at 
http://docs.oracle.com/cd/E11882_01/server.112/e41084/queries007.htm#SQLRF52357
)
"Oracle performs a correlated subquery when a nested subquery references a 
column from a table referred to a parent statement one level above the 
subquery"
So you can reference only one level deep. (See also 
http://stackoverflow.com/questions/1233910/is-there-a-nesting-limit-for-correlated-subqueries-in-oracle
)

So the limit clause adds one level of depth to the subquery, and now Oracle 
cannot recognize the correlation.

For an idea, this is the query I use:

final SelectQuery<Record1<Integer>> instanceQuery = 
using.selectCount().getQuery();
instanceQuery.addFrom(INST);
instanceQuery.addJoin(ACTIVITY, INST.DBID_.equal(ACTIVITY.INSTANCE_DBID_));
instanceQuery.addConditions(ACTIVITY.DBID_.equal(
      select(ACTIVITY_1.DBID_)
            .from(ACTIVITY_1)
            .where(INST.DBID_.equal(ACTIVITY_1.INSTANCE_DBID_))

            .orderBy(ACTIVITY_1.ACTIVITY_ID_.desc())
            .limit(1)));


This is what is generated:

select count(*)
from NOVA_PROCESS_INST INST
  join NOVA_ACTIVITY_INST ACTIVITY
    on INST.DBID_ = ACTIVITY.INSTANCE_DBID_
where (
  ACTIVITY.DBID_ = (
    select V0 DBID_
    from (
      select
        X.V0,
        rownum RN
      from (
             select ACTIVITY_1.DBID_ V0
             from NOVA_ACTIVITY_INST ACTIVITY_1
             where (
               INST.DBID_ = ACTIVITY_1.INSTANCE_DBID_
             )
             order by ACTIVITY_1.ACTIVITY_ID_ desc

           ) X
      where rownum <= (0 + 1)
    )
    where RN > 0
  )
);


          INST.DBID_ = ACTIVITY_1.INSTANCE_DBID_
          *
ERROR at line 16:
ORA-00904: "INST"."DBID_": invalid identifier


And this query works fine in MYSQL and PostgreSQL.
What can I do?

François

-- 
You received this message because you are subscribed to the Google Groups "jOOQ 
User Group" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to jooq-user+unsubscr...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.