[jira] [Updated] (CALCITE-7320) Calcite throws a type mismatch error when executing SQL statements that use GROUPING SETS and correlated subqueries

[weihua zhang (Jira)]

     [ 
https://issues.apache.org/jira/browse/CALCITE-7320?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
 ]

weihua zhang updated CALCITE-7320:
----------------------------------
    Description: 
{code:sql}
!use scott
SELECT deptno,
       (SELECT SUM(cnt)
        FROM (
          SELECT COUNT(*) AS cnt
          FROM emp
          WHERE emp.deptno = dept.deptno
          GROUP BY GROUPING SETS ((deptno), ())
        ))
FROM dept;
{code}


{code:java}
Error while applying rule AggregateProjectMergeRule, args 
[rel#132:LogicalAggregate.NONE.[](input=RelSubset#131,group={0, 1},groups=[{0, 
1}, {1}],CNT=COUNT()), 
rel#130:LogicalProject.NONE.[](input=RelSubset#129,exprs=[$1, $1])]
        at org.apache.calcite.avatica.Helper.createException(Helper.java:56)
        at org.apache.calcite.avatica.Helper.createException(Helper.java:41)
        at 
org.apache.calcite.avatica.AvaticaStatement.executeInternal(AvaticaStatement.java:164)
        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)
Caused by: java.lang.RuntimeException: Error while applying rule 
AggregateProjectMergeRule, args 
[rel#132:LogicalAggregate.NONE.[](input=RelSubset#131,group={0, 1},groups=[{0, 
1}, {1}],CNT=COUNT()), 
rel#130:LogicalProject.NONE.[](input=RelSubset#129,exprs=[$1, $1])]
        at 
org.apache.calcite.plan.volcano.VolcanoRuleCall.onMatch(VolcanoRuleCall.java:250)
        at 
org.apache.calcite.plan.volcano.IterativeRuleDriver.drive(IterativeRuleDriver.java:61)
        at 
org.apache.calcite.plan.volcano.VolcanoPlanner.findBestExp(VolcanoPlanner.java:525)
        at 
org.apache.calcite.tools.Programs.lambda$standard$4(Programs.java:315)
        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)
        ... 10 more
Caused by: java.lang.RuntimeException: Error occurred while applying rule 
AggregateProjectMergeRule
        at 
org.apache.calcite.plan.volcano.VolcanoRuleCall.transformTo(VolcanoRuleCall.java:157)
        at 
org.apache.calcite.plan.RelOptRuleCall.transformTo(RelOptRuleCall.java:273)
        at 
org.apache.calcite.plan.RelOptRuleCall.transformTo(RelOptRuleCall.java:288)
        at 
org.apache.calcite.rel.rules.AggregateProjectMergeRule.onMatch(AggregateProjectMergeRule.java:87)
        at 
org.apache.calcite.plan.volcano.VolcanoRuleCall.onMatch(VolcanoRuleCall.java:223)
        ... 24 more
Caused by: java.lang.IllegalArgumentException: must be sorted: [{1}, {1}]
        at 
org.apache.calcite.rel.core.Aggregate$Group.induce(Aggregate.java:498)
        at 
org.apache.calcite.rel.core.Aggregate.getGroupType(Aggregate.java:485)
        at 
org.apache.calcite.rel.core.Aggregate.explainTerms(Aggregate.java:339)
        at 
org.apache.calcite.rel.AbstractRelNode.getDigestItems(AbstractRelNode.java:414)
        at 
org.apache.calcite.rel.AbstractRelNode.deepHashCode(AbstractRelNode.java:397)
        at 
org.apache.calcite.rel.AbstractRelNode$InnerRelDigest.hashCode(AbstractRelNode.java:448)
        at java.base/java.util.HashMap.hash(HashMap.java:338)
        at java.base/java.util.HashMap.getNode(HashMap.java:576)
        at java.base/java.util.HashMap.get(HashMap.java:564)
        at 
org.apache.calcite.plan.volcano.VolcanoPlanner.registerImpl(VolcanoPlanner.java:1307)
        at 
org.apache.calcite.plan.volcano.VolcanoPlanner.register(VolcanoPlanner.java:600)
        at 
org.apache.calcite.plan.volcano.VolcanoPlanner.ensureRegistered(VolcanoPlanner.java:615)
        at 
org.apache.calcite.plan.volcano.VolcanoPlanner.ensureRegistered(VolcanoPlanner.java:97)
        at 
org.apache.calcite.rel.AbstractRelNode.onRegister(AbstractRelNode.java:274)
        at 
org.apache.calcite.plan.volcano.VolcanoPlanner.registerImpl(VolcanoPlanner.java:1289)
        at 
org.apache.calcite.plan.volcano.VolcanoPlanner.register(VolcanoPlanner.java:600)
        at 
org.apache.calcite.plan.volcano.VolcanoPlanner.ensureRegistered(VolcanoPlanner.java:615)
        at 
org.apache.calcite.plan.volcano.VolcanoRuleCall.transformTo(VolcanoRuleCall.java:144)
        ... 28 more
{code}


  was:

{code:sql}
!use scott
SELECT *,
    (SELECT COUNT(*)
    FROM
        (
        SELECT empno, ename, job
        FROM emp
        WHERE emp.deptno = dept.deptno) AS sub
    GROUP BY GROUPING SETS ((deptno), ())
    ) AS num_dept_groups
FROM dept;
{code}


{code:java}
java.sql.SQLException: Error while executing SQL "SELECT *,
    (SELECT COUNT(*)
    FROM
        (
        SELECT empno, ename, job
        FROM emp
        WHERE emp.deptno = dept.deptno) AS sub
    GROUP BY GROUPING SETS ((deptno), ())
    ) AS num_dept_groups
FROM dept": Error while applying rule AggregateProjectMergeRule, args 
[rel#129:LogicalAggregate.NONE.[](input=RelSubset#128,group={0, 1},groups=[{0, 
1}, {1}],EXPR$0=COUNT()), 
rel#127:LogicalProject.NONE.[](input=RelSubset#126,exprs=[$2, $2])]
        at org.apache.calcite.avatica.Helper.createException(Helper.java:56)
        at org.apache.calcite.avatica.Helper.createException(Helper.java:41)
        at 
org.apache.calcite.avatica.AvaticaStatement.executeInternal(AvaticaStatement.java:164)
        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)
Caused by: java.lang.RuntimeException: Error while applying rule 
AggregateProjectMergeRule, args 
[rel#129:LogicalAggregate.NONE.[](input=RelSubset#128,group={0, 1},groups=[{0, 
1}, {1}],EXPR$0=COUNT()), 
rel#127:LogicalProject.NONE.[](input=RelSubset#126,exprs=[$2, $2])]
        at 
org.apache.calcite.plan.volcano.VolcanoRuleCall.onMatch(VolcanoRuleCall.java:250)
        at 
org.apache.calcite.plan.volcano.IterativeRuleDriver.drive(IterativeRuleDriver.java:61)
        at 
org.apache.calcite.plan.volcano.VolcanoPlanner.findBestExp(VolcanoPlanner.java:525)
        at 
org.apache.calcite.tools.Programs.lambda$standard$4(Programs.java:315)
        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)
        ... 10 more
Caused by: java.lang.RuntimeException: Error occurred while applying rule 
AggregateProjectMergeRule
        at 
org.apache.calcite.plan.volcano.VolcanoRuleCall.transformTo(VolcanoRuleCall.java:157)
        at 
org.apache.calcite.plan.RelOptRuleCall.transformTo(RelOptRuleCall.java:273)
        at 
org.apache.calcite.plan.RelOptRuleCall.transformTo(RelOptRuleCall.java:288)
        at 
org.apache.calcite.rel.rules.AggregateProjectMergeRule.onMatch(AggregateProjectMergeRule.java:87)
        at 
org.apache.calcite.plan.volcano.VolcanoRuleCall.onMatch(VolcanoRuleCall.java:223)
        ... 24 more
Caused by: java.lang.IllegalArgumentException: Type mismatch:
rel rowtype: RecordType(TINYINT NOT NULL DEPTNO0, TINYINT NOT NULL DEPTNO00, 
BIGINT NOT NULL EXPR$0) NOT NULL
equiv rowtype: RecordType(TINYINT $f0, TINYINT NOT NULL DEPTNO0, BIGINT NOT 
NULL EXPR$0) NOT NULL
Difference:
DEPTNO0: TINYINT NOT NULL -> TINYINT

        at 
org.apache.calcite.plan.volcano.VolcanoPlanner.register(VolcanoPlanner.java:594)
        at 
org.apache.calcite.plan.volcano.VolcanoPlanner.ensureRegistered(VolcanoPlanner.java:615)
        at 
org.apache.calcite.plan.volcano.VolcanoRuleCall.transformTo(VolcanoRuleCall.java:144)
        ... 28 more
{code}



> Calcite throws a type mismatch error when executing SQL statements that use 
> GROUPING SETS and correlated subqueries
> -------------------------------------------------------------------------------------------------------------------
>
>                 Key: CALCITE-7320
>                 URL: https://issues.apache.org/jira/browse/CALCITE-7320
>             Project: Calcite
>          Issue Type: Bug
>          Components: core
>            Reporter: weihua zhang
>            Priority: Major
>
> {code:sql}
> !use scott
> SELECT deptno,
>        (SELECT SUM(cnt)
>         FROM (
>           SELECT COUNT(*) AS cnt
>           FROM emp
>           WHERE emp.deptno = dept.deptno
>           GROUP BY GROUPING SETS ((deptno), ())
>         ))
> FROM dept;
> {code}
> {code:java}
> Error while applying rule AggregateProjectMergeRule, args 
> [rel#132:LogicalAggregate.NONE.[](input=RelSubset#131,group={0, 
> 1},groups=[{0, 1}, {1}],CNT=COUNT()), 
> rel#130:LogicalProject.NONE.[](input=RelSubset#129,exprs=[$1, $1])]
>       at org.apache.calcite.avatica.Helper.createException(Helper.java:56)
>       at org.apache.calcite.avatica.Helper.createException(Helper.java:41)
>       at 
> org.apache.calcite.avatica.AvaticaStatement.executeInternal(AvaticaStatement.java:164)
>       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)
> Caused by: java.lang.RuntimeException: Error while applying rule 
> AggregateProjectMergeRule, args 
> [rel#132:LogicalAggregate.NONE.[](input=RelSubset#131,group={0, 
> 1},groups=[{0, 1}, {1}],CNT=COUNT()), 
> rel#130:LogicalProject.NONE.[](input=RelSubset#129,exprs=[$1, $1])]
>       at 
> org.apache.calcite.plan.volcano.VolcanoRuleCall.onMatch(VolcanoRuleCall.java:250)
>       at 
> org.apache.calcite.plan.volcano.IterativeRuleDriver.drive(IterativeRuleDriver.java:61)
>       at 
> org.apache.calcite.plan.volcano.VolcanoPlanner.findBestExp(VolcanoPlanner.java:525)
>       at 
> org.apache.calcite.tools.Programs.lambda$standard$4(Programs.java:315)
>       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)
>       ... 10 more
> Caused by: java.lang.RuntimeException: Error occurred while applying rule 
> AggregateProjectMergeRule
>       at 
> org.apache.calcite.plan.volcano.VolcanoRuleCall.transformTo(VolcanoRuleCall.java:157)
>       at 
> org.apache.calcite.plan.RelOptRuleCall.transformTo(RelOptRuleCall.java:273)
>       at 
> org.apache.calcite.plan.RelOptRuleCall.transformTo(RelOptRuleCall.java:288)
>       at 
> org.apache.calcite.rel.rules.AggregateProjectMergeRule.onMatch(AggregateProjectMergeRule.java:87)
>       at 
> org.apache.calcite.plan.volcano.VolcanoRuleCall.onMatch(VolcanoRuleCall.java:223)
>       ... 24 more
> Caused by: java.lang.IllegalArgumentException: must be sorted: [{1}, {1}]
>       at 
> org.apache.calcite.rel.core.Aggregate$Group.induce(Aggregate.java:498)
>       at 
> org.apache.calcite.rel.core.Aggregate.getGroupType(Aggregate.java:485)
>       at 
> org.apache.calcite.rel.core.Aggregate.explainTerms(Aggregate.java:339)
>       at 
> org.apache.calcite.rel.AbstractRelNode.getDigestItems(AbstractRelNode.java:414)
>       at 
> org.apache.calcite.rel.AbstractRelNode.deepHashCode(AbstractRelNode.java:397)
>       at 
> org.apache.calcite.rel.AbstractRelNode$InnerRelDigest.hashCode(AbstractRelNode.java:448)
>       at java.base/java.util.HashMap.hash(HashMap.java:338)
>       at java.base/java.util.HashMap.getNode(HashMap.java:576)
>       at java.base/java.util.HashMap.get(HashMap.java:564)
>       at 
> org.apache.calcite.plan.volcano.VolcanoPlanner.registerImpl(VolcanoPlanner.java:1307)
>       at 
> org.apache.calcite.plan.volcano.VolcanoPlanner.register(VolcanoPlanner.java:600)
>       at 
> org.apache.calcite.plan.volcano.VolcanoPlanner.ensureRegistered(VolcanoPlanner.java:615)
>       at 
> org.apache.calcite.plan.volcano.VolcanoPlanner.ensureRegistered(VolcanoPlanner.java:97)
>       at 
> org.apache.calcite.rel.AbstractRelNode.onRegister(AbstractRelNode.java:274)
>       at 
> org.apache.calcite.plan.volcano.VolcanoPlanner.registerImpl(VolcanoPlanner.java:1289)
>       at 
> org.apache.calcite.plan.volcano.VolcanoPlanner.register(VolcanoPlanner.java:600)
>       at 
> org.apache.calcite.plan.volcano.VolcanoPlanner.ensureRegistered(VolcanoPlanner.java:615)
>       at 
> org.apache.calcite.plan.volcano.VolcanoRuleCall.transformTo(VolcanoRuleCall.java:144)
>       ... 28 more
> {code}



--
This message was sent by Atlassian Jira
(v8.20.10#820010)