[CALCITE-4738] Pattern of RelNode: `Aggregate-Project-Aggregate` executing RelToSqlConverter error.

• Type: Bug
• Status: Closed
• Priority: Critical
• Resolution: Not A Bug
• Component/s: core
• Assignee: Unassigned
• Reporter: Xurenhe
• Created: 14/Aug/21 09:07
• Updated: 15/Oct/21 01:51
• Resolved: 17/Aug/21 06:07

Description

Here, some bug maybe exist in `RelToSqlConverter`, when handling for `Aggregate-Project-Aggregate`.

Pattern1: Aggregate-Project-Aggregate(failed)
we find returned sql cannot be analyzed.

@Test void testSuccessiveAggregateProjectAggregate() {
    final Function<RelBuilder, RelNode> relFn = b -> b
        .scan("EMP")
        .aggregate(b.groupKey(b.field("DEPTNO")),
            b.count(true, "CNT_D", b.field("MGR")))
        .project(ImmutableList.of(b.field(1)), ImmutableList.of("CNT_D"))
        .aggregate(b.groupKey(), b.sum(false, "COL_SUM", b.field(0)))
        .build();
    final String expected = "SELECT SUM(\"CNT_D\") AS \"COL_SUM\"\n"
        + "FROM (SELECT \"DEPTNO\", COUNT(DISTINCT \"MGR\") AS \"CNT_D\"\n"
        + "FROM \"scott\".\"EMP\"\n"
        + "GROUP BY \"DEPTNO\") AS \"t\"";
    relFn(relFn).ok(expected);
  }

-- return sql, after executing rel_to_sql
SELECT SUM(COUNT(DISTINCT "MGR")) AS "COL_SUM"
FROM "scott"."EMP"
GROUP BY "DEPTNO"

----------------------------------------------
Pattern2: Aggregate-Aggregate(ok)

@Test void testSuccessiveAggregateAggregate() {
    final Function<RelBuilder, RelNode> relFn = b -> b
        .scan("EMP")
        .aggregate(b.groupKey(b.field("DEPTNO")),
            b.count(true, "CNT_D", b.field("MGR")))
        .aggregate(b.groupKey(), b.sum(false, "COL_SUM", b.field(1)))
        .build();
    final String expected = "SELECT SUM(\"CNT_D\") AS \"COL_SUM\"\n"
        + "FROM (SELECT \"DEPTNO\", COUNT(DISTINCT \"MGR\") AS \"CNT_D\"\n"
        + "FROM \"scott\".\"EMP\"\n"
        + "GROUP BY \"DEPTNO\") AS \"t1\"";
    relFn(relFn).ok(expected);
  }

-- return sql, after executing rel_to_sql
SELECT SUM("CNT_D") AS "COL_SUM"
FROM (SELECT "DEPTNO", COUNT(DISTINCT "MGR") AS "CNT_D"
FROM "scott"."EMP"
GROUP BY "DEPTNO") AS "t1"