[calcite] branch master updated: [CALCITE-3866] "numeric field overflow" when running the generated SQL in PostgreSQL (Wenhui Tang)

[fe...@apache.org]

This is an automated email from the ASF dual-hosted git repository.

fengzhu pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/calcite.git


The following commit(s) were added to refs/heads/master by this push:
     new e081c5b  [CALCITE-3866] "numeric field overflow" when running the generated SQL in PostgreSQL (Wenhui Tang)
e081c5b is described below

commit e081c5b4227a57defe47246d8ff3e6f7cce838e4
Author: wenhuitang <we...@yeah.net>
AuthorDate: Mon Mar 23 14:35:32 2020 +0800

    [CALCITE-3866] "numeric field overflow" when running the generated SQL in PostgreSQL (Wenhui Tang)
---
 .../calcite/rel/type/RelDataTypeSystemImpl.java    | 16 ++++++++++
 .../calcite/sql/SqlSplittableAggFunction.java      |  9 ++++--
 .../calcite/rel/rel2sql/RelToSqlConverterTest.java | 36 +++++++++++++++++++++-
 .../calcite/sql/test/SqlOperatorBaseTest.java      |  4 +--
 core/src/test/resources/sql/agg.iq                 |  2 +-
 5 files changed, 61 insertions(+), 6 deletions(-)

diff --git a/core/src/main/java/org/apache/calcite/rel/type/RelDataTypeSystemImpl.java b/core/src/main/java/org/apache/calcite/rel/type/RelDataTypeSystemImpl.java
index f719ceb..32cbc34 100644
--- a/core/src/main/java/org/apache/calcite/rel/type/RelDataTypeSystemImpl.java
+++ b/core/src/main/java/org/apache/calcite/rel/type/RelDataTypeSystemImpl.java
@@ -16,6 +16,7 @@
  */
 package org.apache.calcite.rel.type;
 
+import org.apache.calcite.sql.type.BasicSqlType;
 import org.apache.calcite.sql.type.SqlTypeFamily;
 import org.apache.calcite.sql.type.SqlTypeName;
 
@@ -217,6 +218,21 @@ public abstract class RelDataTypeSystemImpl implements RelDataTypeSystem {
 
   @Override public RelDataType deriveSumType(RelDataTypeFactory typeFactory,
       RelDataType argumentType) {
+    if (argumentType instanceof BasicSqlType) {
+      SqlTypeName typeName = argumentType.getSqlTypeName();
+      if (typeName.allowsPrec()
+          && argumentType.getPrecision() != RelDataType.PRECISION_NOT_SPECIFIED) {
+        int precision = typeFactory.getTypeSystem().getMaxPrecision(typeName);
+        if (typeName.allowsScale()) {
+          argumentType = typeFactory.createTypeWithNullability(
+              typeFactory.createSqlType(typeName, precision, argumentType.getScale()),
+              argumentType.isNullable());
+        } else {
+          argumentType = typeFactory.createTypeWithNullability(
+              typeFactory.createSqlType(typeName, precision), argumentType.isNullable());
+        }
+      }
+    }
     return argumentType;
   }
 
diff --git a/core/src/main/java/org/apache/calcite/sql/SqlSplittableAggFunction.java b/core/src/main/java/org/apache/calcite/sql/SqlSplittableAggFunction.java
index 6fea6c2..26e67e5 100644
--- a/core/src/main/java/org/apache/calcite/sql/SqlSplittableAggFunction.java
+++ b/core/src/main/java/org/apache/calcite/sql/SqlSplittableAggFunction.java
@@ -260,7 +260,10 @@ public interface SqlSplittableAggFunction {
         RelDataType inputRowType, AggregateCall aggregateCall) {
       final int arg = aggregateCall.getArgList().get(0);
       final RelDataTypeField field = inputRowType.getFieldList().get(arg);
-      return rexBuilder.makeInputRef(field.getType(), arg);
+      final RelDataType fieldType = field.getType();
+      final RelDataTypeFactory typeFactory = rexBuilder.getTypeFactory();
+      RelDataType type = typeFactory.getTypeSystem().deriveSumType(typeFactory, fieldType);
+      return rexBuilder.makeInputRef(type, arg);
     }
 
     public AggregateCall split(AggregateCall aggregateCall,
@@ -346,7 +349,9 @@ public interface SqlSplittableAggFunction {
         RelDataType inputRowType, AggregateCall aggregateCall) {
       final int arg = aggregateCall.getArgList().get(0);
       final RelDataType type = inputRowType.getFieldList().get(arg).getType();
-      final RexNode inputRef = rexBuilder.makeInputRef(type, arg);
+      final RelDataTypeFactory typeFactory = rexBuilder.getTypeFactory();
+      final RelDataType type1 = typeFactory.getTypeSystem().deriveSumType(typeFactory, type);
+      final RexNode inputRef = rexBuilder.makeInputRef(type1, arg);
       if (type.isNullable()) {
         return rexBuilder.makeCall(SqlStdOperatorTable.COALESCE, inputRef,
             rexBuilder.makeExactLiteral(BigDecimal.ZERO, type));
diff --git a/core/src/test/java/org/apache/calcite/rel/rel2sql/RelToSqlConverterTest.java b/core/src/test/java/org/apache/calcite/rel/rel2sql/RelToSqlConverterTest.java
index ce9c719..e5b67ce 100644
--- a/core/src/test/java/org/apache/calcite/rel/rel2sql/RelToSqlConverterTest.java
+++ b/core/src/test/java/org/apache/calcite/rel/rel2sql/RelToSqlConverterTest.java
@@ -26,6 +26,9 @@ import org.apache.calcite.rel.RelNode;
 import org.apache.calcite.rel.core.JoinRelType;
 import org.apache.calcite.rel.logical.LogicalAggregate;
 import org.apache.calcite.rel.logical.LogicalFilter;
+import org.apache.calcite.rel.rules.AggregateJoinTransposeRule;
+import org.apache.calcite.rel.rules.AggregateProjectMergeRule;
+import org.apache.calcite.rel.rules.FilterJoinRule;
 import org.apache.calcite.rel.rules.ProjectToWindowRule;
 import org.apache.calcite.rel.rules.PruneEmptyRules;
 import org.apache.calcite.rel.rules.UnionMergeRule;
@@ -2751,7 +2754,38 @@ class RelToSqlConverterTest {
     sql(query6).optimize(rules, hepPlanner).ok(expected6);
   }
 
-  @Test void testRankFunctionForPrintingOfFrameBoundary() {
+  /**
+   * Test case for
+   * <a href="https://issues.apache.org/jira/browse/CALCITE-3866">[CALCITE-3866]
+   * "numeric field overflow" when running the generated SQL in PostgreSQL </a>.
+   */
+  @Test public void testSumReturnType() {
+    String query =
+        "select sum(e1.\"store_sales\"), sum(e2.\"store_sales\") from \"sales_fact_dec_1998\" as "
+            + "e1 , \"sales_fact_dec_1998\" as e2 where e1.\"product_id\" = e2.\"product_id\"";
+
+    String expect = "SELECT SUM(CAST(\"t\".\"EXPR$0\" * \"t0\".\"$f1\" AS DECIMAL"
+        + "(19, 4))), SUM(CAST(\"t\".\"$f2\" * \"t0\".\"EXPR$1\" AS DECIMAL(19, 4)))\n"
+        + "FROM (SELECT \"product_id\", SUM(\"store_sales\") AS \"EXPR$0\", COUNT(*) AS \"$f2\"\n"
+        + "FROM \"foodmart\".\"sales_fact_dec_1998\"\n"
+        + "GROUP BY \"product_id\") AS \"t\"\n"
+        + "INNER JOIN "
+        + "(SELECT \"product_id\", COUNT(*) AS \"$f1\", SUM(\"store_sales\") AS \"EXPR$1\"\n"
+        + "FROM \"foodmart\".\"sales_fact_dec_1998\"\n"
+        + "GROUP BY \"product_id\") AS \"t0\" ON \"t\".\"product_id\" = \"t0\".\"product_id\"";
+
+    HepProgramBuilder builder = new HepProgramBuilder();
+    builder.addRuleClass(FilterJoinRule.class);
+    builder.addRuleClass(AggregateProjectMergeRule.class);
+    builder.addRuleClass(AggregateJoinTransposeRule.class);
+    HepPlanner hepPlanner = new HepPlanner(builder.build());
+    RuleSet rules = RuleSets.ofList(FilterJoinRule.FILTER_ON_JOIN, FilterJoinRule.JOIN,
+        AggregateProjectMergeRule.INSTANCE,
+        AggregateJoinTransposeRule.EXTENDED);
+    sql(query).withPostgresql().optimize(rules, hepPlanner).ok(expect);
+  }
+
+  @Test public void testRankFunctionForPrintingOfFrameBoundary() {
     String query = "SELECT rank() over (order by \"hire_date\") FROM \"employee\"";
     String expected = "SELECT RANK() OVER (ORDER BY \"hire_date\")\n"
         + "FROM \"foodmart\".\"employee\"";
diff --git a/core/src/test/java/org/apache/calcite/sql/test/SqlOperatorBaseTest.java b/core/src/test/java/org/apache/calcite/sql/test/SqlOperatorBaseTest.java
index fe128a3..37b3e0b 100644
--- a/core/src/test/java/org/apache/calcite/sql/test/SqlOperatorBaseTest.java
+++ b/core/src/test/java/org/apache/calcite/sql/test/SqlOperatorBaseTest.java
@@ -8469,8 +8469,8 @@ public abstract class SqlOperatorBaseTest {
         false);
     tester.checkType("sum('name')", "DECIMAL(19, 19)");
     checkAggType(tester, "sum(1)", "INTEGER NOT NULL");
-    checkAggType(tester, "sum(1.2)", "DECIMAL(2, 1) NOT NULL");
-    checkAggType(tester, "sum(DISTINCT 1.5)", "DECIMAL(2, 1) NOT NULL");
+    checkAggType(tester, "sum(1.2)", "DECIMAL(19, 1) NOT NULL");
+    checkAggType(tester, "sum(DISTINCT 1.5)", "DECIMAL(19, 1) NOT NULL");
     tester.checkFails(
         "^sum()^",
         "Invalid number of arguments to function 'SUM'. Was expecting 1 arguments",
diff --git a/core/src/test/resources/sql/agg.iq b/core/src/test/resources/sql/agg.iq
index 398ebea..c3573e6 100644
--- a/core/src/test/resources/sql/agg.iq
+++ b/core/src/test/resources/sql/agg.iq
@@ -1235,7 +1235,7 @@ select deptno,
 from "scott".emp
 group by deptno;
 DEPTNO TINYINT(3)
-SAL_10 DECIMAL(7, 2)
+SAL_10 DECIMAL(19, 2)
 !type
 +--------+---------+
 | DEPTNO | SAL_10  |