[
https://issues.apache.org/jira/browse/CALCITE-7034?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17953825#comment-17953825
]
weihua zhang commented on CALCITE-7034:
---------------------------------------
add test in java/org/apache/calcite/test/JdbcTest.java
{code:java}
@Test void test5421() {
CalciteAssert.that()
.with(CalciteAssert.Config.JDBC_SCOTT)
.query("select sum(sal) as s from emp e1 group by deptno having
count(*) > 2 and exists(select true from emp e2 where e1.deptno = e2.deptno)"
)
.returnsUnordered("S=10875.00", "S=9400.00", "S=8750.00");
}
{code}
get:
{code:java}
LogicalProject(S=[$1])
LogicalFilter(condition=[AND(>($2, 2), EXISTS({
LogicalFilter(condition=[=($cor0.DEPTNO, $7)])
JdbcTableScan(table=[[JDBC_SCOTT, EMP]])
}))], variablesSet=[[$cor0]])
LogicalAggregate(group=[{0}], S=[SUM($1)], agg#1=[COUNT()])
LogicalProject(DEPTNO=[$7], SAL=[$5])
JdbcTableScan(table=[[JDBC_SCOTT, EMP]])
Required columns {7} not subset of left columns {0, 1, 2}
java.lang.AssertionError: Required columns {7} not subset of left columns {0,
1, 2}
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.rewriteExists(SubQueryRemoveRule.java:487)
at
org.apache.calcite.rel.rules.SubQueryRemoveRule.apply(SubQueryRemoveRule.java:107)
at
org.apache.calcite.rel.rules.SubQueryRemoveRule.matchFilter(SubQueryRemoveRule.java:890)
at
org.apache.calcite.rel.rules.SubQueryRemoveRule.access$100(SubQueryRemoveRule.java:77)
at
org.apache.calcite.rel.rules.SubQueryRemoveRule$Config.lambda$static$2(SubQueryRemoveRule.java:1006)
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)
