This is an automated email from the ASF dual-hosted git repository.
mihaibudiu pushed a commit to branch main
in repository https://gitbox.apache.org/repos/asf/calcite.git
The following commit(s) were added to refs/heads/main by this push:
new 64b903c42d [CALCITE-7930] Correlated sub-query with ROLLUP or GROUPING
SETS loses the () group when the sub-query input is empty
64b903c42d is described below
commit 64b903c42d0ae8d4ee4ce39cd05eb325b7cea061
Author: Mihai Budiu <[email protected]>
AuthorDate: Tue Nov 10 15:26:43 2026 -0800
[CALCITE-7930] Correlated sub-query with ROLLUP or GROUPING SETS loses the
() group when the sub-query input is empty
---
.../apache/calcite/sql2rel/RelDecorrelator.java | 48 +++++++++++-
.../sql2rel/TopDownGeneralDecorrelator.java | 11 ++-
core/src/test/resources/sql/sub-query.iq | 89 ++++++++++++++++++++++
3 files changed, 146 insertions(+), 2 deletions(-)
diff --git a/core/src/main/java/org/apache/calcite/sql2rel/RelDecorrelator.java
b/core/src/main/java/org/apache/calcite/sql2rel/RelDecorrelator.java
index 1f5272f29d..64c84bd9dc 100644
--- a/core/src/main/java/org/apache/calcite/sql2rel/RelDecorrelator.java
+++ b/core/src/main/java/org/apache/calcite/sql2rel/RelDecorrelator.java
@@ -636,6 +636,42 @@ protected RexNode removeCorrelationExpr(
return register(rel, newSort, frame.oldToNewOutputs, frame.corDefOutputs);
}
+ /** Returns whether an aggregate call gives the same result for no rows
+ * and for one row of NULL values. */
+ private static boolean isCallSameForNoRowsAndNullRow(AggregateCall aggCall) {
+ final SqlAggFunction function = aggCall.getAggregation();
+ switch (function.getKind()) {
+ case SUM:
+ case SUM0:
+ case MIN:
+ case MAX:
+ case AVG:
+ case ANY_VALUE:
+ case SINGLE_VALUE:
+ // Built-in functions that give NULL (SUM0 gives 0) for both
+ return true;
+ case OTHER_FUNCTION:
+ // A user-defined function that skips NULL inputs skips the row of
+ // NULL values, so it sees no rows in both cases
+ return !aggCall.getArgList().isEmpty() && function.skipsNullInputs();
+ default:
+ // Other built-in functions can give different results. COUNT(*)
+ // gives 0 for no rows and 1 for a row. ARRAY_AGG gives an empty array
+ // for no rows, and NULL or [NULL] for a row, depending on whether it
+ // keeps NULL values.
+ return false;
+ }
+ }
+
+ /** Returns whether an Aggregate gives the same result for no input rows
+ * and for one input row of NULL values. An Aggregate with GROUP BY keys
+ * does not: it gives no group for no rows, but one group for a row. */
+ static boolean isSameForNoRowsAndNullRow(Aggregate aggregate) {
+ return aggregate.getGroupSet().isEmpty()
+ && aggregate.getAggCallList().stream()
+ .allMatch(RelDecorrelator::isCallSameForNoRowsAndNullRow);
+ }
+
public @Nullable Frame decorrelateRel(LogicalAggregate rel, boolean
isCorVarDefined,
boolean parentPropagatesNullValues) {
return decorrelateRel((Aggregate) rel, isCorVarDefined,
parentPropagatesNullValues);
@@ -656,7 +692,17 @@ protected RexNode removeCorrelationExpr(
assert !cm.mapRefRelToCorRef.containsKey(rel);
final RelNode oldInput = rel.getInput();
- final Frame frame = getInvoke(oldInput, isCorVarDefined, rel,
parentPropagatesNullValues);
+ // This Aggregate is the parent of its input. The input can give no row
+ // in place of a row of NULL values only if this Aggregate gives the
+ // same result for both. Example, for an outer table t:
+ // SELECT COUNT(*) FROM (SELECT COUNT(*) FROM s
+ // WHERE s.k = t.k GROUP BY ROLLUP(s.a))
+ // For a row of t that matches no row of s, the () group of ROLLUP
+ // gives one row, so the result is 1, not 0.
+ final boolean aggregatePropagatesNullValues = parentPropagatesNullValues
+ && isSameForNoRowsAndNullRow(rel);
+ final Frame frame =
+ getInvoke(oldInput, isCorVarDefined, rel,
aggregatePropagatesNullValues);
if (frame == null) {
// If input has not been rewritten, do not rewrite this rel.
return null;
diff --git
a/core/src/main/java/org/apache/calcite/sql2rel/TopDownGeneralDecorrelator.java
b/core/src/main/java/org/apache/calcite/sql2rel/TopDownGeneralDecorrelator.java
index 445ebdbe9d..ddbc51ef0c 100644
---
a/core/src/main/java/org/apache/calcite/sql2rel/TopDownGeneralDecorrelator.java
+++
b/core/src/main/java/org/apache/calcite/sql2rel/TopDownGeneralDecorrelator.java
@@ -551,7 +551,16 @@ public RelNode unnestInternal(Project project, boolean
allowEmptyOutputFromRewri
}
public RelNode unnestInternal(Aggregate aggregate, boolean
allowEmptyOutputFromRewrite) {
- RelNode newInput = unnest(aggregate.getInput(),
allowEmptyOutputFromRewrite);
+ // The rewrite of the input can give an empty output in place of a row
+ // of NULL values only if this Aggregate gives the same result for both.
+ // Example, for an outer table t:
+ // SELECT COUNT(*) FROM (SELECT COUNT(*) FROM s
+ // WHERE s.k = t.k GROUP BY ROLLUP(s.a))
+ // For a row of t that matches no row of s, the () group of ROLLUP
+ // gives one row, so the result is 1, not 0.
+ final boolean allowEmptyInputFromRewrite = allowEmptyOutputFromRewrite
+ && RelDecorrelator.isSameForNoRowsAndNullRow(aggregate);
+ RelNode newInput = unnest(aggregate.getInput(),
allowEmptyInputFromRewrite);
UnnestedQuery inputUnnestedQuery =
requireNonNull(mapRelToUnnestedQuery.get(aggregate.getInput()));
builder.push(newInput);
diff --git a/core/src/test/resources/sql/sub-query.iq
b/core/src/test/resources/sql/sub-query.iq
index 97ddb5c65d..e4cb4efad8 100644
--- a/core/src/test/resources/sql/sub-query.iq
+++ b/core/src/test/resources/sql/sub-query.iq
@@ -11242,3 +11242,92 @@ order by e.ename;
(14 rows)
!ok
+
+# [CALCITE-7930] Correlated sub-query with ROLLUP or GROUPING SETS loses the
+# () group when the sub-query input is empty
+# The next 4 queries belong to CALCITE-7930: 2 queries, each run with
+# expand false and with expand true, because the top-down decorrelator
+# fails in both modes.
+# G is the number of groups for each department. DEPT 40 has no employees,
+# so only the () group remains, and G is 1.
+!set expand false
+
+# Query 1 of 4: ROLLUP(job), expand false.
+# Validated: PostgreSQL 14 gives the same rows.
+select deptno,
+ (select count(*) from
+ (select count(*) as n from emp where emp.deptno = dept.deptno
+ group by rollup(job)) as t) as g
+from dept;
++--------+---+
+| DEPTNO | G |
++--------+---+
+| 10 | 4 |
+| 20 | 4 |
+| 30 | 4 |
+| 40 | 1 |
++--------+---+
+(4 rows)
+
+!ok
+
+# Query 2 of 4: GROUPING SETS ((job), (mgr), ()), expand false.
+# Validated: PostgreSQL 14 gives the same rows.
+select deptno,
+ (select count(*) from
+ (select count(*) as n from emp where emp.deptno = dept.deptno
+ group by grouping sets ((job), (mgr), ())) as t) as g
+from dept;
++--------+---+
+| DEPTNO | G |
++--------+---+
+| 10 | 7 |
+| 20 | 8 |
+| 30 | 6 |
+| 40 | 1 |
++--------+---+
+(4 rows)
+
+!ok
+
+!set expand true
+
+# Query 3 of 4: ROLLUP(job), expand true.
+# Validated: PostgreSQL 14 gives the same rows.
+select deptno,
+ (select count(*) from
+ (select count(*) as n from emp where emp.deptno = dept.deptno
+ group by rollup(job)) as t) as g
+from dept;
++--------+---+
+| DEPTNO | G |
++--------+---+
+| 10 | 4 |
+| 20 | 4 |
+| 30 | 4 |
+| 40 | 1 |
++--------+---+
+(4 rows)
+
+!ok
+
+# Query 4 of 4: GROUPING SETS ((job), (mgr), ()), expand true.
+# Validated: PostgreSQL 14 gives the same rows.
+select deptno,
+ (select count(*) from
+ (select count(*) as n from emp where emp.deptno = dept.deptno
+ group by grouping sets ((job), (mgr), ())) as t) as g
+from dept;
++--------+---+
+| DEPTNO | G |
++--------+---+
+| 10 | 7 |
+| 20 | 8 |
+| 30 | 6 |
+| 40 | 1 |
++--------+---+
+(4 rows)
+
+!ok
+
+!set expand false
