[
https://issues.apache.org/jira/browse/CALCITE-5517?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17694700#comment-17694700
]
James Starr commented on CALCITE-5517:
--------------------------------------
How is this a nested correlated sub-query?
> Nested (correlated) subqueries fail at validation | No SELECT scope for inner
> subquery
> --------------------------------------------------------------------------------------
>
> Key: CALCITE-5517
> URL: https://issues.apache.org/jira/browse/CALCITE-5517
> Project: Calcite
> Issue Type: Bug
> Affects Versions: 1.28.0, 1.33.0
> Reporter: Zine eddine Zidane
> Priority: Blocker
>
> Testing with TPC-H Query 2 for reference:
> {code:sql}
> SELECT FIRST 100 s_acctbal, s_name, n_name, p_partkey, p_mfgr, s_address,
> s_phone, s_comment
> FROM part, supplier, partsupp, nation, region
> WHERE p_partkey = ps_partkey
> AND s_suppkey = ps_suppkey
> AND p_size = 15
> AND p_type LIKE '%BRASS'
> AND s_nationkey = n_nationkey
> AND n_regionkey = r_regionkey
> AND r_name = 'EUROPE'
> AND ps_supplycost = (
> SELECT MIN(ps_supplycost)
> FROM partsupp, supplier, nation, region
> WHERE p_partkey = ps_partkey
> AND s_suppkey = ps_suppkey
> AND s_nationkey = n_nationkey
> AND n_regionkey = r_regionkey
> AND r_name = 'EUROPE')
> ORDER BY s_acctbal DESC, n_name, s_name, p_partkey {code}
> Validation fails with error:
> {code:java}
> java.lang.NullPointerException: no SELECT scope for SELECT
> MIN(PS.PS_SUPPLYCOST)
> FROM PARTSUPP AS PS,
> SUPPLIER AS S,
> NATION AS N,
> REGION AS R
> WHERE P.P_PARTKEY = PS.PS_PARTKEY AND S.S_SUPPKEY = PS.PS_SUPPKEY AND
> S.S_NATIONKEY = N.N_NATIONKEY AND N.N_REGIONKEY = R.R_REGIONKEY AND R.R_NAME
> = 'EUROPE' {code}
>
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
