[jira] [Commented] (CALCITE-7442) Getting Wrong index of Correlated variable inside Subquery after FilterJoinRule

["Yash (Jira)" <ji...@apache.org>]

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

Yash commented on CALCITE-7442:
-------------------------------

thank you for the review [~dongsl] and everyone for the review!

> Getting Wrong index of Correlated variable inside Subquery after FilterJoinRule
> -------------------------------------------------------------------------------
>
>                 Key: CALCITE-7442
>                 URL: https://issues.apache.org/jira/browse/CALCITE-7442
>             Project: Calcite
>          Issue Type: Bug
>          Components: core
>    Affects Versions: 1.41.0
>            Reporter: Yash
>            Assignee: Yash
>            Priority: Major
>              Labels: pull-request-available
>             Fix For: 1.42.0
>
>
> Scenario when we have a correlated subquery inside join condition and want to push that subquery to filter before decorrelation for optimization purpose. So When FILTER_INTO_JOIN rule is fired before FILTER_SUB_QUERY_TO_CORRELATE, we get incorrect index of RexFieldAccess inside correlate inside subquery.
>  
> there are below two issues to consider here when FILTER_SUB_QUERY_TO_CORRELATE fires after FILTER_INTO_JOIN. 
>  
> query:
> {code:java}
> select e.empno, d.dname, b.ename
>   from emp e
>   inner join dept d
>     on d.deptno = e.deptno
>   inner join bonus b
>     on e.ename = b.ename
>     and b.job in (
>       select b2.job
>       from bonus b2
>       where b2.ename = b.ename)
>   where e.sal > 1000 and d.dname = 'SALES'{code}
>  
> *Issue in version 1.41*
> *First issue* - after firing FILTER_INTO_JOIN, FILTER_SUB_QUERY_TO_CORRELATE and Decorrelation; we don't make correlate node even tho there is clearly a correlate variable is visible $cor0.ENAME
>  
> {code:java}
> LogicalProject(EMPNO=[$0], DNAME=[$9], ENAME=[$11])
>   LogicalJoin(condition=[=($1, $11)], joinType=[inner], variablesSet=[[$cor0]])
>     LogicalJoin(condition=[=($8, $7)], joinType=[inner])
>       LogicalFilter(condition=[>(CAST($5):DECIMAL(12, 2), 1000.00)])
>         LogicalTableScan(table=[[scott, EMP]])
>       LogicalFilter(condition=[=($1, 'SALES')])
>         LogicalTableScan(table=[[scott, DEPT]])
>     LogicalProject(ENAME=[$0], JOB=[$1], SAL=[$2], COMM=[$3])
>       LogicalJoin(condition=[=($1, $4)], joinType=[inner])
>         LogicalTableScan(table=[[scott, BONUS]])
>         LogicalProject(JOB=[$1])
>           LogicalFilter(condition=[=($0, $cor0.ENAME)])
>             LogicalTableScan(table=[[scott, BONUS]]){code}
>  
> this is happening because of below line in SubqueryRemoveRule#matchFilter
> variablesSet.retainAll(filterVariablesSet)
> when variablesSet is not empty but filterVariablesSet is empty it will make variablesSet empty and LogicalCorrelate node will not be created and $cor0.ENAME will stay as it is
> *Second Issue -* when we revert back variableSet change to calcite's *1.40* we get wrong index of correlated variable and fails with below error
> {code:java}
> Required columns {11} not subset of left columns {0, 1, 2, 3}
> java.lang.AssertionError: Required columns {11} not subset of left columns {0, 1, 2, 3}
>     at org.apache.calcite.util.Litmus.lambda$static$0(Litmus.java:31)
>     at org.apache.calcite.util.Litmus.check(Litmus.java:76)
>     at org.apache.calcite.rel.core.Correlate.isValid(Correlate.java:146)
>     at org.apache.calcite.rel.core.Correlate.<init>(Correlate.java:110)
>     at org.apache.calcite.rel.logical.LogicalCorrelate.<init>(LogicalCorrelate.java:73)
>     at org.apache.calcite.rel.logical.LogicalCorrelate.create(LogicalCorrelate.java:116)
>     at org.apache.calcite.rel.core.RelFactories$CorrelateFactoryImpl.createCorrelate(RelFactories.java:488)
>     at org.apache.calcite.tools.RelBuilder.join(RelBuilder.java:3354)
>     at org.apache.calcite.rel.rules.SubQueryRemoveRule.rewriteIn(SubQueryRemoveRule.java:847)
>     at org.apache.calcite.rel.rules.SubQueryRemoveRule.apply(SubQueryRemoveRule.java:110)
>     at org.apache.calcite.rel.rules.SubQueryRemoveRule.matchFilter(SubQueryRemoveRule.java:1018)
>     at org.apache.calcite.rel.rules.SubQueryRemoveRule.access$300(SubQueryRemoveRule.java:82)
>     at org.apache.calcite.rel.rules.SubQueryRemoveRule$Config.lambda$static$2(SubQueryRemoveRule.java:1318)
>     at org.apache.calcite.rel.rules.SubQueryRemoveRule.onMatch(SubQueryRemoveRule.java:93) {code}
>  



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