[
https://issues.apache.org/jira/browse/CALCITE-7442?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
ASF GitHub Bot updated CALCITE-7442:
------------------------------------
Labels: pull-request-available (was: )
> 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
>
> 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)
