Contents of the submitted ticket:
Issue: What is the maximum level of depth a parent table can be accessed by a correlated subquery?SQL source:
WITH t (a, b) AS
(
SELECT 'M', 'N' FROM DUAL UNION ALL
SELECT 'R', 'Q' FROM DUAL UNION ALL
SELECT 'R', 'S' FROM DUAL
)
,tbl as
(
select
t.*,
(
-- level 1 nesting
select listagg (q, '') within group (order by null) from
(
-- level 2 nesting
select t.a q from dual union all
select t.b q from dual
)
) c
from t
)
select tbl.* from tbl
;
Failure:The SQL shown below fails on 11.2.0.4.0
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE 11.2.0.4.0 Production
TNS for IBM/AIX RISC System/6000: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production
Error starting at line : 2 in command -
WITH t (a, b) AS
(
SELECT 'M', 'N' FROM DUAL UNION ALL
SELECT 'R', 'Q' FROM DUAL UNION ALL
SELECT 'R', 'S' FROM DUAL
)
,tbl as
(
select
t.*,
(
select listagg (q, '') within group (order by null) from
(
select t.a q from dual union all
select t.b q from dual
)
) c
from t
)
select tbl.* from tbl
Error at Command Line : 2 Column : 1
Error report -
SQL Error: ORA-00904: "T"."A": invalid identifier
00904. 00000 - "%s: invalid identifier"
*Cause:
*Action:
Success:BANNER
--------------------------------------------------------------------------------
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
PL/SQL Release 12.1.0.2.0 - Production
CORE 12.1.0.2.0 Production
TNS for IBM/AIX RISC System/6000: Version 12.1.0.2.0 - Production
NLSRTL Version 12.1.0.2.0 - Production
A B C
- - -----
M N MN
R Q QR
R S RS
Documentation:
The documentation for 12.1 says the following about the correlated subquery:
http://docs.oracle.com/database/121/SQLRF/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."
It is the same statement even in 12.2 documentation.
http://docs.oracle.com/database/122/SQLRF/Using-Subqueries.htm#SQLRF52357 As per the documentation, we should be getting a similar error in 12.1.0.2.0 to what has been seen in 11.2.0.4.0 test. But the query returned results.
Since there is a clear discrepancy between the documentation and the actual feature, we would like to know where is the actual bug, is it in the documentation or in the feature?
Please also see the comments from Connor McDonald from asktom.oracle.com team which highlights the issue.
https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:9536139800346653431