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

Aggregation of window function produces invalid SQL for PostgreSQL

    XMLWordPrintableJSON

    Details

      Description

      Issue

      We tested the following behavior against PostgreSQL database, however more of the supported dialects may be affected.

      When aggregating the results of a window function an invalid SQL is generated. I was able to replicate the behavior in tests with:

      @Test void testAggregatingWindowFunction() {
          final RelBuilder builder = relBuilder();
          final RelNode root = builder
              .scan("EMP")
              .project(
                  builder.alias(
                      builder.getRexBuilder().makeOver(
                          builder.getTypeFactory().createSqlType(SqlTypeName.INTEGER),
                          SqlStdOperatorTable.RANK,
                          new ArrayList<>(),
                          new ArrayList<>(),
                          ImmutableList.of(new RexFieldCollation(builder.field("SAL"), ImmutableSet.of())),
                          RexWindowBounds.UNBOUNDED_PRECEDING,
                          RexWindowBounds.UNBOUNDED_FOLLOWING,
                          true,
                          true,
                          false,
                          false,
                          false
                      ),
                      "rank"
                  )
              )
              .as("tmp")
              .aggregate(
                  builder.groupKey(),
                  builder.count(
                      true,
                      "cnt",
                      builder.field("tmp", "rank")
                  )
              )
              .build();
          final String expectedSql = "SELECT COUNT(DISTINCT \"rank\") AS \"cnt\"\n" +
              "FROM (SELECT RANK() OVER (ORDER BY \"SAL\") AS \"rank\"\n" +
              "FROM \"scott\".\"EMP\") AS \"t\"";
          assertThat(
              toSql(root, PostgresqlSqlDialect.DEFAULT),
              isLinux(expectedSql)
          );
        }
      

      The code above fails, since it produces this SQL instead of the expected one, which cannot be executed on PGSQL:

      SELECT COUNT(DISTINCT RANK() OVER (ORDER BY "SAL")) AS "cnt"
      FROM "scott"."EMP"
      

      In that case I am getting these kinds of errors from DB:

      ERROR: aggregate function calls cannot contain window function calls
      

      Suggested solution

      Since SqlDialect already contains support for determining whether the databases support nested aggregations via public boolean supportsNestedAggregations() we could either add another method like public boolean supportsNestedWindows() maybe supportNestedWindowsInAggregation() to be more verbose. Or we could reuse the existing method for the window purposes (which seems non-transparent). Then we will be able to handle the behavior in SqlImplementor.needNewSubQuery() similarly as it already does with the aggregations.

      Please let me know if you agree and I will be more than happy to provide you with PR to review, thanks!

        Attachments

          Issue Links

            Activity

              People

              • Assignee:
                julianhyde Julian Hyde
                Reporter:
                dominik.labuda Dominik Labuda
              • Votes:
                0 Vote for this issue
                Watchers:
                5 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