[jira] [Commented] (CALCITE-5626) Query with EXISTS function gives Table ... not found error

[Runkang He (Jira)]

    [ 
https://issues.apache.org/jira/browse/CALCITE-5626?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17721967#comment-17721967
 ] 

Runkang He commented on CALCITE-5626:
-------------------------------------

I reproduce this issue with emp and dept as below:
{code:sql}
select
    sales.emp.empno
from
    sales.emp
where
    sales.emp.deptno in (
        select
            sales.dept.deptno
        from
            sales.dept
        where
            sales.dept.deptno = 10
    )
{code}

The root cause is that when we resolve the sub-query's identifier 
sales.dept.deptno, the scope child's namespace is UNVALIDATED, so we cannot 
find any table using namespace#getTable(see [calcite code line 
link|https://github.com/apache/calcite/blob/bf8e1142c8635823a45117261c836c95a3994668/core/src/main/java/org/apache/calcite/sql/validate/ListScope.java#L119]).
 And finally throws table not found exception.

> Query with EXISTS function gives Table ... not found error
> ----------------------------------------------------------
>
>                 Key: CALCITE-5626
>                 URL: https://issues.apache.org/jira/browse/CALCITE-5626
>             Project: Calcite
>          Issue Type: Bug
>          Components: core
>    Affects Versions: 1.34.0
>            Reporter: Roman Churganov
>            Priority: Blocker
>
> If query has a sub-query in EXIXST function and full column names used in  
> sub-query , like  
> {code:sql}
>   SELECT sch1.foo.c11 FROM sch1.foo 
>                           WHERE exists ( SELECT sch2.tab.c41 FROM sch2.tab 
> where sch2.tab.c41 = 1 )
> {code}
> it fails with exception:
> {noformat}
>        .....
>       at 
> org.apache.calcite.sql.validate.SqlValidatorImpl.validate(SqlValidatorImpl.java:787)
>       at org.apache.calcite.prepare.PlannerImpl.validate(PlannerImpl.java:226)
>       ... 73 more
> Caused by: org.apache.calcite.sql.validate.SqlValidatorException: Table 
> 'SCH2.TAB' not found
>       at 
> java.base/jdk.internal.reflect.NativeConstructorAccessorImpl.newInstance0(Native
>  Method)
>       at 
> java.base/jdk.internal.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:77)
>       at 
> java.base/jdk.internal.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
>       at 
> java.base/java.lang.reflect.Constructor.newInstanceWithCaller(Constructor.java:499)
>       at 
> java.base/java.lang.reflect.Constructor.newInstance(Constructor.java:480)
>       at 
> org.apache.calcite.runtime.Resources$ExInstWithCause.ex(Resources.java:505)
>       at org.apache.calcite.runtime.Resources$ExInst.ex(Resources.java:599)
>       ... 98 more
> {noformat}
> also need to have a different table in the outer query to reproduce



--
This message was sent by Atlassian Jira
(v8.20.10#820010)