[
https://issues.apache.org/jira/browse/CALCITE-7034?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17953145#comment-17953145
]
Konstantin Orlov commented on CALCITE-7034:
-------------------------------------------
I think it's rather problem of
{{org.apache.calcite.rel.rules.SubQueryRemoveRule#matchJoin}}. In your case we
are following {{else}} branch of {{if(inputIntersectsLeftSide)}}, where we are
creating new join of rhs and correlated subquery. The problem is original
correlates reference joint row type. Given that said, I think the proper
solution is to shift indexes of correlates by the size of lhs. Something like
this:
{code:java}
RexSubQuery subQuery = e;
if (!variablesSet.isEmpty()) {
// Original correlates reference joint row type, but we are
about to create
// new join of original right side and correlated sub-query.
Therefore we have
// to adjust correlated variables int following way:
// 1) new correlation variable must reference row type of
right side only
// 2) field index must be shifted on the size of the left
side
CorrelationId id = Iterables.getOnlyElement(variablesSet);
subQuery = e.clone(e.rel.accept(new RelHomogeneousShuttle() {
private final int offset =
join.getLeft().getRowType().getFieldCount();
private final RexBuilder rexBuilder =
join.getRight().getCluster().getRexBuilder();
private final RexShuttle rexShuttle = new RexShuttle() {
@Override
public RexNode visitFieldAccess(RexFieldAccess
fieldAccess) {
if (!(fieldAccess.getReferenceExpr() instanceof
RexCorrelVariable)
|| !((RexCorrelVariable)
fieldAccess.getReferenceExpr()).id.equals(id)) {
return super.visitFieldAccess(fieldAccess);
}
RexNode updatedCorrelation =
rexBuilder.makeCorrel(join.getRight().getRowType(), id);
int oldIdx = fieldAccess.getField().getIndex();
return
rexBuilder.makeFieldAccess(updatedCorrelation, oldIdx - offset);
}
};
@Override
public RelNode visit(RelNode other) {
RelNode next = super.visit(other);
return next.accept(rexShuttle);
}
}));
}
final int nFields = join.getRowType().getFieldCount();
final RexNode target =
rule.apply(subQuery, variablesSet, logic, builder, 2,
nFields, 0);
final RexShuttle shuttle = new ReplaceSubQueryShuttle(e, target);
{code}
> 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)
