(calcite) branch main updated: [CALCITE-6839] The SUM function sometimes throws overflow exceptions due to incorrect return types

[xiong]

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 5f1f2fff96 [CALCITE-6839] The SUM function sometimes throws overflow 
exceptions due to incorrect return types
5f1f2fff96 is described below

commit 5f1f2fff968ffc890ab7b3f6ced0a38e2c7aecec
Author: Xiong Duan <nobigo...@gmail.com>
AuthorDate: Sun Feb 16 12:51:35 2025 +0800

    [CALCITE-6839] The SUM function sometimes throws overflow exceptions due to 
incorrect return types
---
 .../org/apache/calcite/test/CoreQuidemTest.java    | 10 +++++++
 core/src/test/resources/sql/agg.iq                 | 34 ++++++++++++++++++++++
 .../org/apache/calcite/test/CustomTypeSystems.java | 32 ++++++++++++++++++++
 3 files changed, 76 insertions(+)

diff --git a/core/src/test/java/org/apache/calcite/test/CoreQuidemTest.java 
b/core/src/test/java/org/apache/calcite/test/CoreQuidemTest.java
index 9d19ec653e..614a4e11fa 100644
--- a/core/src/test/java/org/apache/calcite/test/CoreQuidemTest.java
+++ b/core/src/test/java/org/apache/calcite/test/CoreQuidemTest.java
@@ -70,6 +70,16 @@ public static void main(String[] args) throws Exception {
               .with(CalciteConnectionProperty.FUN, SqlLibrary.CALCITE.fun)
               .with(CalciteAssert.Config.SCOTT)
               .connect();
+        case "scott-spark":
+          discard(CustomTypeSystems.SPARK_TYPE_SYSTEM);
+          return CalciteAssert.that()
+              .with(CalciteConnectionProperty.PARSER_FACTORY,
+                  ExtensionDdlExecutor.class.getName() + "#PARSER_FACTORY")
+              .with(CalciteConnectionProperty.FUN, SqlLibrary.CALCITE.fun)
+              .with(CalciteConnectionProperty.TYPE_SYSTEM,
+                  CustomTypeSystems.class.getName() + "#SPARK_TYPE_SYSTEM")
+              .with(CalciteAssert.Config.SCOTT)
+              .connect();
         case "scott-checked-rounding-half-up":
           discard(CustomTypeSystems.ROUNDING_MODE_HALF_UP);
           return CalciteAssert.that()
diff --git a/core/src/test/resources/sql/agg.iq 
b/core/src/test/resources/sql/agg.iq
index 7322b84da2..42575d1af7 100644
--- a/core/src/test/resources/sql/agg.iq
+++ b/core/src/test/resources/sql/agg.iq
@@ -3815,4 +3815,38 @@ select distinct sum(deptno + '1') as deptsum from dept 
order by 1;
 
 !ok
 
+# [CALCITE-6839] The SUM function sometimes throws overflow exceptions due
+# to incorrect return types
+!use scott-spark
+# Test TINYINT Type
+select sum(mgr) from emp;
++--------+
+| EXPR$0 |
++--------+
+| 100611 |
++--------+
+(1 row)
+
+!ok
+
+EnumerableAggregate(group=[{}], EXPR$0=[SUM($3)])
+  EnumerableTableScan(table=[[scott, EMP]])
+!plan
+
+# Test Float Type
+select sum(cast(mgr as float)) from emp;
++----------+
+| EXPR$0   |
++----------+
+| 100611.0 |
++----------+
+(1 row)
+
+!ok
+
+EnumerableAggregate(group=[{}], EXPR$0=[SUM($0)])
+  EnumerableCalc(expr#0..7=[{inputs}], expr#8=[CAST($t3):FLOAT], $f0=[$t8])
+    EnumerableTableScan(table=[[scott, EMP]])
+!plan
+
 # End agg.iq
diff --git 
a/testkit/src/main/java/org/apache/calcite/test/CustomTypeSystems.java 
b/testkit/src/main/java/org/apache/calcite/test/CustomTypeSystems.java
index 6df9bbdf55..fda158c365 100644
--- a/testkit/src/main/java/org/apache/calcite/test/CustomTypeSystems.java
+++ b/testkit/src/main/java/org/apache/calcite/test/CustomTypeSystems.java
@@ -17,8 +17,12 @@
 package org.apache.calcite.test;
 
 import org.apache.calcite.rel.type.DelegatingTypeSystem;
+import org.apache.calcite.rel.type.RelDataType;
+import org.apache.calcite.rel.type.RelDataTypeFactory;
 import org.apache.calcite.rel.type.RelDataTypeSystem;
+import org.apache.calcite.sql.type.BasicSqlType;
 import org.apache.calcite.sql.type.SqlTypeName;
+import org.apache.calcite.sql.type.SqlTypeUtil;
 
 import java.math.RoundingMode;
 import java.util.function.Function;
@@ -46,6 +50,34 @@ private CustomTypeSystems() {
   public static final RelDataTypeSystem NEGATIVE_SCALE_ROUNDING_MODE_HALF_UP =
       withMinScale(ROUNDING_MODE_HALF_UP, sqlTypeName -> -1000);
 
+  /** Type system that similar to Spark. */
+  public static final RelDataTypeSystem SPARK_TYPE_SYSTEM =
+      new DelegatingTypeSystem(RelDataTypeSystem.DEFAULT) {
+        @Override public RelDataType deriveSumType(RelDataTypeFactory 
typeFactory,
+            RelDataType argumentType) {
+          if (argumentType instanceof BasicSqlType) {
+            // For TINYINT, SMALLINT, INTEGER, BIGINT,
+            // using BIGINT
+            if (SqlTypeUtil.isExactNumeric(argumentType) && 
!SqlTypeUtil.isDecimal(argumentType)) {
+              argumentType =
+                  typeFactory.createTypeWithNullability(
+                      typeFactory.createSqlType(SqlTypeName.BIGINT),
+                      argumentType.isNullable());
+            }
+            // For FLOAT, REAL and DOUBLE,
+            // using DOUBLE
+            if (SqlTypeUtil.isApproximateNumeric(argumentType)) {
+              argumentType =
+                  typeFactory.createTypeWithNullability(
+                      typeFactory.createSqlType(SqlTypeName.DOUBLE),
+                      argumentType.isNullable());
+            }
+            return argumentType;
+          }
+          return super.deriveSumType(typeFactory, argumentType);
+        }
+    };
+
   /** Decorates a type system so that
    * {@link org.apache.calcite.rel.type.RelDataTypeSystem#roundingMode()}
    * returns a given value. */