suibianwanwan created CALCITE-7034:
--------------------------------------
Summary: IllegalArgumentException when correlate subQuery in on
clause and use rightside column
Key: CALCITE-7034
URL: https://issues.apache.org/jira/browse/CALCITE-7034
Project: Calcite
Issue Type: Bug
Reporter: suibianwanwan
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)
