Is there a nesting limit for correlated subqueries in some versions of Oracle?

Question

Here is the code that will help you understand my question:

create table con ( content_id number);
create table mat ( material_id number, content_id number, resolution number, file_location varchar2(50));
create table con_groups (content_group_id number, content_id number);

insert into con values (99);
insert into mat values (1, 99, 7, 'C:\foo.jpg');
insert into mat values (2, 99, 2, '\\server\xyz.mov');
insert into mat values (3, 99, 5, '\\server2\xyz.wav');
insert into con values (100);
insert into mat values (4, 100, 5, 'C:\bar.png');
insert into mat values (5, 100, 3, '\\server\xyz.mov');
insert into mat values (6, 100, 7, '\\server2\xyz.wav');

insert into con_groups values (10, 99);
insert into con_groups values (10, 100);

commit;

SELECT m.material_id,
       (SELECT file_location 
          FROM (SELECT file_location
                  FROM mat
                 WHERE mat.content_id = m.content_id
              ORDER BY resolution DESC) special_mats_for_this_content            
         WHERE rownum = 1) special_mat_file_location                                     
  FROM mat m
 WHERE m.material_id IN (select material_id 
                           from mat
                     inner join con on con.content_id = mat.content_id
                     inner join con_groups on con_groups.content_id = con.content_id
                          where con_groups.content_group_id = 10);

Please consider the number 10 at the end of the query to be a parameter. In other words this value is just hardcoded in this example; it would change depending on the input.

My question is: Why do I get the error

"M"."CONTENT_ID": invalid identifier 

for the nested, correlated subquery? Is there some sort of nesting limit? This subquery needs to be ran for every row in the resultset because the results will change based on the content_id, which can be different for each row. How can I accomplish this with Oracle?

Not that I'm trying to start a SQL Server vs Oracle discussion, but I come from a SQL Server background and I'd like to point out that the following, equivalent query runs fine on SQL Server:

create table con ( content_id int);
create table mat ( material_id int, content_id int, resolution int, file_location varchar(50));
create table con_groups (content_group_id int, content_id int);

insert into con values (99);
insert into mat values (1, 99, 7, 'C:\foo.jpg');
insert into mat values (2, 99, 2, '\\server\xyz.mov');
insert into mat values (3, 99, 5, '\\server2\xyz.wav');
insert into con values (100);
insert into mat values (4, 100, 5, 'C:\bar.png');
insert into mat values (5, 100, 3, '\\server\xyz.mov');
insert into mat values (6, 100, 7, '\\server2\xyz.wav');

insert into con_groups values (10, 99);
insert into con_groups values (10, 100);

SELECT m.material_id,
       (SELECT file_location 
          FROM (SELECT TOP 1 file_location
                  FROM mat
                 WHERE mat.content_id = m.content_id
              ORDER BY resolution DESC) special_mats_for_this_content            
               ) special_mat_file_location                                     
  FROM mat m
 WHERE m.material_id IN (select material_id 
                           from mat
                     inner join con on con.content_id = mat.content_id
                     inner join con_groups on con_groups.content_id = con.content_id
                          where con_groups.content_group_id = 10);

Can you please help me understand why I can do this in SQL Server but not Oracle 9i? If there is a nesting limit, how can I accomplish this in a single select query in Oracle without resorting to looping and/or temporary tables?

Answer 1

Recent versions of Oracle do not have a limit but most older versions of Oracle have a nesting limit of 1 level deep.

This works on all versions:

SELECT  (
        SELECT  *
        FROM    dual dn
        WHERE   dn.dummy = do.dummy
        )
FROM    dual do

This query works in 12c and 18c but does not work in 10g and 11g. (However, there is at least one version of 10g that allowed this query. And there is a patch to enable this behavior in 11g.)

SELECT  (
        SELECT  *
        FROM    (
                SELECT  *
                FROM    dual dn
                WHERE   dn.dummy = do.dummy
                )
        WHERE   rownum = 1
        )
FROM    dual do

If necessary you can workaround this limitation with window functions (which you can use in SQL Server too:)

SELECT  *
FROM    (
        SELECT  m.material_id, ROW_NUMBER() OVER (PARTITION BY content_id ORDER BY resolution DESC) AS rn
        FROM    mat m
        WHERE   m.material_id IN
                (
                SELECT  con.content_id
                FROM    con_groups
                JOIN    con
                ON      con.content_id = con_groups.content_id
                WHERE   con_groups.content_group_id = 10
                )
        )
WHERE   rn = 1

Answer 2

@Quassnoi This was the case in oracle 9. From Oracle 10 ...

From Oracle Database SQL Reference 10g Release 1 (10.1) Oracle performs a correlated subquery when a nested subquery references a column from a table referred to a parent statement any number of levels above the subquery

From Oracle9i SQL Reference Release 2 (9.2) Oracle performs a correlated subquery when the subquery references a column from a table referred to in the parent statement.

A subquery in the WHERE clause of a SELECT statement is also called a nested subquery. You can nest up to 255 levels of subqueries in the a nested subquery.

I don't think it works if you have something like select * from (select * from ( select * from ( ....))))
Just select * from TableName alias where colName = (select * from SomeTable where someCol = (select * from SomeTable x where x.id = alias.col))

Check out http://forums.oracle.com/forums/thread.jspa?threadID=378604

Answer 3

Quassnoi answered my question about nesting, and made a great call by suggesting window analytic functions. Here is the exact query that I need:

SELECT m.material_id, m.content_id,
              (SELECT max(file_location) keep (dense_rank first order by resolution desc)
                 FROM mat
                WHERE mat.content_id = m.content_id) special_mat_file_location
      FROM mat m
     WHERE m.material_id IN (select material_id
                               from mat
                         inner join con on con.content_id = mat.content_id
                         inner join con_groups on con_groups.content_id = con.content_id
                              where con_groups.content_group_id = 10);

Thanks!