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

["Xurenhe (Jira)" <ji...@apache.org>]

Xurenhe created CALCITE-4738:
--------------------------------

             Summary: Pattern of RelNode: `Aggregate-Project-Aggregate` executing RelToSqlConverter error.
                 Key: CALCITE-4738
                 URL: https://issues.apache.org/jira/browse/CALCITE-4738
             Project: Calcite
          Issue Type: Bug
          Components: core
            Reporter: Xurenhe


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.
{code:java}
@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);
  }
{code}
{code:sql}
-- return sql, after executing rel_to_sql
SELECT SUM(COUNT(DISTINCT "MGR")) AS "COL_SUM"
FROM "scott"."EMP"
GROUP BY "DEPTNO"
{code}
----------------------------------------------
Pattern2: Aggregate-Aggregate(ok)
{code:java}
@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);
  }
{code}
{code:sql}
-- 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"
{code}




--
This message was sent by Atlassian Jira
(v8.3.4#803005)