Uploaded image for project: 'Calcite'
  1. Calcite
  2. CALCITE-3866

"numeric field overflow" when running the generated SQL in PostgreSQL

    XMLWordPrintableJSON

    Details

    • Type: Bug
    • Status: Closed
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: None
    • Fix Version/s: 1.23.0
    • Component/s: None

      Description

      When we try to generate a sql after applying AggregateJoinTransposeRule.EXTENDED, the result sql can not run on PostgreSQL, and throws the following exception:

      PSQLException: ERROR: numeric field overflow Detail: A field with precision 7, scale 2 must round to an absolute value less than 10^5.
      

      I found that the main reason is that :

      the return types of sum may have wrong precision when the type of
      its operand is the decimal with precision, for example:

      @Test public void testSum() {
        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 = "";
        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);
      }
      

      the result generated sql of the query is :

      SELECT SUM(CAST(\"t\".\"EXPR$0\" * \"t0\".\"$f1\" AS DECIMAL(10, 4))), SUM(CAST(\"t\".\"$f2\" * \"t0\".\"EXPR$1\" AS DECIMAL(10, 4)))
      FROM (SELECT \"product_id\", SUM(\"store_sales\") AS \"EXPR$0\", COUNT(*) AS \"$f2\"
      FROM \"foodmart\".\"sales_fact_dec_1998\"
      GROUP BY \"product_id\") AS \"t\"
      INNER JOIN (SELECT \"product_id\", COUNT(*) AS \"$f1\", SUM(\"store_sales\") AS \"EXPR$1\"
      FROM \"foodmart\".\"sales_fact_dec_1998\"
      GROUP BY \"product_id\") AS \"t0\" ON \"t\".\"product_id\" = \"t0\".\"product_id\"
      

      AggregateJoinTransposeRule.EXTENDED generates a Aggregate  to sum up the sub-totals:

      // Aggregate above to sum up the sub-totals
      final List<AggregateCall> newAggCalls = new ArrayList<>();
      final int groupCount = aggregate.getGroupCount();
      final int newLeftWidth = sides.get(0).newInput.getRowType().getFieldCount();
      final List<RexNode> projects =
          new ArrayList<>(
              rexBuilder.identityProjects(relBuilder.peek().getRowType()));
      for (Ord<AggregateCall> aggCall : Ord.zip(aggregate.getAggCallList())) {
        final SqlAggFunction aggregation = aggCall.e.getAggregation();
        final SqlSplittableAggFunction splitter =
            Objects.requireNonNull(
                aggregation.unwrap(SqlSplittableAggFunction.class));
        final Integer leftSubTotal = sides.get(0).split.get(aggCall.i);
        final Integer rightSubTotal = sides.get(1).split.get(aggCall.i);
        newAggCalls.add(
            splitter.topSplit(rexBuilder, registry(projects),
                groupCount, relBuilder.peek().getRowType(), aggCall.e,
                leftSubTotal == null ? -1 : leftSubTotal,
                rightSubTotal == null ? -1 : rightSubTotal + newLeftWidth));
      }
      
      public AggregateCall topSplit(RexBuilder rexBuilder,
          Registry<RexNode> extra, int offset, RelDataType inputRowType,
          AggregateCall aggregateCall, int leftSubTotal, int rightSubTotal) {
        final List<RexNode> merges = new ArrayList<>();
        final List<RelDataTypeField> fieldList = inputRowType.getFieldList();
        if (leftSubTotal >= 0) {
          final RelDataType type = fieldList.get(leftSubTotal).getType();
          merges.add(rexBuilder.makeInputRef(type, leftSubTotal));
        }
        if (rightSubTotal >= 0) {
          final RelDataType type = fieldList.get(rightSubTotal).getType();
          merges.add(rexBuilder.makeInputRef(type, rightSubTotal));
        }
        RexNode node;
        switch (merges.size()) {
        case 1:
          node = merges.get(0);
          break;
        case 2:
          node = rexBuilder.makeCall(SqlStdOperatorTable.MULTIPLY, merges);
          node = rexBuilder.makeAbstractCast(aggregateCall.type, node);
          break;
        default:
          throw new AssertionError("unexpected count " + merges);
        }
        int ordinal = extra.register(node);
        return AggregateCall.create(getMergeAggFunctionOfTopSplit(), false, false,
            false, ImmutableList.of(ordinal), -1, aggregateCall.collation,
            aggregateCall.type, aggregateCall.name);
      }
      

      but it use the type of origin sum to generate a cast like 

      SUM(CAST(\"t\".\"$f2\" * \"t0\".\"EXPR$1\" AS DECIMAL(10, 4)))

      ReturnTypes.AGG_SUM simplely use the operand type as its return type:

      public static final SqlReturnTypeInference AGG_SUM = opBinding -> {
        final RelDataTypeFactory typeFactory = opBinding.getTypeFactory();
        final RelDataType type = typeFactory.getTypeSystem()
            .deriveSumType(typeFactory, opBinding.getOperandType(0));
        if (opBinding.getGroupCount() == 0 || opBinding.hasFilter()) {
          return typeFactory.createTypeWithNullability(type, true);
        } else {
          return type;
        }
      };
      @Override public RelDataType deriveSumType(RelDataTypeFactory typeFactory,
          RelDataType argumentType) {
        return argumentType;
      }
      

      So the result of \"t\".\"$f2\" * \"t0\".\"EXPR$1\" may too large to cause overflow 

        Attachments

          Issue Links

            Activity

              People

              • Assignee:
                winipanda TANG Wen-hui
                Reporter:
                winipanda TANG Wen-hui
              • Votes:
                0 Vote for this issue
                Watchers:
                3 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved:

                  Time Tracking

                  Estimated:
                  Original Estimate - Not Specified
                  Not Specified
                  Remaining:
                  Remaining Estimate - 0h
                  0h
                  Logged:
                  Time Spent - 40m
                  40m