Re: Weird SQL

> | So this sentence states that a subquery in WHERE clause
> | can see columns of any table referred to any of its parent
> | statements. However, this is not true for inline views and
> | (not completely sure, but looks like) scalar subqueries.
>
> This final part is not true as my example prove it (was in 10.2.0.1).
> And now, at least 100 work:
>
> SQL> select (
> 2 select dummy from (
> ...
> 100 select dummy from (
> 101 select dummy from dual b
> 102 where b.dummy = a.dummy
> 103 )))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))­))))))))))))))))))))))))) dummy
> 104 from dual a
> 105 /
> D
> -
> X
>
> 1 row selected.

Michel,

this is mostly strange, because on my 10.2.0.2 test instance your example gives:

ERROR at line 102:
ORA-00904: "A"."DUMMY": invalid identifier

and of course it doesn't work on previous releases (tested on 9.2.0.7 and 10.1.0.5). So it seems that what you see is a side effect of a bug fixed in first 10.2 patch set, or a new feature disabled/removed due to some problems with it, or even a regression introduced in 10.2.0.2 that re-establishes this restriction. However, since your test case contradicts the documented behavior, I tend to think it was a defect in initial 10.2 release fixed in 10.2.0.2. Only OSS can tell for sure, so you may want to open an SR with them (or I can do it if you don't want to bother.) For now, I wouldn't rely on this test case.

Regards,

     Vladimir M. Zakharychev
     N-Networks, makers of Dynamic PSP(tm)
     http://www.dynamicpsp.com