Oracle Database Discussions

Hello,

We’ve came a cross a case that we need to perform a query that is nested 3 levels, as far as I know there should not be any problem with supplying an answer to such a query, but some Oracle servers on our site do not deliver to requested functionality, the following is a small test case that shows the problem.

SQL> drop table t1;

Table dropped.

SQL> create table t1(c1 number);

Table created.

SQL> drop table t2;

Table dropped.

SQL> create table t2(c1 number);

Table created.

SQL>
SQL> insert into t1 (c1) values (1);

1 row created.

SQL> insert into t1 (c1) values (2);

1 row created.

SQL> insert into t1 (c1) values (3);

1 row created.

SQL> insert into t2 (c1) values (1);

1 row created.

SQL> insert into t2 (c1) values (2);

1 row created.

SQL> insert into t2 (c1) values (3);

1 row created.

SQL>
SQL> commit;

Commit complete.

SQL>
SQL> select * from t1 where exists (select 1 from t2 where t2.c1 = t1.c1);

C1
----------
1
2
3

When nesting the inner query one more level (into 3rd level), it does not work:

SQL> select * from t1 where exists (select * from (select 1 from t2 where t2.c1 = t1.c1));
select * from t1 where exists (select * from (select 1 from t2 where t2.c1 = t1.c1))
*
ERROR at line 1:
ORA-00904: "T1"."C1": invalid identifier

Using explicit aliasing for t1 also do not work:

SQL>
SQL> select * from t1 x where exists (select * from (select 1 from t2 where t2.c1 = x.c1));
select * from t1 x where exists (select * from (select 1 from t2 where t2.c1 = x.c1))
*
ERROR at line 1:
ORA-00904: "X"."C1": invalid identifier

This behavior is not consistent, we have several servers on our site that the last two queries do return the 1,2,3 rows.

Looks like a possible bug.

Comments?

TIA,

Tal Olier (tal.olier@gmail.com)