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

[weihua zhang (Jira)]

weihua zhang created CALCITE-7320:
-------------------------------------

             Summary: 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



{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}




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