[
https://issues.apache.org/jira/browse/CALCITE-7274?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=18036064#comment-18036064
]
weihua zhang commented on CALCITE-7274:
---------------------------------------
I think that performing field trimming after decorrelating subqueries is good
practice. Currently, in [Calcite's call
chain|https://github.com/apache/calcite/blob/c18c045aaba6151bdd4d7eda2ff0efae6b03cf56/core/src/main/java/org/apache/calcite/prepare/Prepare.java#L304],
trimming is done right after the SQL-to-Rel transformation. This forces us to
implement a lot of cumbersome adaptations to handle subquery scenarios—which I
think are completely unnecessary. Trimming fields after all subqueries have
been decorrelate would simplify the process.
> RexFieldAccess has wrong index when use trim unused fields
> ----------------------------------------------------------
>
> Key: CALCITE-7274
> URL: https://issues.apache.org/jira/browse/CALCITE-7274
> Project: Calcite
> Issue Type: Bug
> Reporter: weihua zhang
> Priority: Major
>
> {code:sql}
> !use scott
> SELECT empno
> FROM emp AS e
> LEFT JOIN dept AS d
> ON d.deptno = e.deptno
> AND (EXISTS (
> SELECT e2.deptno FROM emp AS e2
> WHERE e2.deptno = d.deptno
> GROUP BY e2.deptno
> HAVING SUM(e2.sal) > 1000000));
> {code}
> {code:java}
> ava.lang.AssertionError: Field ordinal 6 is invalid for type
> 'RecordType(TINYINT DEPTNO)'
> at
> org.apache.calcite.rex.RexBuilder.makeFieldAccess(RexBuilder.java:234)
> at org.apache.calcite.rex.RexUtil$10.visitFieldAccess(RexUtil.java:1960)
> at org.apache.calcite.rex.RexUtil$10.visitFieldAccess(RexUtil.java:1956)
> 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.rex.RexShuttle.apply(RexShuttle.java:292)
> at org.apache.calcite.rel.core.Filter.accept(Filter.java:129)
> at org.apache.calcite.rex.RexUtil.shiftFieldAccess(RexUtil.java:1956)
> at
> org.apache.calcite.rel.rules.SubQueryRemoveRule$1.visit(SubQueryRemoveRule.java:1033)
> at
> org.apache.calcite.rel.RelHomogeneousShuttle.visit(RelHomogeneousShuttle.java:63)
> at
> org.apache.calcite.rel.logical.LogicalFilter.accept(LogicalFilter.java:158)
> at
> org.apache.calcite.rel.RelShuttleImpl.visitChild(RelShuttleImpl.java:59)
> at
> org.apache.calcite.rel.RelShuttleImpl.visitChildren(RelShuttleImpl.java:73)
> at org.apache.calcite.rel.RelShuttleImpl.visit(RelShuttleImpl.java:151)
> at
> org.apache.calcite.rel.rules.SubQueryRemoveRule$1.visit(SubQueryRemoveRule.java:1034)
> at
> org.apache.calcite.rel.RelHomogeneousShuttle.visit(RelHomogeneousShuttle.java:67)
> at
> org.apache.calcite.rel.logical.LogicalProject.accept(LogicalProject.java:178)
> at
> org.apache.calcite.rel.RelShuttleImpl.visitChild(RelShuttleImpl.java:59)
> at
> org.apache.calcite.rel.RelShuttleImpl.visitChildren(RelShuttleImpl.java:73)
> at org.apache.calcite.rel.RelShuttleImpl.visit(RelShuttleImpl.java:151)
> at
> org.apache.calcite.rel.rules.SubQueryRemoveRule$1.visit(SubQueryRemoveRule.java:1034)
> at
> org.apache.calcite.rel.RelHomogeneousShuttle.visit(RelHomogeneousShuttle.java:43)
> at
> org.apache.calcite.rel.logical.LogicalAggregate.accept(LogicalAggregate.java:159)
> at
> org.apache.calcite.rel.RelShuttleImpl.visitChild(RelShuttleImpl.java:59)
> at
> org.apache.calcite.rel.RelShuttleImpl.visitChildren(RelShuttleImpl.java:73)
> at org.apache.calcite.rel.RelShuttleImpl.visit(RelShuttleImpl.java:151)
> at
> org.apache.calcite.rel.rules.SubQueryRemoveRule$1.visit(SubQueryRemoveRule.java:1034)
> at
> org.apache.calcite.rel.RelHomogeneousShuttle.visit(RelHomogeneousShuttle.java:63)
> at
> org.apache.calcite.rel.logical.LogicalFilter.accept(LogicalFilter.java:158)
> at
> org.apache.calcite.rel.rules.SubQueryRemoveRule.matchJoin(SubQueryRemoveRule.java:1030)
> at
> org.apache.calcite.rel.rules.SubQueryRemoveRule.access$000(SubQueryRemoveRule.java:80)
> at
> org.apache.calcite.rel.rules.SubQueryRemoveRule$Config.lambda$static$4(SubQueryRemoveRule.java:1095)
> at
> org.apache.calcite.rel.rules.SubQueryRemoveRule.onMatch(SubQueryRemoveRule.java:91)
> at
> org.apache.calcite.plan.AbstractRelOptPlanner.fireRule(AbstractRelOptPlanner.java:350)
> at org.apache.calcite.plan.hep.HepPlanner.applyRule(HepPlanner.java:527)
> 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:421)
> 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)
> at org.apache.calcite.tools.Programs.lambda$of$0(Programs.java:188)
> at
> org.apache.calcite.tools.Programs$SequenceProgram.run(Programs.java:381)
> at org.apache.calcite.prepare.Prepare.optimize(Prepare.java:178)
> at org.apache.calcite.prepare.Prepare.prepareSql(Prepare.java:320)
> at org.apache.calcite.prepare.Prepare.prepareSql(Prepare.java:221)
> at
> org.apache.calcite.prepare.CalcitePrepareImpl.prepare2_(CalcitePrepareImpl.java:673)
> at
> org.apache.calcite.prepare.CalcitePrepareImpl.prepare_(CalcitePrepareImpl.java:524)
> at
> org.apache.calcite.prepare.CalcitePrepareImpl.prepareSql(CalcitePrepareImpl.java:492)
> at
> org.apache.calcite.jdbc.CalciteConnectionImpl.parseQuery(CalciteConnectionImpl.java:246)
> at
> org.apache.calcite.jdbc.CalciteMetaImpl.prepareAndExecute(CalciteMetaImpl.java:654)
> at
> org.apache.calcite.avatica.AvaticaConnection.prepareAndExecuteInternal(AvaticaConnection.java:677)
> at
> org.apache.calcite.avatica.AvaticaStatement.executeInternal(AvaticaStatement.java:157)
> at
> org.apache.calcite.avatica.AvaticaStatement.executeQuery(AvaticaStatement.java:228)
> at net.hydromatic.quidem.Quidem.checkResult(Quidem.java:317)
> at net.hydromatic.quidem.Quidem.access$2600(Quidem.java:54)
> at
> net.hydromatic.quidem.Quidem$ContextImpl.checkResult(Quidem.java:1778)
> at
> net.hydromatic.quidem.Quidem$CheckResultCommand.execute(Quidem.java:985)
> at
> net.hydromatic.quidem.Quidem$CompositeCommand.execute(Quidem.java:1522)
> at net.hydromatic.quidem.Quidem.execute(Quidem.java:204)
> at org.apache.calcite.test.QuidemTest.checkRun(QuidemTest.java:307)
> at org.apache.calcite.test.QuidemTest.test(QuidemTest.java:484)
> at org.apache.calcite.test.CoreQuidemTest.main(CoreQuidemTest.java:54)
> {code}
> before `root = trimUnusedFields(root);`
> {code:java}
> LogicalProject(EMPNO=[$0])
> LogicalJoin(condition=[AND(=($8, $7), EXISTS({
> LogicalFilter(condition=[>($1, 1000000.00)])
> LogicalAggregate(group=[{0}], agg#0=[SUM($1)])
> LogicalProject(DEPTNO=[$7], SAL=[$5])
> LogicalFilter(condition=[=($7, $cor0.DEPTNO0)]) // <---
> $cor0.DEPTNO0 index is 8, this is right
> LogicalTableScan(table=[[scott, EMP]])
> }))], joinType=[left])
> LogicalTableScan(table=[[scott, EMP]])
> LogicalTableScan(table=[[scott, DEPT]])
> {code}
> after `root = trimUnusedFields(root);`
> {code:java}
> LogicalProject(EMPNO=[$0])
> LogicalJoin(condition=[AND(=($2, $1), EXISTS({
> LogicalFilter(condition=[>($1, 1000000.00)])
> LogicalAggregate(group=[{0}], agg#0=[SUM($1)])
> LogicalProject(DEPTNO=[$7], SAL=[$5])
> LogicalFilter(condition=[=($7, $cor0.DEPTNO0)]) // <---
> $cor0.DEPTNO0 index is 8, this is a bug, should be 2
> LogicalTableScan(table=[[scott, EMP]])
> }))], joinType=[left])
> LogicalProject(EMPNO=[$0], DEPTNO=[$7])
> LogicalTableScan(table=[[scott, EMP]])
> LogicalProject(DEPTNO=[$0])
> LogicalTableScan(table=[[scott, DEPT]])
> {code}
> if I change to
> {code:java}
> final SqlToRelConverter.Config config =
> SqlToRelConverter.config()
> .withTrimUnusedFields(false) // true ---> false
> .withExpand(THREAD_EXPAND.get())
>
> .withInSubQueryThreshold(castNonNull(THREAD_INSUBQUERY_THRESHOLD.get()))
> .withExplain(sqlQuery.getKind() == SqlKind.EXPLAIN);
> {code}
> will no problem
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
