suibianwanwank commented on code in PR #4375:
URL: https://github.com/apache/calcite/pull/4375#discussion_r2157962270
##########
core/src/main/java/org/apache/calcite/sql2rel/RelDecorrelator.java:
##########
@@ -760,11 +766,198 @@ protected RexNode removeCorrelationExpr(
RelNode newRel = relBuilder.build();
+ for (AggregateCall aggCall : rel.getAggCallList()) {
+ if (aggCall.getAggregation() instanceof SqlCountAggFunction) {
+ parentPropagatesNullValues = false;
+ break;
+ }
+ }
+
+ if (rel.getGroupType() == Aggregate.Group.SIMPLE
+ && rel.getGroupSet().isEmpty()
+ && !frame.corDefOutputs.isEmpty()
+ && !parentPropagatesNullValues) {
+ newRel = rewriteScalarAggregate(rel, newRel, outputMap, corDefOutputs);
+ }
+
// Aggregate does not change input ordering so corVars will be
// located at the same position as the input newProject.
return register(rel, newRel, outputMap, corDefOutputs);
}
+ /**
+ * Special case where the group by is static (i.e., aggregation functions
without group by).
+ *
+ * <p>Background:
+ * For the query:
+ * SELECT SUM(salary), COUNT(name) FROM A;
+ * When table A is empty, it returns [null, 0].
+ * But for
+ * SELECT SUM(salary), COUNT(name) FROM A group by id
+ * When table A is empty, it returns empty. This causes result mismatch.
+ * In the general decorrelation framework, we add corVar as an additional
groupKey to
+ * rewrite Correlate as JOIN. (See the code above for details) This means
that when the input
+ * is empty, the result produced using a JOIN is incorrect.
+ *
+ * <p>We refer to this situation as: `The well-known count bug`,
+ * More details about this issue: Optimization of Nested SQL Queries
Revisited
+ * (https://dl.acm.org/doi/pdf/10.1145/38714.38723)
+ *
+ * <p>To handle this situation, we using a LEFT JOIN to ensure that an
output is always produced.
+ *
+ * <p>Given the SQL:
+ * SELECT deptno FROM dept d
+ * WHERE 0 = (SELECT COUNT(*) FROM emp e WHERE d.deptno = e.deptno)
+ * Corresponding plan:
+ * LogicalProject(DEPTNO=[$0])
+ * LogicalCorrelate(correlation=[$cor0], joinType=[inner],
requiredColumns=[{0}])
+ * LogicalProject(DEPTNO=[$0])
+ * LogicalTableScan(table=[[scott, DEPT]])
+ * LogicalProject(cs=[true])
+ * LogicalFilter(condition=[=(0, $0)])
+ * LogicalAggregate(group=[{}], EXPR$0=[COUNT()])
Review Comment:
Done.
--
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]
