This is an automated email from the ASF dual-hosted git repository.
asolimando 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 585cbda527 [CALCITE-7330] AggregateCaseToFilterRule should not be
applied on aggregate functions that don't skip NULL inputs
585cbda527 is described below
commit 585cbda5270c3619960e3e63d3142ce0f3553c4c
Author: Alessandro Solimando <[email protected]>
AuthorDate: Thu Dec 11 18:12:32 2025 +0100
[CALCITE-7330] AggregateCaseToFilterRule should not be applied on aggregate
functions that don't skip NULL inputs
Added SqlAggFunction.skipsNullInputs() method to indicate whether an
aggregate
function skips NULL input values. AggregateCaseToFilterRule now checks this
method before applying the CASE-to-FILTER transformation, preventing
incorrect
optimization for aggregates where NULL inputs are semantically significant
This is not a breaking change: the method defaults to true (standard SQL
behavior
where aggregates skip NULLs), preserving existing behavior for all built-in
aggregates. Custom UDAFs that do not skip NULL inputs should override this
method to return false
---
.../rel/rules/AggregateCaseToFilterRule.java | 3 ++-
.../org/apache/calcite/sql/SqlAggFunction.java | 22 ++++++++++++++++
.../org/apache/calcite/test/RelOptRulesTest.java | 29 ++++++++++++++++++++++
.../org/apache/calcite/test/RelOptRulesTest.xml | 13 ++++++++++
4 files changed, 66 insertions(+), 1 deletion(-)
diff --git
a/core/src/main/java/org/apache/calcite/rel/rules/AggregateCaseToFilterRule.java
b/core/src/main/java/org/apache/calcite/rel/rules/AggregateCaseToFilterRule.java
index 5922d05032..7ac593adad 100644
---
a/core/src/main/java/org/apache/calcite/rel/rules/AggregateCaseToFilterRule.java
+++
b/core/src/main/java/org/apache/calcite/rel/rules/AggregateCaseToFilterRule.java
@@ -230,7 +230,8 @@ && isIntLiteral(arg2, BigDecimal.ZERO)) {
false, call.rexList, ImmutableList.of(), newProjects.size() - 1,
null,
RelCollations.EMPTY, dataType, call.getName());
} else if ((RexLiteral.isNullLiteral(arg2) // Case A1
- && call.getAggregation().allowsFilter())
+ && call.getAggregation().allowsFilter()
+ && call.getAggregation().skipsNullInputs())
|| (kind == SqlKind.SUM0 // Case A2
&& isIntLiteral(arg2, BigDecimal.ZERO))) {
newProjects.add(arg1);
diff --git a/core/src/main/java/org/apache/calcite/sql/SqlAggFunction.java
b/core/src/main/java/org/apache/calcite/sql/SqlAggFunction.java
index b7f7df233e..59cb7522e6 100644
--- a/core/src/main/java/org/apache/calcite/sql/SqlAggFunction.java
+++ b/core/src/main/java/org/apache/calcite/sql/SqlAggFunction.java
@@ -212,6 +212,28 @@ public boolean allowsNullTreatment() {
return false;
}
+ /** Returns whether this aggregate function skips NULL input values.
+ *
+ * <p>Standard SQL aggregate functions skip NULL input values:
+ * {@code SUM(x)}, {@code AVG(x)}, {@code MIN(x)}, {@code MAX(x)},
+ * {@code COUNT(x)}, etc. For example, {@code SUM(x)} only sums non-NULL
+ * values of x.
+ *
+ * <p>This property is only relevant for aggregate functions that accept
+ * value arguments. Functions like {@code COUNT(*)} that count rows rather
+ * than values are not affected by this property.
+ *
+ * <p>Custom user-defined aggregate functions may treat NULL values as
+ * semantically significant inputs. Such functions should override this
+ * method to return {@code false}.
+ *
+ * @return true if NULL input values are skipped (standard SQL behavior),
+ * false if NULL input values have semantic significance
+ */
+ public boolean skipsNullInputs() {
+ return true;
+ }
+
/**
* Gets rollup aggregation function.
*/
diff --git a/core/src/test/java/org/apache/calcite/test/RelOptRulesTest.java
b/core/src/test/java/org/apache/calcite/test/RelOptRulesTest.java
index 8d432256b4..ff3cb03c36 100644
--- a/core/src/test/java/org/apache/calcite/test/RelOptRulesTest.java
+++ b/core/src/test/java/org/apache/calcite/test/RelOptRulesTest.java
@@ -114,6 +114,7 @@
import org.apache.calcite.rex.RexLiteral;
import org.apache.calcite.rex.RexNode;
import org.apache.calcite.rex.RexUtil;
+import org.apache.calcite.sql.SqlAggFunction;
import org.apache.calcite.sql.SqlBasicFunction;
import org.apache.calcite.sql.SqlFunction;
import org.apache.calcite.sql.SqlFunctionCategory;
@@ -126,6 +127,7 @@
import org.apache.calcite.sql.type.OperandTypes;
import org.apache.calcite.sql.type.ReturnTypes;
import org.apache.calcite.sql.type.SqlTypeName;
+import org.apache.calcite.sql.util.SqlOperatorTables;
import org.apache.calcite.sql.validate.SqlConformanceEnum;
import org.apache.calcite.sql.validate.SqlMonotonicity;
import org.apache.calcite.sql2rel.RelDecorrelator;
@@ -140,6 +142,7 @@
import org.apache.calcite.util.DateString;
import org.apache.calcite.util.Holder;
import org.apache.calcite.util.ImmutableBitSet;
+import org.apache.calcite.util.Optionality;
import com.google.common.collect.ImmutableList;
import com.google.common.collect.ImmutableSet;
@@ -6186,6 +6189,32 @@ public boolean test(Project project) {
sql(sql).withRule(CoreRules.AGGREGATE_CASE_TO_FILTER).checkUnchanged();
}
+ /** Test case for
+ * <a
href="https://issues.apache.org/jira/browse/CALCITE-7330";>[CALCITE-7330]
+ * AggregateCaseToFilterRule should not be applied on aggregate functions
that
+ * don't skip NULL inputs</a>. */
+ @Test void testAggregateCaseToFilterWithCustomNullAwareUdaf() {
+ final SqlAggFunction nullAwareAgg =
+ new SqlAggFunction("NULL_AWARE_SUM", null, SqlKind.SUM,
ReturnTypes.ARG0_NULLABLE, null,
+ OperandTypes.NUMERIC, SqlFunctionCategory.NUMERIC, false, false,
+ Optionality.FORBIDDEN) {
+ @Override public boolean skipsNullInputs() {
+ return false; // NULL values are semantically relevant
+ }
+ };
+
+ final String sql = "select null_aware_sum(case when deptno > 10 then sal
else null end)\n"
+ + "from emp";
+
+ sql(sql)
+ .withFactory(t ->
+ t.withOperatorTable(opTab ->
+ SqlOperatorTables.chain(opTab,
+ SqlOperatorTables.of(ImmutableList.of(nullAwareAgg)))))
+ .withRule(CoreRules.AGGREGATE_CASE_TO_FILTER)
+ .checkUnchanged();
+ }
+
@Test void testPullAggregateThroughUnion() {
final String sql = "select deptno, job from"
+ " (select deptno, job from emp as e1"
diff --git
a/core/src/test/resources/org/apache/calcite/test/RelOptRulesTest.xml
b/core/src/test/resources/org/apache/calcite/test/RelOptRulesTest.xml
index 8ed236780f..e1db15d0ce 100644
--- a/core/src/test/resources/org/apache/calcite/test/RelOptRulesTest.xml
+++ b/core/src/test/resources/org/apache/calcite/test/RelOptRulesTest.xml
@@ -85,6 +85,19 @@ from emp]]>
LogicalAggregate(group=[{}], SUM_NO_MATCH=[SUM($0)], SUM_NO_MATCH2=[SUM($1)],
SUM_NO_MATCH3=[SUM($2)])
LogicalProject($f0=[CASE(=($7, -1), 1, 0)], $f1=[CASE(=($7, -1), 2, 0)],
$f2=[CASE(=($7, -1), 3, -1)])
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testAggregateCaseToFilterWithCustomNullAwareUdaf">
+ <Resource name="sql">
+ <![CDATA[select null_aware_sum(case when deptno > 10 then sal else null
end)
+from emp]]>
+ </Resource>
+ <Resource name="planBefore">
+ <![CDATA[
+LogicalAggregate(group=[{}], EXPR$0=[NULL_AWARE_SUM($0)])
+ LogicalProject($f0=[CASE(>($7, 10), $5, null:INTEGER)])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
]]>
</Resource>
</TestCase>
