Weihua Zhang created CALCITE-7587:
-------------------------------------
Summary: 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
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)
