[
https://issues.apache.org/jira/browse/CALCITE-7034?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
suibianwanwan updated CALCITE-7034:
-----------------------------------
Summary: IllegalArgumentException when correlate subQuery in on clause and
use rightside columns (was: IllegalArgumentException when correlate subQuery
in on clause and use rightside column)
> IllegalArgumentException when correlate subQuery in on clause and use
> rightside columns
> ---------------------------------------------------------------------------------------
>
> Key: CALCITE-7034
> URL: https://issues.apache.org/jira/browse/CALCITE-7034
> Project: Calcite
> Issue Type: Bug
> Reporter: suibianwanwan
> Priority: Major
>
> Test in sub-query.iq:
> {code:java}
> !use scott
> SELECT e1.*
> FROM emp e1
> JOIN dept d
> ON e1.deptno = d.deptno
> AND d.deptno IN (
> SELECT e3.empno
> FROM emp e3
> WHERE d.deptno > e3.comm
> )
> ORDER BY e1.empno, e1.deptno;{code}
> Throw:
> {code:java}
> > Caused by: java.lang.IllegalArgumentException: field ordinal [8] out of
> > range; input fields are: [DEPTNO]
> > at org.apache.calcite.tools.RelBuilder.field(RelBuilder.java:588)
> > at org.apache.calcite.tools.RelBuilder.field(RelBuilder.java:563)
> > at
> > org.apache.calcite.sql2rel.CorrelateProjectExtractor$3.visitFieldAccess(CorrelateProjectExtractor.java:327)
> > at
> > org.apache.calcite.sql2rel.CorrelateProjectExtractor$3.visitFieldAccess(CorrelateProjectExtractor.java:324)
> > at org.apache.calcite.rex.RexFieldAccess.accept(RexFieldAccess.java:103)
> > at org.apache.calcite.rex.RexShuttle.visitList(RexShuttle.java:167)
> > at org.apache.calcite.rex.RexShuttle.visitCall(RexShuttle.java:119)
> > at org.apache.calcite.rex.RexShuttle.visitCall(RexShuttle.java:38)
> > at org.apache.calcite.rex.RexCall.accept(RexCall.java:208)
> > at
> > org.apache.calcite.sql2rel.CorrelateProjectExtractor.replaceCorrelationsWithInputRef(CorrelateProjectExtractor.java:324){code}
>
> I believe the error occurs in JOIN_SUB_QUERY_TO_CORRELATE, where the query's
> subPlan will be applied by this rule:
> {code:java}
> LogicalJoin(condition=[AND(=($7, $8), IN(CAST($8):SMALLINT NOT NULL, {
> LogicalProject(EMPNO=[$0])
> LogicalFilter(condition=[>(CAST($cor0.DEPTNO0):DECIMAL(7, 2), $6)])
> LogicalTableScan(table=[[scott, EMP]])
> }))], joinType=[left])
> LogicalTableScan(table=[[scott, EMP]])
> LogicalProject(DEPTNO=[$0])
> LogicalTableScan(table=[[scott, DEPT]]) {code}
> This rule attempts to pre-filter the side where corVar exists in the
> condition through Correlate, i.e., rewrite it as:
> {code:java}
> LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4],
> SAL=[$5], COMM=[$6], DEPTNO=[$7], DEPTNO0=[$8])
> LogicalJoin(condition=[=($7, $8)], joinType=[inner])
> LogicalTableScan(table=[[scott, EMP]])
> LogicalFilter(condition=[=(CAST($8):SMALLINT NOT NULL, $9)])
> LogicalCorrelate(correlation=[$cor0], joinType=[inner],
> requiredColumns=[{8}])
> LogicalProject(DEPTNO=[$0])
> LogicalTableScan(table=[[scott, DEPT]])
> LogicalProject(EMPNO=[$0])
> LogicalFilter(condition=[>(CAST($cor0.DEPTNO0):DECIMAL(7, 2) NOT
> NULL, $6)])
> LogicalTableScan(table=[[scott, EMP]]) {code}
> Note that _$cor0.DEPTNO0_ here originates from the subquery in the original
> plan. However, its scope was narrowed by
> {code:java}
> LogicalJoin(condition=[AND(=($7, $8)...) {code}
> to just its right side:
> {code:java}
> LogicalProject(DEPTNO=[$0])
> LogicalTableScan(table=[[scott, DEPT]]) {code}
> However, its index did not shift and remained at 8. This caused an error in
> the plan.
>
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
