This is an automated email from the ASF dual-hosted git repository.
xiong 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 254f2bac7b [CALCITE-6834] In query that applies COALESCE to nullable
SUM,EnumerableProjectToCalcRule throws AssertionError
254f2bac7b is described below
commit 254f2bac7b25643667f01aab9e14b0f32dc4a8bd
Author: Xiong Duan <[email protected]>
AuthorDate: Fri Feb 28 18:33:47 2025 +0800
[CALCITE-6834] In query that applies COALESCE to nullable
SUM,EnumerableProjectToCalcRule throws AssertionError
---
.../rel/rules/ProjectAggregateMergeRule.java | 3 +-
.../org/apache/calcite/test/RelOptRulesTest.java | 11 +++++
.../org/apache/calcite/test/RelOptRulesTest.xml | 41 ++++++++++++++----
core/src/test/resources/sql/agg.iq | 48 ++++++++++++++++++++++
4 files changed, 94 insertions(+), 9 deletions(-)
diff --git
a/core/src/main/java/org/apache/calcite/rel/rules/ProjectAggregateMergeRule.java
b/core/src/main/java/org/apache/calcite/rel/rules/ProjectAggregateMergeRule.java
index 92cb5834a4..815f275d12 100644
---
a/core/src/main/java/org/apache/calcite/rel/rules/ProjectAggregateMergeRule.java
+++
b/core/src/main/java/org/apache/calcite/rel/rules/ProjectAggregateMergeRule.java
@@ -118,7 +118,7 @@ && kindCount(project.getProjects(), SqlKind.CASE) == 0) {
final RexLiteral literal = (RexLiteral) operands.get(2);
if (Objects.equals(literal.getValueAs(BigDecimal.class),
BigDecimal.ZERO)) {
int j = findSum0(cluster.getTypeFactory(), aggCall, aggCallList);
- return cluster.getRexBuilder().makeInputRef(call.type, j);
+ return
cluster.getRexBuilder().makeInputRef(aggCallList.get(j).getType(), j);
}
}
break;
@@ -157,6 +157,7 @@ && kindCount(project.getProjects(), SqlKind.CASE) == 0) {
builder.project(
RexPermuteInputsShuttle.of(mapping).visitList(projects2),
project.getRowType().getFieldNames());
+ builder.convert(project.getRowType(), true);
call.transformTo(builder.build());
}
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 21fc13be13..39d2a3e649 100644
--- a/core/src/test/java/org/apache/calcite/test/RelOptRulesTest.java
+++ b/core/src/test/java/org/apache/calcite/test/RelOptRulesTest.java
@@ -6991,6 +6991,17 @@ private HepProgram getTransitiveProgram() {
.check();
}
+ /** Test case for
+ * <a
href="https://issues.apache.org/jira/browse/CALCITE-6834";>[CALCITE-6834]
+ * In query that applies COALESCE to nullable SUM,
EnumerableProjectToCalcRule
+ * throws AssertionError</a>. */
+ @Test void testProjectAggregateMergeSum01() {
+ final String sql = "select coalesce(sum(cast(mgr as tinyint)), 0) as ss0\n"
+ + "from sales.emp";
+ sql(sql).withRule(CoreRules.PROJECT_AGGREGATE_MERGE)
+ .check();
+ }
+
/** As {@link #testProjectAggregateMergeSum0()} but there is another use of
* {@code SUM} that cannot be converted to {@code SUM0}. */
@Test void testProjectAggregateMergeSum0AndSum() {
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 9c726fc5ae..3cf19a6f0f 100644
--- a/core/src/test/resources/org/apache/calcite/test/RelOptRulesTest.xml
+++ b/core/src/test/resources/org/apache/calcite/test/RelOptRulesTest.xml
@@ -818,15 +818,17 @@ from (
</Resource>
<Resource name="planBefore">
<![CDATA[
-LogicalAggregate(group=[{}], agg#0=[$SUM0($2)])
- LogicalAggregate(group=[{3, 7}], COUNT_COMM=[COUNT()])
- LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+LogicalProject(EXPR$0=[$0])
+ LogicalAggregate(group=[{}], agg#0=[$SUM0($2)])
+ LogicalAggregate(group=[{3, 7}], COUNT_COMM=[COUNT()])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
]]>
</Resource>
<Resource name="planAfter">
<![CDATA[
-LogicalAggregate(group=[{}], agg#0=[COUNT()])
- LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+LogicalProject(EXPR$0=[$0])
+ LogicalAggregate(group=[{}], agg#0=[COUNT()])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
]]>
</Resource>
</TestCase>
@@ -9022,9 +9024,32 @@ LogicalProject(SS0=[CASE(IS NOT NULL($0),
CAST($0):INTEGER NOT NULL, 0)])
</Resource>
<Resource name="planAfter">
<![CDATA[
-LogicalAggregate(group=[{}], agg#0=[$SUM0($0)])
- LogicalProject(SAL=[$5])
- LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+LogicalProject(SS0=[$0])
+ LogicalAggregate(group=[{}], agg#0=[$SUM0($0)])
+ LogicalProject(SAL=[$5])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testProjectAggregateMergeSum01">
+ <Resource name="sql">
+ <![CDATA[select coalesce(sum(cast(mgr as tinyint)), 0) as ss0
+from sales.emp]]>
+ </Resource>
+ <Resource name="planBefore">
+ <![CDATA[
+LogicalProject(SS0=[CASE(IS NOT NULL($0), CAST($0):INTEGER NOT NULL, 0)])
+ LogicalAggregate(group=[{}], agg#0=[SUM($0)])
+ LogicalProject($f0=[CAST($3):TINYINT])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+ </Resource>
+ <Resource name="planAfter">
+ <![CDATA[
+LogicalProject(SS0=[CAST($0):INTEGER NOT NULL])
+ LogicalAggregate(group=[{}], agg#0=[$SUM0($0)])
+ LogicalProject($f0=[CAST($3):TINYINT])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
]]>
</Resource>
</TestCase>
diff --git a/core/src/test/resources/sql/agg.iq
b/core/src/test/resources/sql/agg.iq
index 42575d1af7..553047b76e 100644
--- a/core/src/test/resources/sql/agg.iq
+++ b/core/src/test/resources/sql/agg.iq
@@ -3849,4 +3849,52 @@ EnumerableAggregate(group=[{}], EXPR$0=[SUM($0)])
EnumerableTableScan(table=[[scott, EMP]])
!plan
+# [CALCITE-6834] In query that applies COALESCE to nullable SUM,
EnumerableProjectToCalcRule throws AssertionError
+# sum(mgr) return TINYINT, coalesce(sum(mgr),0) return INTEGER, so need CAST
+!use scott
+select coalesce(sum(mgr),0) as m
+from emp;
+java.sql.SQLException: Error while executing SQL "select coalesce(sum(mgr),0)
as m
+from emp": Value 38835 out of range
+!error
+EnumerableCalc(expr#0=[{inputs}], expr#1=[CAST($t0):INTEGER NOT NULL], M=[$t1])
+ EnumerableAggregate(group=[{}], agg#0=[$SUM0($3)])
+ EnumerableTableScan(table=[[scott, EMP]])
+!plan
+
+# sum(cast(mgr as int)) return INTEGER, coalesce(sum(cast(mgr as int)),0)
return INTEGER, so don't need CAST
+select coalesce(sum(cast(mgr as int)),0) as m
+from emp;
++--------+
+| M |
++--------+
+| 100611 |
++--------+
+(1 row)
+
+!ok
+
+EnumerableAggregate(group=[{}], agg#0=[$SUM0($0)])
+ EnumerableCalc(expr#0..7=[{inputs}], expr#8=[CAST($t3):INTEGER], $f0=[$t8])
+ EnumerableTableScan(table=[[scott, EMP]])
+!plan
+
+# sum(mgr) return INTEGER, coalesce(sum(mgr),0) return BIGINT, so need CAST
+select coalesce(sum(cast(mgr as int)),cast(0 as bigint)) as m
+from emp;
++--------+
+| M |
++--------+
+| 100611 |
++--------+
+(1 row)
+
+!ok
+
+EnumerableCalc(expr#0=[{inputs}], expr#1=[CAST($t0):BIGINT NOT NULL], M=[$t1])
+ EnumerableAggregate(group=[{}], agg#0=[$SUM0($0)])
+ EnumerableCalc(expr#0..7=[{inputs}], expr#8=[CAST($t3):INTEGER], $f0=[$t8])
+ EnumerableTableScan(table=[[scott, EMP]])
+!plan
+
# End agg.iq
