[
https://issues.apache.org/jira/browse/CALCITE-7587?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=18086705#comment-18086705
]
Weihua Zhang commented on CALCITE-7587:
---------------------------------------
the two fixes are related but apply to different shuttles.
> RelDecorrelator fails on correlated scalar subquery with ROW_NUMBER window
> function due to RexOver nullability mismatch
> -----------------------------------------------------------------------------------------------------------------------
>
> Key: CALCITE-7587
> URL: https://issues.apache.org/jira/browse/CALCITE-7587
> Project: Calcite
> Issue Type: Bug
> Reporter: Weihua Zhang
> Assignee: Weihua Zhang
> Priority: Major
> Labels: pull-request-available
>
> A correlated scalar subquery in the `SELECT` list that contains a window
> function can fail during decorrelation with a row type mismatch assertion.
> {code:sql}
> SELECT e.ename,
> (SELECT ROW_NUMBER() OVER (PARTITION BY e.deptno ORDER BY e.empno, d.deptno)
> FROM dept d WHERE e.deptno = d.deptno) AS rn
> FROM emp e
> ORDER BY e.empno;
> {code}
> {code:java}
> java.lang.AssertionError: Cannot add expression of different type to set:
> set type is RecordType(SMALLINT NOT NULL EMPNO, VARCHAR(10) ENAME, TINYINT
> DEPTNO, BIGINT $f0) NOT NULL
> expression type is RecordType(SMALLINT NOT NULL EMPNO, VARCHAR(10) ENAME,
> TINYINT DEPTNO, BIGINT NOT NULL EXPR$0) NOT NULL
> set is
> rel#433:LogicalCorrelate.NONE.[](left=HepRelVertex#425,right=HepRelVertex#432,correlation=$cor2,joinType=left,requiredColumns={0,
> 2})
> expression is LogicalProject(EMPNO=[$0], ENAME=[$1], DEPTNO=[$2],
> EXPR$0=[ROW_NUMBER() OVER (PARTITION BY CASE(IS NULL($3), null:TINYINT,
> CAST($2):TINYINT) ORDER BY CASE(IS NULL($3), null:SMALLINT,
> CAST($0):SMALLINT), $3)])
> LogicalJoin(condition=[=($2, $3)], joinType=[left])
> LogicalProject(EMPNO=[$0], ENAME=[$1], DEPTNO=[$7])
> LogicalTableScan(table=[[scott, EMP]])
> LogicalTableScan(table=[[scott, DEPT]])
> Type mismatch:
> rowtype of original rel: RecordType(SMALLINT NOT NULL EMPNO, VARCHAR(10)
> ENAME, TINYINT DEPTNO, BIGINT $f0) NOT NULL
> rowtype of new rel: RecordType(SMALLINT NOT NULL EMPNO, VARCHAR(10) ENAME,
> TINYINT DEPTNO, BIGINT NOT NULL EXPR$0) NOT NULL
> Difference:
> $f0: BIGINT -> BIGINT NOT NULL
> at
> org.apache.calcite.plan.RelOptUtil.verifyTypeEquivalence(RelOptUtil.java:436)
> at
> org.apache.calcite.plan.hep.HepRuleCall.transformTo(HepRuleCall.java:58)
> at
> org.apache.calcite.plan.RelOptRuleCall.transformTo(RelOptRuleCall.java:273)
> at
> org.apache.calcite.plan.RelOptRuleCall.transformTo(RelOptRuleCall.java:288)
> at
> org.apache.calcite.sql2rel.RelDecorrelator$RemoveCorrelationForScalarProjectRule.onMatch(RelDecorrelator.java:3140)
> at
> org.apache.calcite.plan.AbstractRelOptPlanner.fireRule(AbstractRelOptPlanner.java:380)
> at org.apache.calcite.plan.hep.HepPlanner.applyRule(HepPlanner.java:682)
> at
> org.apache.calcite.plan.hep.HepPlanner.applyRules(HepPlanner.java:539)
> at
> org.apache.calcite.plan.hep.HepPlanner.executeRuleInstance(HepPlanner.java:355)
> at
> org.apache.calcite.plan.hep.HepInstruction$RuleInstance$State.execute(HepInstruction.java:178)
> at
> org.apache.calcite.plan.hep.HepPlanner.lambda$executeProgram$0(HepPlanner.java:323)
> at
> com.google.common.collect.ImmutableList.forEach(ImmutableList.java:421)
> at
> org.apache.calcite.plan.hep.HepPlanner.executeProgram(HepPlanner.java:322)
> at
> org.apache.calcite.plan.hep.HepProgram$State.execute(HepProgram.java:118)
> at
> org.apache.calcite.plan.hep.HepPlanner.executeProgram(HepPlanner.java:317)
> at
> org.apache.calcite.plan.hep.HepPlanner.findBestExp(HepPlanner.java:288)
> at
> org.apache.calcite.sql2rel.RelDecorrelator.applyHepProgram(RelDecorrelator.java:485)
> at
> org.apache.calcite.sql2rel.RelDecorrelator.removeCorrelationViaRule(RelDecorrelator.java:458)
> at
> org.apache.calcite.sql2rel.RelDecorrelator.decorrelateQuery(RelDecorrelator.java:263)
> at
> org.apache.calcite.sql2rel.RelDecorrelator.decorrelateQuery(RelDecorrelator.java:230)
> at
> org.apache.calcite.sql2rel.RelDecorrelator.decorrelateQuery(RelDecorrelator.java:225)
> at
> org.apache.calcite.tools.Programs$DecorrelateProgram.run(Programs.java:457)
> at
> org.apache.calcite.tools.Programs$SequenceProgram.run(Programs.java:407)
> at org.apache.calcite.prepare.Prepare.optimize(Prepare.java:178)
> at org.apache.calcite.prepare.Prepare.prepareSql(Prepare.java:325)
> at org.apache.calcite.prepare.Prepare.prepareSql(Prepare.java:221)
> at
> org.apache.calcite.prepare.CalcitePrepareImpl.prepare2_(CalcitePrepareImpl.java:675)
> at
> org.apache.calcite.prepare.CalcitePrepareImpl.prepare_(CalcitePrepareImpl.java:526)
> at
> org.apache.calcite.prepare.CalcitePrepareImpl.prepareSql(CalcitePrepareImpl.java:494)
> 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:678)
> 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:353)
> at org.apache.calcite.test.QuidemTest.test(QuidemTest.java:543)
> at org.apache.calcite.test.CoreQuidemTest.main(CoreQuidemTest.java:54)
> {code}
> right res:
> {code:java}
> ENAME, RN
> SMITH, 1
> ALLEN, 1
> WARD, 1
> JONES, 1
> MARTIN, 1
> BLAKE, 1
> CLARK, 1
> SCOTT, 1
> KING, 1
> TURNER, 1
> ADAMS, 1
> JAMES, 1
> FORD, 1
> MILLER, 1
> {code}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
