[
https://issues.apache.org/jira/browse/CALCITE-7034?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17953824#comment-17953824
]
weihua zhang commented on CALCITE-7034:
---------------------------------------
add test in java/org/apache/calcite/test/JdbcTest.java
{code:java}
@Test void test7034() {
CalciteAssert.that()
.with(CalciteAssert.Config.JDBC_SCOTT)
.query("SELECT e1.empno 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"
)
.returnsUnordered("");
}
{code}
get:
{code:java}
LogicalSort(sort0=[$0], sort1=[$1], dir0=[ASC], dir1=[ASC])
LogicalProject(EMPNO=[$0], DEPTNO=[$7])
LogicalJoin(condition=[AND(=($7, $8), IN(CAST($8):SMALLINT NOT NULL, {
LogicalProject(EMPNO=[$0])
LogicalFilter(condition=[>(CAST($cor0.DEPTNO0):DECIMAL(7, 2) NOT NULL, $6)])
JdbcTableScan(table=[[JDBC_SCOTT, EMP]])
}))], joinType=[inner])
JdbcTableScan(table=[[JDBC_SCOTT, EMP]])
JdbcTableScan(table=[[JDBC_SCOTT, DEPT]])
Required columns {8} not subset of left columns {0}
java.lang.AssertionError: Required columns {8} not subset of left columns {0}
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:145)
at org.apache.calcite.rel.core.Correlate.<init>(Correlate.java:109)
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:479)
at org.apache.calcite.tools.RelBuilder.join(RelBuilder.java:3224)
at
org.apache.calcite.rel.rules.SubQueryRemoveRule.rewriteIn(SubQueryRemoveRule.java:755)
at
org.apache.calcite.rel.rules.SubQueryRemoveRule.apply(SubQueryRemoveRule.java:105)
at
org.apache.calcite.rel.rules.SubQueryRemoveRule.matchJoin(SubQueryRemoveRule.java:968)
at
org.apache.calcite.rel.rules.SubQueryRemoveRule.access$000(SubQueryRemoveRule.java:77)
at
org.apache.calcite.rel.rules.SubQueryRemoveRule$Config.lambda$static$4(SubQueryRemoveRule.java:1014)
at
org.apache.calcite.rel.rules.SubQueryRemoveRule.onMatch(SubQueryRemoveRule.java:88)
at
org.apache.calcite.plan.AbstractRelOptPlanner.fireRule(AbstractRelOptPlanner.java:350)
at org.apache.calcite.plan.hep.HepPlanner.applyRule(HepPlanner.java:541)
at
org.apache.calcite.plan.hep.HepPlanner.applyRules(HepPlanner.java:419)
at
org.apache.calcite.plan.hep.HepPlanner.executeRuleCollection(HepPlanner.java:285)
at
org.apache.calcite.plan.hep.HepInstruction$RuleCollection$State.execute(HepInstruction.java:105)
at
org.apache.calcite.plan.hep.HepPlanner.lambda$executeProgram$0(HepPlanner.java:210)
at
com.google.common.collect.ImmutableList.forEach(ImmutableList.java:423)
at
org.apache.calcite.plan.hep.HepPlanner.executeProgram(HepPlanner.java:209)
at
org.apache.calcite.plan.hep.HepProgram$State.execute(HepProgram.java:118)
at
org.apache.calcite.plan.hep.HepPlanner.executeProgram(HepPlanner.java:204)
at
org.apache.calcite.plan.hep.HepPlanner.findBestExp(HepPlanner.java:190)
{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)
