iwanttobepowerful commented on code in PR #4670:
URL: https://github.com/apache/calcite/pull/4670#discussion_r2600838777
##########
core/src/test/resources/sql/sub-query.iq:
##########
@@ -5505,4 +5505,41 @@ WHERE EXISTS
(2 rows)
!ok
+
+# [CALCITE-7297] The result is incorrect when the GROUP BY key in a subquery
is a RexFieldAccess
+SELECT *,
+ (SELECT COUNT(*)
+ FROM
+ (
+ SELECT empno, ename, job
+ FROM emp
+ WHERE emp.deptno = dept.deptno) AS sub
+ GROUP BY deptno) AS num_dept_groups
+FROM dept;
++--------+------------+----------+-----------------+
Review Comment:
This SQL statement throws an exception regardless of whether my
modifications are applied or not—we should probably log a separate Jira ticket
for this issue.
```
!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;
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
!error
```
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
