iwanttobepowerful commented on code in PR #4375:
URL: https://github.com/apache/calcite/pull/4375#discussion_r2136880538
##########
core/src/main/java/org/apache/calcite/sql2rel/RelDecorrelator.java:
##########
@@ -760,6 +766,136 @@ protected RexNode removeCorrelationExpr(
RelNode newRel = relBuilder.build();
+ for (AggregateCall aggCall : rel.getAggCallList()) {
+ if (aggCall.getAggregation() instanceof SqlCountAggFunction) {
+ parentPropagatesNullValues = false;
+ break;
+ }
+ }
+
+ // Special case where the group by is static (i.e., aggregation functions
without group by).
+ //
+ // When unnesting an Aggregate, we add corVar as an extra groupKey to
rewrite Correlate as JOIN.
+ // 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.
+ //
+ // 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)
+ //
+ // To handle this situation, we ensure aggregated result output through
pre-join
+ // Given the following plan:
+ // LogicalCorrelate(correlation=[$cor0], joinType=[inner],
requiredColumns=[{0}])
+ // LogicalProject(DEPTNO=[$0])
+ // LogicalTableScan(table=[[scott, DEPT]])
+ // LogicalProject(EXPR$1=[IS NULL($1)])
+ // LogicalFilter(condition=[=(0, $0)])
+ // LogicalAggregate(group=[{}], EXPR$0=[COUNT()],
EXPR$1=[SUM($0)])
+ // LogicalFilter(condition=[=($cor0.DEPTNO, $7)])
+ // LogicalTableScan(table=[[scott, EMP]])
+ //
+ // The regular rewrite as:
+ // LogicalJoin(condition=[=($0, $2)], joinType=[inner])
+ // LogicalProject(DEPTNO=[$0])
+ // LogicalTableScan(table=[[scott, DEPT]])
+ // LogicalProject(EXPR$1=[IS NULL($2)], DEPTNO=[$0])
+ // LogicalFilter(condition=[=(0, $1)])
+ // LogicalAggregate(group=[{0}], EXPR$0=[COUNT()],
EXPR$1=[SUM($0)])
+ // LogicalProject(DEPTNO=[$7])
+ // LogicalFilter(condition=[IS NOT NULL($7)])
+ // LogicalTableScan(table=[[scott, EMP]])
+ // It will causes rows with `count=0` to be filtered out, and IS NULL($2)
will return null
+ // instead of true. Therefore, we use LEFT JOIN to ensure that
+ // correlation fields (extra group by key) always returns the aggregation
result.
+ //
+ // Rewrite Aggregate as:
+ // LogicalProject(DEPTNO=[$0], EXPR$0=[CASE(IS NOT NULL($2), $2, 0),
EXPR$1=[$(1)])
+ // LogicalJoin(condition=[IS NOT DISTINCT FROM($0, $1)],
joinType=[left])
+ // LogicalAggregate(group=[{0}])
+ // LogicalProject(DEPTNO=[$0])
+ // LogicalTableScan(table=[[scott, DEPT]])
+ // LogicalAggregate(group=[{0}], EXPR$0=[COUNT(), EXPR$1=[SUM($0)])
+ // LogicalProject(DEPTNO=[$7])
+ // LogicalFilter(condition=[IS NOT NULL($7)])
+ // LogicalTableScan(table=[[scott, EMP]])
+ //
+ // Here we perform an early join, preserving all possible CorVar sets from
the outer scope
+ // and their corresponding aggregation results. This ensures that for any
row from the left
+ // input of the Correlation, there is always an aggregation result
available for join output.
+ //
+ // Implementation based on: Improving Unnesting of Complex Queries
+ //
(https://15799.courses.cs.cmu.edu/spring2025/papers/11-unnesting/neumann-btw2025.pdf)
Review Comment:
```suggestion
//
(https://dl.gi.de/server/api/core/bitstreams/c1918e8c-6a87-4da2-930a-bfed289f2388/content)
```
--
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]
