[CALCITE-4491] Aggregation of window function produces invalid SQL for PostgreSQL

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Affects Version/s: 1.26.0
• Fix Version/s: 1.27.0
• Component/s: core
• Labels: pull-request-available
• Assignee: Julian Hyde
• Reporter: Dominik Labuda
• Created: 08/Feb/21 18:40
• Updated: 03/Jun/21 22:32
• Resolved: 28/May/21 08:21

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!

Issue Links

• links to: GitHub Pull Request #2346